Unwanted "Parameter Value" Msg box

[April Slater via AccessMonster.com]

I have created a form to filter records for print to a report. Problem is
that when you click the button to preview the report, the "Enter Parameter
Value" msg box appears. I can't see where that is being called in the code.

Private Sub View_Report_Click()
On Error GoTo Err_View_Report_Click
Dim strWhere As String
strWhere = "[Type of Contract]='" & Contract.Column(0) & "' and " & "
[Discipline]='" & Discipline.Column(0) & "' and " & "[Project Name]='" &
ProjectName.Column(0) & "'"


DoCmd.OpenReport "Lessons Learned", acPreview, , strWhere

Exit_View_Report_Click:
Exit Sub

Err_View_Report_Click:
MsgBox Err.Description
Resume Exit_View_Report_Click

End Sub

 

[Guest]

hi,
access is confused. that is why your are getting the
message.
i think it's in your where clause.
the items in bracket i assume are controls on the form. if
that is true then you'll need to structure it like this:
[fieldname] = Forms![formname]![controlname]
The fieldname is the name of a field in the underlying
table or query of the report you want to open. The
controlname is the name of the control on the form that
contains the value you want records in the report to match.

 

[April Slater via AccessMonster.com]

I see your point and I reworked the string. Thanks a ton, but now I get a
string error and I can find it to fix it no matter what. Here is my
current code:
Private Sub Preview_Click()
On Error GoTo Err_Preview_Click

Dim strWhere As String
strWhere = [Type of Contract] = Forms![Create Report]![Contract] And
[Discipline] = Form![Create Report]![Discipline] And [Project Name] = Forms!
[Create Report]![ProjectName]

DoCmd.OpenReport "Lessons Learned", acPreview, , strWhere

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
MsgBox Err.Description
Resume Exit_Preview_Click

End Sub

The error I get is "MS Access can't find the field '|' referred to in your
expression.

I have totally retyped the expression and still get this. Where is this
charactor in the expression! I don't see it.

April

 

[John Vinson]

I see your point and I reworked the string. Thanks a ton, but now I get a
string error and I can find it to fix it no matter what. Here is my
current code:
Private Sub Preview_Click()
On Error GoTo Err_Preview_Click

Dim strWhere As String
strWhere = [Type of Contract] = Forms![Create Report]![Contract] And
[Discipline] = Form![Create Report]![Discipline] And [Project Name] = Forms!
[Create Report]![ProjectName]

DoCmd.OpenReport "Lessons Learned", acPreview, , strWhere

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
MsgBox Err.Description
Resume Exit_Preview_Click

End Sub

You need to build up the value of strWhere by concatenating string
constants (literal values between " delimiters) and string variables
(your form references), using the & operator to concatenate the
pieces. In addition, when you're building a WHERE clause searching for
values in text fields, those values must be delimited by quote
characters - either ' or ". Since it's conceivable that a project name
might contain an apostrophe (April's Big Database Project) you're best
off using ", which is Chr(34).

Try

strWhere = "[Type of Contract] = " & Chr(34) _
& Forms![Create Report]![Contract] & Chr(34) _
& " And [Discipline] = " & Chr(34) _
& Forms![Create Report]![Discipline] & Chr(34) _
& " And [Project Name] = " & Chr(34) _
& Forms![Create Report]![ProjectName] & Chr(34)

So if the Type of Contract is "New", Discipline is "Database Design",
and Project name is "April's Big Database Project" you'll assemble all
these pieces to set strWhere to

[Type of Contract] = "New" AND [Discipline] = "Database Design" AND
[Project Name] = "April's Big Database Project"


John W. Vinson[MVP]