Using parameters in reports and forms

[Mani]

Hi! Please bear in mind that I am still learning the ropes in the Access
worlds, so pardon my lack of terms. Here's my question. I want to create a
forms which pulls up a report that I would have created already, but on the
form I want to be able to have parameters so that when the report is pulled
up, it is pulled up based on what I type in the parameter. How do I
accomplish this? Please help, I need to get this done asap and I kindly
thanks in advance!!

 

[KARL DEWEY]

On your form put an unbound text box, list box, or combo box to enter or
select the report criteria.
In the query design view grid, for the report, in criteria row under the
corresponding field enter this --
[Forms]![YourFormName]![unboundObjectName]
OR
Like [Forms]![YourFormName]![unboundObjectName] & "*"
OR
Like "*" & [Forms]![YourFormName]![unboundObjectName] & "*"
OR
Like "*" & [Forms]![YourFormName]![unboundObjectName]

based on how you will be entering the criteria -- exact, beginning, some
part, or ending.

 

[Bill]

Mani,
You can pass filter expressions and arguments to
reports in the DoCmd.OpenReport method, though
arguments are not supported until Access 2002 and
later.

Assuming you have a command button or the equivalent
on your form plus a text-box or the equivalent in which
the user can specify optional parameters (arguments)
appropriate to the report, you can include the argument(s)
to the OpenReport in the OnClick event of your command
button.

Then, in the OnOpen sub of your report you can reference
the arguments in Me.OpenArgs. (Remember that OpenArgs
for Reports isn't supported until Access 2002 and later
versions.)

Bill

 

[Mani]

Hi Bill! I've partially tried what you suggested, but for some reason the
report is not running correctly. Let me tell you the steps I took in
attempting this task.
1) I have a query of all the fields I want to display on the report the
name of the query is qry0809Services.

2) I created a report based on the query (qry0809Services). I named it
rptServices0809.

3) I created a form with 3 combo boxes (EDA, School, Serivce) and I have 2
unbound text (Start Date) and (End Date). Then I titled all my combo boxes
as needed. Now.. this is where I am not sure if I did this part correctly, I
clicked on the Command Button, which requires me to choose category and
actions, which I do not want to do, so I clicked on Cancel, and I still have
a command button on the form. So then, I went to properties, and changed the
caption of the command button to OK. Then I clicked on the Event Procedure
and clicked on the Code Builder and typed:
Private Sub cboOK_Click()
Me.Visible = False
End Sub
Then I saved my form as frmParameterTesting

4) I went back to the qry0809Services and typed all the criteria as needed
(i.e. forms!frmParameterTesting!cboFindEDA,
forms!frmParameterTesting!cboFindSchool,
forms!frmParameterTesting!cboFindService, and Between
forms!frmParameterTesting!StartDate and forms!frmParameterTesting!EndDate

5) Now I went back to the report (rptServices0809) and coded the report for
the On Open Event and Close Event. For the Open Event, I typed in
DoCmc.OpenForm, "frmParameterTesting",,,,,acDialog and for the Close Event I
typed in DoCmd.Close acForm, "frmParameterTesting".

Wheeeewww..... nowwww... I closed the report after saving it.

