Access 2000 reports with Input Parameters

  • Thread starter Thread starter Lauren Quantrell
  • Start date Start date
L

Lauren Quantrell

I have a report. On the report's OnOpen event I have a routine that
decides what the recordset is going to be:
Select Case Forms!myForm.MyMethod
Case 1
me.recordsource = "myRecordSource1"
Case 2
me.recordsource = "myRecordSource2"
etc...
End Select

This works fine ONLY IF:
I have the input parameters already in place in the report's design
view
AND
only if there is already a recordsource specified for the report in
design view.
Otherwise I get a parameter prompt.

Shouldn't I be able to construct it like this the following?:
Select Case Forms!myForm.MyMethod
Case 1
me.inputparameters = "@myID = Forms!myForm.myID, @myString =
Forms!myForm.MyField"
me.recordsource = "myRecordSource1"
Case 2
me.inputparameters = "@myProductID = Forms!myForm.myProductID,
@myWhatever = Forms!myForm.MyWhatever"
me.recordsource = "myRecordSource2"
etc...
End Select

It seems like this should work because I'm changing the input
parameters before changing the recordsource, but I get a parameter
prompt here.

Any help on how to do this is appreciated.
lq
 
It is not clear if you MUST change the recordsouce of the report?

Perhaps you can base the report on a query, or even a table, but then simply
set the conditions?

I never did like putting form references into the sql. I don't know of any
other database system that allows this anyway. I would keep the query and
the sql nice clean and readable.

However, reports *OFTEN* need certainly conditions and parameters. The
solution I use is simply make a nice report prompt form. The user selects
things like sales rep, date range or whatever you desire to ask for. The
user then hits a print preview button, and then can review the report.

The solution I use is to simply build your own where clause in code, and
pass that to the reports where clause. This approach gives lots of
flexibilities, you don't deal with dumb prompts, and even better is that
your sql in queries is nice and clean without a bunch of confusing forms!ref
stuff.


Take a look at the following screen shots to see what I mean:

http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html

So, I simply build a nice prompt form, and let the user enter values into
that.

The code looks like:

dim strWhere as string

if isnull(me.cboSalesRep) = false then
' set condition for sales rep
strWhere = "SalesRep = '" & me.cboSalesRep & "'"
endif

' set condistion for sales date
if isnull(me.dtStart) = false then
if strWhere <> "" then strWhere = strWhere & " and "
'
strWhere = strWhere & " SalesDate = #" & format(me.dtstart,"mm/dd/yyy") &
"#"
endif

docmd.OpenReprot "thereport",acviewPreview,,strWhere

Anyway, you can get the idea here, and that is we create our own LEGAL sql
"where" clause, and pass that to the report.

The other real bonus is that if you look close at the same screen shots, I
OFTEN MENTION to the user to just simply leave the textbox/combo box blank
for all, and again this much easier then trying to write a query that some
how also allows the user to leave the value blank for "all" values.
 
Thank you for your response and for the form examples. There are
plenty of ways to use those examples and I'll take a look further.
In the case I was mentioning, I have a form that contains a list of
names of contacts that match various criteris. This form is populated
from one of many different stored procedures that involve dozens of
parameters. All I want to do is to present the user with a
print/preview button, and whatever records are on the form are
printed. There's no reason to present the user with a form listing all
of the parameter options to choose from again.
Thus...
I open a report.
I want the report to reflect the recordset (a stored procedure) that
was used to polulate the form.
I want the report to pass the same parameters to the same stored
procedure as was passed to that stored procedure when it populated the
form.
Using an Access ADP Project this is not as simple as it was in MDB
databases where all I would do is put the following in the OnOpen
Event of the report: Me.RecordSource =
Forms!frmFormName.RecordSource

lq
 
Back
Top