T
Trisha
Hi, I have a report which is used to print multiple
records, like with continuous forms.
This report is based upon a query that has parameters like
Forms!frmSearch.txtField
There are two different ways to pass the selected records
to this report:
The first one is using the search form frmSearch. The
search form displays the results in a subform (based upon
the query) and offers a print button besides, which opens
the report. This one is straight forward and works well.
The problem is that I also want to offer the user
prefedined searches: So on the main menu, he should have
the option whether to specify the search criteria himself
(-> search form) or whether he wants to use one of the
predefined searches.
The predefined searches should use the same report, but
somehow pass the values from code.
I have tried two things:
DoCmd.OpenReport "rptReport", acViewPreview,,"[Forms!
frmSearch.txtField = 'something' AND ...."
This doesn't work because I have around 20 criteria which
need to specified, so the WHERE clause doesn't accept all
of them, and cuts them somewhere in the middle. I tried
concatenating two or more strings but they still get cut
when put in the WHERE clause.
The second thing is using QueryDef:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdffrmSearch As DAO.QueryDef
Set db = CurrentDb()
Set qdffrmSearch = db.QueryDefs("qryfrmSearch")
qdffrmSearch![Forms!frmSearch.txtField] = "whatever"
....
Set rs = qdffrmSearch.OpenRecordset()
DoCmd.OpenReport,"rptReport"
This doesn't work either, it still asks for the query
parameters when the DoCmd statement is reached.
Please please help! I'm not that great at VBA and I'm
getting desperate!
Sorry for the long post, and thanks in advance
Trisha
records, like with continuous forms.
This report is based upon a query that has parameters like
Forms!frmSearch.txtField
There are two different ways to pass the selected records
to this report:
The first one is using the search form frmSearch. The
search form displays the results in a subform (based upon
the query) and offers a print button besides, which opens
the report. This one is straight forward and works well.
The problem is that I also want to offer the user
prefedined searches: So on the main menu, he should have
the option whether to specify the search criteria himself
(-> search form) or whether he wants to use one of the
predefined searches.
The predefined searches should use the same report, but
somehow pass the values from code.
I have tried two things:
DoCmd.OpenReport "rptReport", acViewPreview,,"[Forms!
frmSearch.txtField = 'something' AND ...."
This doesn't work because I have around 20 criteria which
need to specified, so the WHERE clause doesn't accept all
of them, and cuts them somewhere in the middle. I tried
concatenating two or more strings but they still get cut
when put in the WHERE clause.
The second thing is using QueryDef:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdffrmSearch As DAO.QueryDef
Set db = CurrentDb()
Set qdffrmSearch = db.QueryDefs("qryfrmSearch")
qdffrmSearch![Forms!frmSearch.txtField] = "whatever"
....
Set rs = qdffrmSearch.OpenRecordset()
DoCmd.OpenReport,"rptReport"
This doesn't work either, it still asks for the query
parameters when the DoCmd statement is reached.
Please please help! I'm not that great at VBA and I'm
getting desperate!
Sorry for the long post, and thanks in advance
Trisha