[Solved] Form always opens first record

[jklane]

Hi, I'm working on my first OO database and hoping someone can help me. Since I'm a novice at this, I'm hoping it's something simple I've overlooked or don't understand. Any help would be greatly appreciated.

I have two tables, Customers and Vehicles. The Customers table has a primary key, CustomerID. The Vehicles table has a primary key, ID. One customer can have more than one vehicle. I have created a form where I can select a customer, which then displays the vehicles belonging to that customer. I select a vehicle and click a Push Button to run a macro that opens a new form with customer and vehicle details.

Here's the macro:

Code: Select all

Sub OpenFormMaintDetail
const sNewDocumentName="MaintDetail"
Doc = StarDesktop.CurrentComponent
Form = Doc.DrawPage.Forms.GetByIndex(0)
CustID = Form.getByName("TxtCustID").Text
frm_container = ThisDatabaseDocument.FormDocuments.getByName(sNewDocumentName)
frm_container.open
FormModel2 = frm_container.component.DrawPage.forms.getbyindex(0)
FormModel2.Filter =("Customers.CustomerID = " & CustID)
FormModel2.ApplyFilter = True
FormModel2.reload()
end sub

The problem is that regardless of what vehicle I select, the new form always displays the first vehicle in the table for that customer. To be clear, the original form contains a mainform (customer info) and a subform (vehicle info). I'm wondering if I need to filter by vehicle id (ID), but nothing I've tried has worked so far and I've been unsuccessful researching this forum and the net for a solution.

[F3K Total]

Hi,
the code seems to be correct. If you load up that .odb-file, only containing a few records having fake-data, we can help you.
If the file should be to big for upload, first execute once via Tools/SQL...

Code: Select all

CHECKPOINT DEFRAG

to compress the database.
R

[jklane]

R,
Thank you for the reply. I've changed the names to the US presidential candidates' names. Donald Trump and Hillary Clinton have 2 vehicles each. Everyone else has one vehicle. If you open the form "Maintenance" and select a record then click PRINT REPORT, you'll see that the first vehicle assigned to a customer is opened.

[F3K Total]

Hi,
this should work:

Code: Select all

Sub OpenFormMaintDetail
    Doc = StarDesktop.CurrentComponent
    Form = Doc.DrawPage.Forms.GetByName("MainForm")
    CustID = Form.columns.getbyname("CustomerID").getint
    SubForm = Form.getbyname("SubForm")
    VehikelID = Subform.Columns.getbyname("ID").getint
    frm_container = ThisDatabaseDocument.FormDocuments.getByName("MaintDetail")
    frm_container.open
    MainFormMaintDetail = frm_container.component.DrawPage.forms.getbyName("MainForm")
    MainFormMaintDetail.Filter =("CustomerID = " & CustID)
    MainFormMaintDetail.ApplyFilter = True
    MainFormMaintDetail.reload()
    SubFormMaintDetail = MainFormMaintDetail.getbyname("SubForm")
    SubFormMaintDetail.Filter =("ID = " & VehikelID)
    SubFormMaintDetail.ApplyFilter = True
    SubFormMaintDetail.reload()
end sub

I suggest you, to inspect your forms by using the Form Navigator, then you'll understand what MainForm, SubForm ... are. They are the names of the structural forms.

R

[jklane]

Yes! That worked! Thank you!