Conditional Prompting for Parameter?

  • Thread starter Thread starter ghetto_banjo
  • Start date Start date
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?
 
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.
 
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?
Click to expand...

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.
 
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?
Click to expand...


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.
 
Back
Top