It's not difficult to build two combo boxes where one shows a filtered list dependent on selections in the other combo box. There is an article on this site showing how to do it on a form which shows one record per screen.
Try the same technique in continuous forms and it doesn't work as well. The data still goes into the combos but filtering to show the combo in one record, makes the other records appear to be blank. The data is still there; it's just not visible because of the filtering.
This article shows an approach that still lets you select from a filtered list, but you get to see the data in the other records as well.
In the sample database, open frmItems_Problem and add a few records. As you update the Contact field, only contacts from the same business as the current record can be seen. The reason is that contacts from the other businesses are not part of the combo's dataset, so they cannot be displayed.
To make all of the data visible in the Contacts field you need to remove the filter; delete the criteria highlighted in the next screenshot and all of the values are visible again.
Of course, that's not much use because we want to filter the contact list to simplify data entry. That's where the popup form comes in. fpopContacts is an unbound form that contains a single listbox, lstContact, with 3 columns. The first and third columns are hidden by setting their widths to 0.
In frmItems_Solution, the Suppliers combo box has the following code in the AfterUpdate event:
Private Sub Supplier_AfterUpdate() DoCmd.OpenForm "fpopContacts", _ View:=acNormal, _ WindowMode:=acDialog, _ OpenArgs:=Me.Name & "|Contact|" & Me.Supplier 'code pauses here while the selection is made Me.Contact.Requery DoCmd.Close acForm, "fpopContacts" End Sub
fpopContacts launches in dialog mode, which stops the code until you close the popup. The OpenArgs is a pipe-delimited list containing the name of the caller form, the name of the control to be updated, and the value to be used as the filter.
When the form opens, the OpenArgs string is parsed and used to create the SQL for the listbox.
Private Sub Form_Open(Cancel As Integer) Dim sSQL As String, _ sFilter As String Dim lngID As Long Dim x sSQL = "SELECT tblContacts.ContactID, " _ & "[LastName] & "", "" & [FirstName] AS Contact, " _ & "tblContacts.BusinessID FROM tblContacts" If Len(Me.OpenArgs) > 0 Then x = Split(Me.OpenArgs, "|") sForm = x(0) sControl = x(1) lngID = x(2) sFilter = " WHERE tblContacts.[BusinessID]=" & lngID sSQL = sSQL & sFilter Else sSQL = sSQL End If Me.lstContact.RowSource = sSQL End Sub
The SQL is copied from the rowsource of the Contacts field. It has been broken onto several lines to make it easier to read, and the internal quotes have been doubled up to keep the VBA parser happy.
The If statement checks to see whether there is anything in the OpenArgs, indicating that we will be filtering rows in the listbox. If anything is found it is parsed using the Split function, to give us 3 variables. The filter string is now built and appended to the starting SQL, and the rowsource of the listbox is updated.
Note: sForm and sControl are declared at the module level because their values will be used in more than one routine. If you look at the complete code in the database you will see those declarations.
The AfterUpdate event on the listbox pushes the selected value into Contact and hides the popup.
Private Sub lstContact_AfterUpdate() Forms(sForm).Controls(sControl) = Me.lstContact Me.Visible = False End Sub
At this stage, the original form continues running its code and these two lines finish the job: updating Contact, and closing the popup form, ready for next time.
Me.Contact.Requery DoCmd.Close acForm, "fpopContacts"
That's it. There are some things that could improve the technique; make the popup launch next to the control that called it, and work out a way to adapt the SQL so that you can use the same popup form with multiple tables. But as it stands, you should find it to be an improvement.