Open report from a form with the same criteria used to open the fo

[scratchtrax]

I am in 2000. I have a selection main menu form with several combo-boxes
that are the selection criteria to open another form to the correct selection
set. I would like to add a command button to the second form that will open
my report and contain the same set of selected records.

Right now, the user enters info into the combo & text boxes and clicks a
button. These selections and entries are the sql selection query criteria
used to open the second form to the selected set of records when the user
clicks the button. I'd like to add a button to that form that will open (or
print) a report that contains the same set of records.

I've considered going back to the main menu form and adding the button there
to go right to the report (which I'd rather not do). Or, perhaps saving the
sql selection criteria into the second form and using it again to open the
report from the second form. Any ideas, thoughts or opinions would be
gratefully appreciated. If saving the sql statement is even possible. I've
used open args before but this would have to be a little different.

 

[Allen Browne]

Presumably you opened your 2nd form with something like this:
Dim strWhere As String
strWhere = "SomeField = 999"
DoCmd.OpenForm "Form2", , , strWhere

If so, the WHERE string is not in the Filter of the form. Assuming the
report is based on the same table(s), you can use that as the WhereCondition
of OpenReport, i.e.:
Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

(In Access 2002 and later, if you filter on a lookup combo, the filter
string may contain something the report can't parse. If that happens, you
need to modify the RecordSource for your report so it is a filter that uses
the same alias for the table name for the combo's RecordSource, so it can
understand the name.)

 

[scratchtrax]

Allen,
Thank you. That was perfect. I am going to be bringing/using this on
2002 & 2003, so I am trying to make sure that I understand your additional
tip:

(In Access 2002 and later, if you filter on a lookup combo, the filter
string may contain something the report can't parse. If that happens, you
need to modify the RecordSource for your report so it is a filter that uses
the same alias for the table name for the combo's RecordSource, so it can
understand the name.)

I took the form object I had and saved it as a report object. After reading
your above tip, I look at the record source for the report and it is the same
table as the source for the form. Will that be '...something the report
can't parse...' in 2002 or 2003? I guess I'm asking about the part where it
should be changed to the 'combo's RecordSource' as there are several combos
and text boxes used to build the criteria.

....am I reading too much into this?
Best Regards & Thank You!
-M

 

[Allen Browne]

If you don't have the problem, there's no need to fix it.

The probem arises where you have a combo where the bound column is hidden
(zero-width.) If you filter on this control, in Access 2002 and later you
end up with a filter string that contains something like this:
Lookup_Field1 = 'TheTextYouSee"
instead of:
Field1 = 99
where 99 represents the number in the actual field.

If you are interested in some code that parses this kind of thing, there's a
somwhat convoluted example here:
http://allenbrowne.com/AppFindAsUTypeCode.html