Record Source for a Report

[Brando]

My access database opens with a search form that allows the user to enter multiple criteria into various fields. A command button then builds a filter based on the user’s choices and displays matching records in a list box on a subform. The On Click code is:

Private Sub btnSearch_Click()

' Update the record source
Me.frmsubResults.Form.RecordSource = "SELECT * FROM qryBizMax " & BuildFilter

' Requery the subform
Me.frmsubResults.Requery
End Sub


The record source of the subform is:

SELECT * FROM qryBizMax;

(it works great up to here. I have no idea how, except that the code in the record source changes with each new search. Crazy.)
I want the user to be able to generate a report of the resulting data. The problem is, the subform is the Access equivalent of a one-way, dead-end street. The record source of the report cannot be the subform, nor can it be the query, because the user choices are applied in the filter.

I am open to any suggestions.

 

[pbaldy]

There are any number of ways to do this. One would be to set the report's source (in its open event) to the recordsource of the subform. Another would be to use the same function to build a wherecondition to use with OpenReport. I think you'll find if you study the code you already use and figure out how it works, you'll have a much easier time adapting it to other uses.

 

[Brando]

Per your suggestion, I tried putting the following on the OnOpen Event of the report:


Private Sub Report_Open(Cancel As Integer)

RecordSource = "SELECT * FROM qryBizMax " & BuildFilter

End Sub


I followed this with the function that builds the filter (after changing the code to refer to the appropriate text boxes, etc. on the form.)
However, the report only runs successfully if there are no parameters entered on the search form. What am I missing?

 

[pbaldy]

Add

Debug.Print BuildFilter

to see what the function is returning (the result will display in the Immediate window in the VBA editor).

 

[Brando]

Paul,

Thank you, thank you. Your initial advice about setting the report's source in its open event to match that of the subform was the key. Then, when I added the code for the filter, I proceeded to mess up it up by not telling the report how to find the fields on the form. However, I fixed it and it works great. I appreciate your help!

P.S. Very cool funtion this DeBug thing.