Filtering a MS Access Listbox on a Form Upon Opening/Loading

0
votes

Two tables in my MSAccess database: MainT, and SubT which is linked to MainT via a one-to-many join.

I use a listbox (Listbox1) on a form (Form1) which opens a new form (Form2) on double-click. On that second form (Form2) there is also a second listbox (Listbox2).

The issue I am having is that Form2 displays the correct record from MainT as selected from Listbox1 on Form1 - but the Listbox2 on Form2 is supposed to show only records from SubT which match the record from MainT.

Instead Lisbox2 is displaying all the records in SubT and is not filtering based on those records in SubT that match the record from MainT being displayed in the form (Form2)

The double-click command from Listbox1 to open Form2:

 DoCmd.OpenForm "Form2", , , "TrackerPTID =" & Me.ListBox1, , acDialog

So my question is how I can take the value from Form1/Listbox1 that gets Form2 to open to the correct record and pass that to the Listbox2 so that it uses that same value to filter Listbox2 to the records on SubT that match the record on MainT

Thanks in advance!

R

2
sqlformsms-accesslistbox

2 Answers

0
votes

If you are using Access 2007 or later I would suggest using TempVars.

Set a Tempvar in Form1 before opening Form2

Tempvar("TrackerPTID") = Me.Listbox1
DoCmd.OpenForm "Form2", , , "TrackerPTID =" & Me.ListBox1, , acDialog

Then define the Listbox2.RowSource like:

Select * from SubT where TrackerPTID = [Tempvars]![TrackerPTID]
0
votes

As you have the ID on Form2, all you need is to filter the listbox. Use the OnCurrent event of Form2 to filter on Form2's ID:

Private Sub Form_Current()

    Me!Listbox2.RowSource = "Select * From Table2 Where ID = " & Me!ID.Value & ""

End Sub