Unanswered: Filter subform data using combo box in main form
I have been staring at this for a couple hours. I am fairly new to Access, so bear with me.
I have a form that I use to enter data into a table (Invoice Details) for tracking fleet vehicle maintenence. One of the form fields is a combo box which is controlled / populated by a field (VehicleID) in another table (Assets). The form contains a subform.
As far as the subform goes, this is what I am trying to do...
The subform is populated by the 'Invoice Details' table (The same table that the main form populates). I would like filter the records to show the maintenence history for a particular vehicle as it is selected in the combo box in the main form.
I have created a query (Invoice Details Query) to filter the records based on the value of the combo box in the main form. I created the subform (Invoice Details Query Subform) from the query. I also created an OnChange expression for the VehicleID combo box in the main form:
=[Invoice Details Query subform].Requery
It (almost) does what I am looking for....
The filter for the subform works fine, but only as I scroll through the records in the main form. As I change selections in the combo box, I get no records showing at all in the subform until I go to another record in the main form.
I hope all this made sense....
I know I am missing something fairly simple, so if someone could smack me in the forehead with the solution, I would greatly appreciate it.
I'm curious why both the main and sub form's are bound to the same table. That's unusual, and can lead to an error (it can say another user has the data locked when you try to edit, except the "other user" is you).
It would be more common for the main form to be bound to the assets table. With master/child links, the subform would always display the records for the vehicle in the main form.
I say that having created a vehicle maintenance application, and that's how I set it up. The main form displays the basic vehicle data, the subform displays the maintenance history. I also have a combo on the main form to select a vehicle to display.