Using a combo box to display info in a subform

[PJ]

I found a great sample that I am currently using in a form. I have an
inventory listing shown as a data sheet in a subform on a main form. At
this time, the user chooses a location from a combo box on the form and
the code filters the subform resaults to only show the location in the
combo box. The code I found and am using is as follows:

Private Sub Combo0_AfterUpdate()

' Declare variables
Dim frm As Form
Dim strInput As Variant
Dim strFilter As String

' Store value selected in combo box by user
strInput = [Combo0]

' This is the line of code that refers to the subform from the main
form:
Set frm = Forms![testinvwithsub]![InvTbltestform].Form

' Build criteria string
strFilter = BuildCriteria("Building", dbText, strInput)

' Set Filter property to apply filter
frm.Filter = strFilter

' Set FilterOn property to show filtered records
frm.FilterOn = True

End Sub

What I would like to be able to do is be able to just print the
filtered resaults from a button. I already have a button that will
print all (all records from subform). Would it be easier to have the
combo box that displays queries that are broken down by location, then
have a button that prints the query that is picked from the combo box?
Or is there a way to only print out the filtered data as shown. Thanks
in advance, and thanks also to the poster that posted the original code.

 

[tina]

assuming that you're printing the subform records using a report object,
with code such as

DoCmd.OpenReport

you can add a button that uses the same OpenReport action, and includes a
WHERE clause to filter the report records as you wish. you'd probably have
to build the criteria much the same as you did to filter the subform
records. see the OpenReport Action topic in Help for more information on
its' arguments and how they work.

hth

 

[PJ]

Thanks alot for the help Tina! I have one more question though, how can
I have the same combo box show all the records again? Either by having
a null value in the combo box or maybe a "All" selection.

Thanks!

 

[tina]

either one should work. just write the options into your code for the one
button. something along the lines of

If IsNull(Me!ComboboxName) Then
DoCmd.OpenReport "ReportName"
Else
DoCmd.OpenReport "ReportName", , , "FieldName = " _
& Me!ComboboxName
End If

if the field you're filtering on is text rather than number data type, the
syntax would be

"FieldName = '" & Me!ComboboxName & "'"

hth

 

[PJ]

Thanks again Tina!

 

[tina]

you're welcome