6) I reopen the rptServices0809 (now I see to command buttons, the Open
Form and Close Form, which initially I created the OK button, but that
disappeared I don't know whether or not the Open and Close Form appears
because I coded that on the report , anyways.... I selected what I wanted and
typed in the date range I wanted. Once I click on the Open Form button, it
said:

"The report name ‘rptParameterTesting†you entered in either the property
sheet or macro is misspelled or refers o a report that doesn’t exit.
If the invalid report name is in a macro, an Action Failed dialog box will
display the macro name and the macro’s arguments after you click OK. Open
the Macro window, and enter the correct report name."

PLEASEEEEEEEE HELLLLLPPPPP.... I've Tried doing this twice.... and still no
output of the report!!! Please help me Please, I'm dying of exhaustion in
trying to figure this out...... crying.... Any help is highly appreciated....
Thanks Bill!!

 

[Mani]

Ok.. Bill.. I think I partially fixed this after I typed up my second badge
of questions.. anyhow... now.. I'm able to select from the form and all...
but now.. when i click ok to run the report... I get a blank report.. no
data is retreived. I know the data, since i selected the ones i know for
sure should display on the report.. but i am getting blank reports.... what
am I doing wrong???

 

[Bill]

Mani,
I'm a little confused here. If you want your form to open
a report, whose ControlSource is qry0809Services, and
based the report on some date range, then it would
be your form that would have two unbound text boxes
where one would enter the desired date range. With both
dates having been entered, your command botton on the
form would have an OnClick event with code like this:
(The command button on your form would remain visible
at all times.)

Private Sub MyCmdButton_Click()
Dim strWhereExp as String
'Test to be sure date fields not blank (not verified for format)
If Len(Me.StartDate & "") > 0 AND Len(Me.EndDate & "") > 0 Then
strWhereExp = "MyStartDateField >= " & Me.StartDate _
"AND MyEndDateField <= " & Me.EndDate
DoCmd.OpenReport "rpt0809Services",acPreview,,strWhereExp
End If
End Sub

Is this what you're trying to accomplish?

Bill

 

[Mani]

Hi Bill,
Ok... lets ignore what I sent earlier... i figure how to do it using one
combo box (EDA) and two unbound text boxes for the (Start Date and End
Date). Ok.. so that's working fine.. but now my question is... can I add
multiple combo boxes on the form with criteria. If so, how can I achieve
this?? I want to add a combo box, possibly name it (School) so I can select
the schools I want, and also another combo box possibly name it (Service
Type) so I can select the services I want. I hope that makes sense... I
would appreciate any help.. Thanks in advance again ..

 

[Bill]

What do you want to do, add the service type and
school to the "WHERE clause" of the report? I.e.,
generate reports for a particular service and school
for a time window?

Bill

 

[Mani]

Yes, actually I want to generate reports for a particular EDA for a
particular school.

 

[Bill]

Mani,
Okay, just add the combo boxes to your form that
are bound to the two items "service type" and
"school". Now, since you'll want to have the
option to create a report independent of those two
items, I suggest you create some mechanism whereby
the user can specify "Ignore service type and school.
I'm assuming here that you have a table of schools
and service types.

You'll need to check the value of each combo control
before appending the added specification to the WHERE
clause, as I posted yesterday.

(Mani, I've not shown any argument checking to our
discussion here, but you need to add some code that
insures that there are valid values added to the WHERE
clause and handle the errors accordingly.)

strWhereExp = "MyStartDateField >= " & Me.StartDate _
"AND MyEndDateField <= " & Me.EndDate _
"AND my school stuff
here.............................. _
"AND my service stuff here

Bill

 

[Mani]

Hi Bill! Thanks so much for the reply... I'm just confused as in the
beginning... but I guess I have to take baby steps. I went ahead and took
another direction in developing the reports with parameters, by not adding
more combo boxes... I only have one combo box, although I ideally I wold love
to have another one... but I'm just really really confuse.. to make a long
story short.... Ok, so I got my form and report to run.. but there is just
ONE problem now... Ok.. so I have a form, with two command buttons (OK and
Cancel). The OK button is working fine, but when I click the Cancel button,
the parameter dialog pops up that says "Enter Parameter Value" for the EDA
combo box, StartDate and EndDate. I hope this makes sense. When I click
cancel, I want the dialog box to automatically close. Please HELP ME!!!! :-(
Thanks in advance...

 

[Mani]

Bill,
I read what you suggested, but how should I implement the needed task? What
do I do now? I am still very new to Access so I need step-by-step
information.. Please help me... Thanks in advance again.

 

[Bill]

Not sure Mani how I can be of help to you, in that I don't know
the state of trouble you've having at this point.

How big is your mdb file? First, Click on Tools -> Database Utilities
-> Compact and Repair Database, then look at the size of the mdb
file using Windows Explorer. If you're having such horrendous problems,
you might want to zip the mdb and send it to me so I can have a look
at what might be causing you so much grief.

Where in the world are you? Your posting times seem to be quite a
bit different than where I am on the west coast of the USA.

Bill
bill no space stanton at psln dot com

 

[Mani]

Bill,
Thanks for the reply again.. I am in the Midwest where there are only
cows..lol.. ok.. but on a serious note... I don't know if I should zip the
file (not that I don't trust your sincere intention), but I have records of
students' confidential information... but can you just tell my why my cancel
button is not working?? As follows are my codes to the report:

Private Sub Report_Close()
DoCmd.close acForm, "frmServices0809Parameter"
End Sub

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "frmServices0809Parameter", , , , , acDialog
End Sub

And here are the codes to my form:


Private Sub Cancel_Click()
DoCmd.close
End Sub

Private Sub OK_Click()
Me.Visible = False
End Sub

I hope this will help. I highly appreciate your help. Thanks in advance
again!!

 

[Bill]

Well, like I said before, you apparently had a previous version
of your database that made a reference to objects that are no
longer present. Bring up your application in design mode and
click on VBA to view the code sheet. With that, you can use
the Object Browser to find the offending report or form on the
left and the objects it references. Also, if you click on view,
you can look at object dependencies.
Hope this helps.
Bill

 

[Mani]

I am totally lost of what the objective is that you're wanting me to do. I
went to the VBA view and saw the codes that I've seen already. I don't see
the view and object dpendencies you're talking about. I'm still at a
complete lost... Please help.

 

[Bill]

When viewing VBA, there's a object browser on the
toolbar. Obviously, you'll have to experiment with it
before you learn how to correlate object references
from the various forms, reports and modules that are
listed on the left side of the screen.

Mani, I always like to encourage folks in the learning
process and with you also. However, my sense is that
you have a application design and database structure
that is giving you more fits than one would ordinarily
expect. My offer to examine your application and
database still stands, the confidential nature of the data
notwithstanding..............I do that all the time.

Bill

 

[Mani]

Should I zip you the file?

When viewing VBA, there's a object browser on the
toolbar. Obviously, you'll have to experiment with it
before you learn how to correlate object references
from the various forms, reports and modules that are
listed on the left side of the screen.

Mani, I always like to encourage folks in the learning
process and with you also. However, my sense is that
you have a application design and database structure
that is giving you more fits than one would ordinarily
expect. My offer to examine your application and
database still stands, the confidential nature of the data
notwithstanding..............I do that all the time.

Bill

 

[Bill]

Yes, I suspect you'll need the compression. We might
have problems at my end if the resulting zip file is much
bigger than 7.5MB, but I'll pursue a solution to that if
it is.
Bill

 

[Bill]

I'll need to authorize receipt if your ISP is gmail.