link to a separate report from a report

[bv21]

I have a field in a report (call it FIELD1) that I have created a macro for - the macro generates a new report with all the records that contain the answer for FIELD1 - I would like to be able to click on this field and have the report generate based on what is in that field currently - but when I click on the field it pulls up an [lookup field1] box and I have to make sure I type in exactly what I need - is there anyway to have that lookup linked to a table of the specific records I want to search by? or even better if it was able to understand the entry that was in FIELD1 that was just clicked on return those records in the linked report? your help is greatly appreciated.

[pbaldy]

Is this what you're trying to do?

BaldyWeb wherecondition

[bv21]

thanks - i'll try that - is there anyway to have: DoCmd.OpenForm "SecondFormName", , , "FieldName = '" & Me.ControlName & "'"

where "FieldName = '" will pull whatever is in that field? or will I have to specify prior, what that fieldname needs to be - thanks!

[Dal Jeanis]

The wherecondition parameter has to resolve at execution time to a string that is a valid limitation on the query that underlies the form. As long as whatever you code fits that description, then you're good. For instance, if you had two control on the form that the VBA was behind, and Control1 had a value of "FirstName" and Control2 had a value of "George", then a parameter

 "( " & Me.Control1 & " = '" & Me.Control2 & "')"

would resolve to

"( Firstname = 'George')"

which would be a valid limit if there was a field Firstname in the query for the form.

I've forgotten whether you get nothing or everything if the filter is not valid - I think everything.

[pbaldy]

Well, typically you know the field name (the code is run from that field after all), and you get the value from the form/report. If you really mean you get the field name too, it can be done.

Me.ControlWithField & " = " & Me.ControlWithValue

type of thing