Repeated Parameter Requests

[jallen_45]

I created a report from a single query, and then I added two charts to the
report (using the data from the same query as the report). I would like to
enter a unique ID number to get a report with just one organization's data on
it (as opposed to every organization in the entire database). I get the
report with the desired charts, but I have to enter the unique ID number five
times! It seems that each of the charts require the unique ID number to be
entered 2 times because the report with out the charts only requires the ID
number to be enterred once.

What can I do so that I only have to enter the unique ID once and get the
report with both charts? Simple instructions would be great as I am still
quite the novice! Thank you very much in advance!

Joe

 

[Mikal via AccessMonster.com]

Hi, Joe.

The only way I know is to use a form to pass the parameter to the query.
Here's how I do it:
1. make a form with a text box and a button on it. Name the text box
something like tboMyID and name the form something like frmMyParameters.
Don't assign a record source to the form and don't assign a control source to
the textbox.
2. modify your query so that the parameter for the ID number is Forms!
[frmMyParameters}.[tboMyID]
3. Make the button open the report with the charts. Everytime the report or
chart needs information it will pick it up from the form instead of prompting
you for it.

Some of the brighter lights around here might have a better way of doing it
and if so, I'll be watching to improve my own technique.

Hope this helps.

Mike

I created a report from a single query, and then I added two charts to the

 

[George Nicholson]

This is the technique I find myself using most of the time for values I need
to reference over & over again.
It might not be your cup of tea.

- Open the VB editor (F11 or Tools>Macros>VBE)
- Insert>Module
- Copy & paste everything between Start & End Code into the module:

'************ Start Code ****************
Public glngProjectID as Long

Public Function GetProjectID() As Long
On Error GoTo ErrHandler

Do While glngProjectID = 0
' Prompt user for a value
glngProjectID = Clng(InputBox("Please enter a ProjectID"))
' Note: alternatives to a user prompt include:
' reading value from an open form,
' passing an optional value to this function,
' reading a value from a table, etc.
Loop
GetProjectID = glngProjectID

ExitHere:
Exit Function
ErrHandler:
MsgBox("Error in function 'GetProjectID'")
Resume ExitHere
End Function

'************ End Code ****************

-Replace the criteria parameters in your query with:
GetProjectID()
(and the parentheses are necessary)

- you should only be prompted for the ID once per report. After that the
stored value will be used.

- NOTE: that value will *remain* stored until you change it. You can either
change the ID to a new value with:
glngProjectID = (some new value)
or you set the ID to zero which would cause the prompt to appear the next
time the function is called:
glngProjectID = 0

Good places to change/set the value would include the CommandButton_Click
event that runs the report, some AfterUpdate event where the user has
selected a new ProjectID, etc.

Note that you can use GetProjectID() in any query and any code within your
app.

HTH,

 

[Mikal via AccessMonster.com]

This is the technique I find myself using most of the time for values I need
to reference over & over again.
It might not be your cup of tea.

<snip>

Like I said: Brighter Lights. Thank you, George. I'll be trying that out.
Just one question: Since you declared glngProjectID as long, doesn't that
restrict it to numeric data? If I wanted to use a different data type, say
text, should I declare something like gstrProjectID as string?

TIA

Mike

 

[jallen_45]

Thanks George!

Your technique solved my initial problem, but created another (my fault, I
should have thought more into the furture).

Here's the new issue: I did everything you said to and I only have to enter
the ID number once and the report comes up. However, when I close the report
and run another, the option to enter the ID number is not there because it
was changed with the first report (the initial zero value is gone). If I
close Access and reopen and rerun the report, I can change the ID number.

I forgot to mention that the report displays data for only one account
holder (report card), so I need to be able to run numerous reports in one
sitting without restarting Access.

Thanks again for your help earlier. Any information on my new question will
be greatly appreciated!

Joe

 

[George Nicholson]

- NOTE: that value will *remain* stored until you change it. You can

Assuming you are running reports via a command button, make sure you reset
the variable to zero before you open report. when the variable is zero, it
will trigger the prompt again:

glngProjectID = 0 '(or next-value-in-list, if you have a loop set up)
Docmd.Openreport "MyReport"

HTH,

 

[George Nicholson]

should I declare something like gstrProjectID as string?

yes

 

[jallen_45]

Okay that looks like it will fix the problem, but where do I put it and what
is the line structure (in a new module or macro)? Remember, I'm a novice!

Thanks!
Joe

 

[George Nicholson]

How are reports being run? via a command button? please post the code

 

[jallen_45]

The report can be run via a button in the switchboard or clicking on the
report itself. The report is intended for multiple users to get performance
data from their assigned organization, so they would normally use the
switchboard.

As for the code, which one? The report or switchboard?

Thanks again!

Joe

 

[George Nicholson]

-Open the report in design view
-View>Properties
-In the Properties window, find the OnOpen event
-Select [EventProcedure] and click the ellipse (...)
-You should now be in the VB Editor in a sub labeled:
Private Sub Report_Open(Cancel As Integer)
(This event sub may already have code in it, it may not).

- Add the following to the sub (after an existing Dim statements, before any
other executable code)
glngProjectID = 0

-Save the report

That should reset the variable before the report's query runs, thereby
causing the prompt to appear *every* time the report is opened. It should
make no difference whether the report is opened from the switchboard or
opened directly from the database window.

Consider adding the same line to the Close event of the report, just to make
sure the current value gets "cleared".

HTH,

 

[jallen_45]

That did it. Thank you VERY much for your help on this one!

Joe

-Open the report in design view
-View>Properties
-In the Properties window, find the OnOpen event
-Select [EventProcedure] and click the ellipse (...)
-You should now be in the VB Editor in a sub labeled:
Private Sub Report_Open(Cancel As Integer)
(This event sub may already have code in it, it may not).

- Add the following to the sub (after an existing Dim statements, before any
other executable code)
glngProjectID = 0

-Save the report

That should reset the variable before the report's query runs, thereby
causing the prompt to appear *every* time the report is opened. It should
make no difference whether the report is opened from the switchboard or
opened directly from the database window.

Consider adding the same line to the Close event of the report, just to make
sure the current value gets "cleared".

HTH,

The report can be run via a button in the switchboard or clicking on the
report itself. The report is intended for multiple users to get
performance
data from their assigned organization, so they would normally use the
switchboard.

As for the code, which one? The report or switchboard?

Thanks again!

Joe