Conditional Prompting for Parameter?

G

ghetto_banjo

Hello,

I have a report, and i want to conditionally prompt a user for a
data. I have this in the control source of a text box on my report.

=iif([Suffix] = 0, " ", [Enter Parameter])


However, Access sees that prompt in the iif statement, and wants a
value for it before it even checks the conditional statement. So even
when [Suffix] = 0, it wants to know what [Enter Parameter] equals.


Any thoughts?
 
G

ghetto_banjo

Well i have decided to make my report based on a table instead of a
query, and before i open the report, i am using VB code to get the job
done with a combination of Dlookup, InputBox, and UPDATE queries.
Still interested if my above question is possible though...


Thanks.
 
P

pietlinden

Hello,

I have a report, and i want to conditionally prompt a user for a
data.  I have this in the control source of a text box on my report.

=iif([Suffix] = 0, " ", [Enter Parameter])

However, Access sees that prompt in the iif statement, and wants a
value for it before it even checks the conditional statement.  So even
when [Suffix] = 0, it wants to know what [Enter Parameter] equals.

Any thoughts?

You know that Reports are read-only, right? If you want to prompt the
user for information, you have to do it BEFORE the Open event of the
report and then pass the arguments in the Open event. I think there's
an article on Access Web about showing the filter passed to a report
when the report is open.
 
D

Dirk Goldgar

ghetto_banjo said:
Hello,

I have a report, and i want to conditionally prompt a user for a
data. I have this in the control source of a text box on my report.

=iif([Suffix] = 0, " ", [Enter Parameter])


However, Access sees that prompt in the iif statement, and wants a
value for it before it even checks the conditional statement. So even
when [Suffix] = 0, it wants to know what [Enter Parameter] equals.

Any thoughts?


You could write a VBA function like this:

'------ start of "air code" ------
Function MaybeParameter(pSuffix As Variant) As String

If pSuffix = 0 Then
MaybeParameter = " "
Else
MaybeParameter = InputBox("Enter Parameter:")
End If

End Sub
'------ end of "air code" ------

Then you could set the controlsource of the textbox to:

=MaybeParameter([Suffix])

However, that will prompt the user every time the section containing that
text box is executed -- possibly multiple times per record! Did you want to
have one parameter prompt, no matter how many times the function is called
by the report? Then you might use this alternate version:

'------ start of "air code" #2 ------
Function MaybeParameter(pSuffix As Variant) As String

Static varParamValue As Variant

If pSuffix = 0 Then
MaybeParameter = " "
Else
If IsEmpty(varParamValue) Then
varParamValue = InputBox("Enter Parameter:")
End If
MaybeParameter = varParamValue
End If

End Sub
'------ end of "air code" #2 ------

You would want to put that function in the report's module, not in a
standard module, so that the static variable is reset each time the report
is opened.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top