Using parameters in reports and forms

  • Thread starter Thread starter Mani
  • Start date Start date
M

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!!
 
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.
 
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!!
 
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???
 
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 ..
 
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...
 
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,
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!!
 
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.
 
Should I zip you the file?

Bill said:
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
Click to expand...
 
Hi Bill! I've managed to figure out what I needed to do with the forms and
reports with parameters. I've attached a module to the form, so that the
form would close without having pop up parameters. All that is working
great! However, now that I've attached a module, for some reason my
switchboard is not working properly. When I click on the Switchboard manager
to edit items and what not, I get this message: Run-time error '6467': The
expression you entered is referred to an object that is closed or doesn't
exist.

Here's the code for my module:
Option Compare Database
Public bInReportOpenEvent As Boolean ' Is report in the Open event?

Function IsNull(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or
' Datasheet view.
Dim oAccessObject As AccessObject
Set oAccessObject = CurrentProject.AllForms(strFormName)
If oAccessObject.IsLoaded Then
If oAccessObject.CurrentView <> acCurViewDesign Then
IsNull = True
End If
End If
End Function

Is there something with the module that's why my switchboard isn't working
properly?? Please help!! Thanks in advance....
 
Hi Bill! I finally figured out by looking at examples and such to make my
parameter form close without the parameter value window popping up. So all
that is working fine after attaching modules to the form. However, now my
switchboard is not woking after I have implemented this task. When I clicked
the Switchboard manager and tried to edit items there is a message that pops
up as follows:

Run-time error '2467': The expression you entered refers to an object that
is closed or doesn't exist.

And then it gives me option to debug.... This was what that was highlighted:

Set oAccessObject = CurrentProject.AllForms(strFormName)

Here are the codes for my module to the parameter form:


Option Compare Database
Public bInReportOpenEvent As Boolean ' Is report in the Open event?

Function IsNull(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or
' Datasheet view.
Dim oAccessObject As AccessObject
Set oAccessObject = CurrentProject.AllForms(strFormName)
If oAccessObject.IsLoaded Then
If oAccessObject.CurrentView <> acCurViewDesign Then
IsNull = True
End If
End If
End Function

So it seems to me there is something wrong with the coding in the module.
Unless you think it's something else. Please help me.. Thanks in advance!!
Looking forward to hearing from ya!
 
Back
Top