Main form and subform for 1:m tables in Access 2013

Question

I want to display the fields from two tables on a form. The two tables are ‘Customer’ and ‘Vehicle’. (1:m). The customer fields will be displayed in the main form and to display the Vehicle fields I have put a subform in Datasheet view and I added a textbox for vehicleID. When I enter CustomerID and click a commandbutton on another form this main form (frmCustomerDetails) will be opened with the subform (sfrmVehicle). The customer fields are correctly displayed in the main form. There is no error messages, but I cant see the Vehicle fields in the subform. It just displays #Name? six times. There are six records in the vehicle table, so I think it doesn’t even filter the vehicleid for this customer. (Only two records in the vehicle table belong to this customer.) My Ms Access 2013 VBA code is;

Dim strSQL1 As String
Dim rs1 As DAO.Recordset
strSQL1 = "SELECT Customer.CustomerID, " & _
               "  Customer.fName, " & _
               "  Customer.lName, " & _
               "  Customer.Telephone, " & _
               "  Customer.MobilePhone, " & _
               "  Customer.EMail, " & _
               "  Customer.Address1, " & _
               "  Customer.Address2, " & _
               "  Customer.City, " & _
               "  Customer.State, " & _
               "  Customer.PostalCode, " & _
               "  Vehicle.VehicleID " & _
               "FROM Customer INNER JOIN Vehicle ON Customer.CustomerID = Vehicle.CustomerID;"
Set rs1 = CurrentDb.OpenRecordset(Name:=strSQL1, Type:=dbOpenDynaset)
rs1.FindFirst "[CustomerID] =""" & txtIDs & """"
DoCmd.OpenForm "frmCustomerDetails"
[Forms]![frmCustomerDetails]![txtCustomerName] = rs1.Fields!fName
..
..
[Forms]![frmCustomerDetails]![sfrmVehicle]![txtVehicleId].ControlSource = rs1.Fields!VehicleID
rs1.Close
Set rs1 = Nothing

Do I have to have two different SQL statements for main form and subform? Then how to do filtering. Need help. please.

You don't need any code at all for a main form + subform. Please read: support.office.com/en-us/article/… — Andre

Mark Mark · Accepted Answer · 2017-08-25T07:52:48

To do the filtering on the subform all you need is this code;

Me.sfrmVehicle.Form.Filter = "[vehicleID]=" & Me.vehicleIDTextBox
Me.sfrmVehicle.Form.FilterOn = True

And for the name error in the subform, that could be because you haven't selected the correct source object in the data tab in its property sheet.

Edit: With the SQL statements, like Andre said, you don't need them.

Main form and subform for 1:m tables in Access 2013

1 Answers