Full Version: Filter Subform
UtterAccess Forums > Microsoft® Access > Access Forms
I need to filter my form to include only the records which include a specific vendor.
I have a "Service Call" form which shows all of our service calls. We have a subform called "expenses" linked to that main form based on the unique ticket number assigned to each call. I need to be able to filter the main form to show me just the records which have bought parts from "vendor x". Is that possible? If so, how do I do it?
Jack Cowley
Create a form with a combo box that lists vendors and a command button. Use code similar to this in the command button...
oCmd.OpenForm "frmServiceCall", , , "[VendorID] = " & Me.NameOfComboboxWithVendorID
Change object names as necessary to work with your actual forms and I assume that the table/query the form is based on has a VendorID...
Ok, the only problem with this idea is that the Vendor ID isn't listed on the "Tickets" form; only in the "Expenses" subform. So the command button won't find anything in the "Tickets" form based on the Vendor ID. The form "Tickets" and subform "Expenses" are linked by the work order ID number. Is there another way to solve this problem?
Please post relevant details of your Tables (Table names, PK, FK, ...), the SQL for the RecordSource for the main Form and the Subform, the LinkMasterFields / LinkChildFields.
lso, please advise if you need the RecordSource for the main Form to be updatable as (I guess at the moment) there are 2 ways of using a SubQuery to achieve what you want and one method is efficient but the Recordset is not updatable while the other is less efficient but the Recordset is updatable.
Table Names: TBL_Tickets & TBL_Expenses
Forms: FRM_Tickets-All (Main Form) & SUB_EXP_Tickets (Sub Form)
RecordSource for Frm_Tickets-All: FRMQ_Tickets-All (A query with all the fields of the TBL_Tickets table)
RecordSource for TBL_Expenses: FRMQ_Expenses (A query with all the fields of the TBL_Expenses table)
Linked master/child fields: WorkOrderNum
I'm a novice so I'm not sure what you mean by whether or not the recordsource is updatable. Explain a little and I'll see if I can answer you. Anything else you need?
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.