Filter a subform

[Denver]

I have Form with a subform and combobox..
i have this rowsource for my combo58
RowSource:SELECT DISTINCT location FROM [Data Entry2] UNION SELECT ' All
Locations' FROM [Data Entry2] ORDER BY location;

i set Link childs and Link Master fields to blank.. so that all my records
will be displayed in my subform.

can anyone help me how can i write my codes for my Combo58 that when i
select a certain location it apply filters and when I select "All locations'
it will display back again all my records in the subform?

thank you and more power

 

[Marshall Barton]

I have Form with a subform and combobox..
i have this rowsource for my combo58
RowSource:SELECT DISTINCT location FROM [Data Entry2] UNION SELECT ' All
Locations' FROM [Data Entry2] ORDER BY location;

i set Link childs and Link Master fields to blank.. so that all my records
will be displayed in my subform.

can anyone help me how can i write my codes for my Combo58 that when i
select a certain location it apply filters and when I select "All locations'
it will display back again all my records in the subform?

Use the combo box's AfterUpdate event:

If Me.Combo58 = " All Locations" Then
Me.subformcontrol.Form.FilterOn = False
Else
Me.subformcontrol.Form.Filter = _
"Location=" & Me.Combo58
Me.subformcontrol.Form.FilterOn = True
End If

If Location is a Text field in the table, then the filter
would be:
"Location=""" & Me.Combo58 & """"

 

[Scott Lichtenberg]

You're half way there with the UNION query. You need to add code to the
AfterUpdate event of the combo box. There are two ways to filter the
records in your subform. One way is to use the Filter and FilterOn
properties. The other is to change the recordsource of the subform.
Here's some sample code for both ways.


Dim f as Form
Set f = Me!MySubformControlName.Form 'Create a variable to represent
the subform. It's easier to type

'Filtering

If Me!combo58 = "All" Then
f.FilterOn = False 'Turn off the filter
Else
f.Filter = "Location = '" & Me!combo58 & "'" 'Note the embedded
quotes. If your location is numeric, you don't need them
f.FilterOn = True
End If

'Recordsource
If Me!combo58 = "All" Then
f.Recordsource = "SELECT * FROM MyTable"
Else
f.Recordsource = "SELECT * FROM MyTable WHERE Location = '" & combo58 &
"'"
End If

Use the filtering method if your subform table contains a relatively small
number of records (hundreds). Use the recordsource method if your subform
table contains thousands of records.

Hope this helps

 

[tina]

a third option is to set the subform's RecordSource to a query. add criteria
to the "location" field in the query, as

Forms!FormName!Combo58 Or Forms!FormName!Combo58 = "All locations"

replace FormName in both places above with the correct name of the main
form, of course.

then add code to Combo58's AfterUpdate event, as

Me!SubformName.Form.Requery

replace SubformName with the correct name of the subform control within the
main form. to get that name, open the main form in Design view. click once
on the subform, within the main form, to select it. open the Properties box,
click on the Other tab, and look at the Name property. that is the "name"
you want to use to replace SubformName in the line of code above.

hth

 

[Denver]

Thank you Marshall Barton.

Thank you Scott Lichtenberg.

Thank you tina.

thank you guys i really appreciate it works wil.

a third option is to set the subform's RecordSource to a query. add criteria
to the "location" field in the query, as

Forms!FormName!Combo58 Or Forms!FormName!Combo58 = "All locations"

replace FormName in both places above with the correct name of the main
form, of course.

then add code to Combo58's AfterUpdate event, as

Me!SubformName.Form.Requery

replace SubformName with the correct name of the subform control within the
main form. to get that name, open the main form in Design view. click once
on the subform, within the main form, to select it. open the Properties box,
click on the Other tab, and look at the Name property. that is the "name"
you want to use to replace SubformName in the line of code above.

hth

I have Form with a subform and combobox..
i have this rowsource for my combo58
RowSource:SELECT DISTINCT location FROM [Data Entry2] UNION SELECT ' All
Locations' FROM [Data Entry2] ORDER BY location;

i set Link childs and Link Master fields to blank.. so that all my records
will be displayed in my subform.

can anyone help me how can i write my codes for my Combo58 that when i
select a certain location it apply filters and when I select "All locations'
it will display back again all my records in the subform?

thank you and more power