How to display correct records in Subform based on dropdown in Form 2

[cfvcf]

I have a mainform that has a dropdown of "Categories". There is a Subform with a recordsource of detail records, which Category is a field. I want to display only the records in the subform that fit that Category selected.

I have done this before where I had built different queries based on what they selected, but I'd like to take the Dropdown selection and open the subform with those records. Below is what I coded when I used queries:
Me.subform_Inv_Detail.Form.RecordSource = qwyName
How can I change that to point to the actual field in the subform and only show the fields containing that Category?
Make sense? Thanks!

 

[lars7]

You could link the subform to the Main form through the Dropdown list box in properties like this:

Link Child Fields -> "Catagories"
Link Master fields -> "YourDropDownBox"

 

[cfvcf]

I guess I'm not sure where you put those properties on either the mainform with the dropdown of category or on the subform that uses the detail table with the field category. Are these form properties or table properties? Thanks!

 

[lars7]

Hi,

If you right click on the edge of your subform a shortcut menu will appear.

Choose the bottom option properties.

The property box will open and the all tab should be selected.

You will see the Child and Master field link options on the 3rd and 4th rows.

you should type in:

Link Child Fields -> "Category"
Link Master fields -> "YourDropDownBoxName"

try this and let us know if it works.

 

[cfvcf]

Works great!
But I have another question. Currently, I open the Main Menu (with the subform) when the app opens and from buttons on other forms. What I do is make sure the combobox is invisible when it opens but I want the subform to have all records in the table displayed. There's a button "Categories" and when that clicks it makes the Category combobox visible. It's when they click the Category combobox, the subform displays only records for that category.

The way it works now is that the subform is empty when the Main Menu frm displays but it works great when they click a category in the combobox. Is there a way to change the values for the
Link Child Fields -> "Category"
Link Master fields -> "YourDropDownBoxName"
to the Key ID of the recordsource that was previously in those fields before I made this change?
Thanks you have been so helpful!

 

[lars7]

Ok remove the master and child links from the sub form and leave the sub form unbound then try this in the after update event of your Dropdownbox:

Private Sub YourdropDownBox_AfterUpdate()

Dim frm As Form
Set frm = Forms!YourMainForm!YourSubForm.Form
frm.RecordSource = "SELECT YourTable.[Catagory], * " & _
"FROM YourTable " & _
"WHERE YourTable.[Catagory]= '" & Me.YourDropDownBox & "'"

End Sub

the sub form will open now with all the records shown and the dropdownbox will filter for your catagories.

Hope this helps.

 

[cfvcf]

I think that will work. I coded the Me.subform_Inv_Detail.Form.RecordSource = qwyName in the afterupdate event before. Thanks again, I'll try it tomorrow. Might be back with my problems with syntax...Have a good evening.

 

[cfvcf]

That did help - thanks so much!