Try something like this:
- Option Compare Database
-
-
Private Sub Field1_AfterUpdate()
-
SetFilter
-
End Sub
-
-
Private Sub Field2_AfterUpdate()
-
SetFilter
-
End Sub
-
-
Private Sub Field3_AfterUpdate()
-
SetFilter
-
End Sub
-
-
Private Sub SetFilter()
-
Dim FilterCriteria As String
-
If Field1 & "" <> "" Then FilterCriteria = FilterCriteria & " AND [Field1]='" & Field1 & "'"
-
If Field2 & "" <> "" Then FilterCriteria = FilterCriteria & " AND [Field2]='" & Field2 & "'"
-
If Field3 & "" <> "" Then FilterCriteria = FilterCriteria & " AND [Field3]='" & Field3 & "'"
-
If FilterCriteria = "" Then
-
Me.FilterOn = False
-
Else
-
FilterCriteria = Mid(FilterCriteria, 6) 'REMOVE THE LEADING " AND "
-
Me.Filter = FilterCriteria
-
Me.FilterOn = True
-
End If
-
End Sub
Do this by creating 3 Combo Boxes somewhere on your form. Relabel the "Field#" with the appropriate combo box name, then set your row source to the appropriate field on the associated table (you can use the Query Builder for this to make it easier). Make sure your control source properties are blank. Finally if you find you are generating huge lists of duplicate data in the combo boxes, or wish to avoid having this happen in the future change "SELECT" to "SELECT DISTINCT" in the row source. Also if you wish to avoid typos you can change the
Limit to List property to "Yes."
This will create a series of 3 combo boxes that allow you to look up information. It's quick and dirty, and still allows for user error (Like if you select a name then a company which that name is not associated with in any record) but if you add it to your main data form, then you can filter your records down. Attached is a screen shot showing an example of how I implemented this.