There are a few ways to do this. Like gnawoncents has asked, if you can provide what you have, it will make it easier to narrow down what might best work for you.
It sounds to me like you could run the ComboBox Wizard and select the "Find a Record on my form based on the value I selected in my combo box" option. Then after the ComboBox has been created, you can right-click on it and change it to a ListBox. This doesn't use a SubForm, but it's pretty simple to implement and maintain. This wont Filter the MainForm, just move to the appropriate record:
- Private Sub Combo11_AfterUpdate()
-
' Find the record that matches the control.
-
Dim rs As Object
-
-
Set rs = Me.Recordset.clone
-
rs.FindFirst "[PartID] = " & Str(Nz(Me![Combo11], 0))
-
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
-
End Sub
If you need a SubForm, there are a few different ways I can think of doing this:
The First, which is probably easiest, but I'm not a fan of is to setup a Query for the MainForm that has a criteria expression that points to the SubForm, then things will either be automatic, or only need a MainForm.Requery from the SubForm OnCurrent Event. This will Filter the MainForm. The Criteria would be something like this:
- Forms![MainForm]![SubForm].Form![PartID]
The Second, is to write a VBA routine for the SubForm OnCurrent Event that applies a Filter to the MainForm:
- Private Sub Form_Current()
-
Forms("MainForm").Form.Filter = "[PartID] = " & Me!PartID
-
Forms("MainForm").Form.FilterOn = True
-
End Sub
The Third, is to write a VBA routine for the SubForm OnCurrent Event that just finds the Record on the MainForm:
- Private Sub Form_Current()
-
Dim rs As Object
-
Set rs = Forms("MainForm").Form.Recordset.clone
-
rs.FindFirst "[PartID] = " & Me!PartID
-
If Not rs.EOF Then Forms("MainForm").Form.Bookmark = rs.Bookmark
-
End Sub
The Fourth, and very cool, but tricky way to do this is to hook into the SubForm's events from the MainForm. This one is tricky, but works very well once it's up and working. This code is all located on the MainForm:
- Private WithEvents fSubForm As Access.Form
-
Private Sub Form_Load()
-
' Hook into Subform Event
-
Set fSubForm = Me.SubForm.Form
-
fSubForm.OnCurrent = "[Event Procedure]"
-
End Sub
-
Public Sub fSubForm_Current()
-
' Subform OnCurrent
-
Me.Filter = "PartID" & Me.SubForm!PartID
-
Me.FilterOn = True
-
End Sub
-