Accessing data between a main form and subform

[Bobpj]

I want to create a combo box on a subform that relates to a data field on the
mainform is this possible?

As an example, the mainform has a field that displays the type of vehicle a
client wants to hire. This field was set in the underlying table in an
earlier action.

On the subform I want the combo box to display a list of suppliers who
provide that type of vehicle.

As I move through the records on the mainform, I want the combox box
contents to change according to the type of vehicle that appears in the main
form field.

Thanks

 

[Allen Browne]

You will need to use code to change the RowSource property of the combo,
depending on the value of the VehicleTypeID in the main form.

If the combo's display column is not the bound column, you will need to use
the AfterUpdate event of the main form's VehicleTypeID, and the Current
event of the form (that is the main form.) Something like this:

Private Sub VehicleTypeID_AfterUpdate()
Dim strSql As String
Dim strWhere As String
If IsNull(Me.VehicleTypeID) Then
strWhere = "(False)"
Else
strWhere = "[VehicleTypeID] = " & Me.VehicleTypeID
End If
strSql = "SELECT SupplierID FROM tblVehicleSupplier WHERE " & strWhere
Me.[Sub1].Form!VehicleTypeID.RowSource = strSql
End Sub

Private Sub Form_Current()
Call VehicleTypeID_AfterUpdate
End Sub

If the combo's display column is its bound column, you could get away using
its Enter event.