You can accomplish this by carrying out an OpenForm action in a macro that runs in response to the Supplier form's Current event. You can make the Product List form display records whose value in the SupplierID field in the underlying table matches the value in the SupplierID field on the Suppliers form. Set the action's Where Condition argument as follows:
[SupplierID]=Forms![Suppliers]![SupplierID]
TipIf you don't always want the Product List form to open when you move between records on the Suppliers form, include a conditional expression in the macro that checks whether or not the Product List form is open.
This conditional expression uses the IsLoaded function to check whether the Product List form is open. The IsLoaded function is a sample Function procedure included with the Northwind sample database, and not a built-in Visual Basic function.
After creating the macro, set the Suppliers form's OnCurrent event property to the name of the macro.
When you move between supplier records on a Suppliers form, you might want to see the products each supplier carries in a Products form. In the Form_Current event procedure of the Suppliers form, set the FilterOn and Filter properties of the Products form:
Private Sub Form_Current()
' Declare and set a variable to store the WHERE
' clause that describes the records you want to
' display.
Dim strCond As String
strCond = "SupplierID = Forms!Suppliers!SupplierID"
' Use the IsLoaded function from the Northwind
' sample database to check whether the Products
' form is open, then set the properties.
If IsLoaded("Products") Then
Forms![Products].FilterOn = True
Forms![Products].Filter = strCond
End If
End Sub