Can't get DoCmd.OpenReport to print only selected records

[Tony Girgenti]

Hello,

When i use the attached code my report always prints all records. I can't
figure out how to make it select only the records in the where option. When
i watch "strWhere" it shows "[COMPANY NAME] = BLAIR".

Any help would be appreciated.

Tony

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stDocName As String
Dim strWhere As String

strWhere = "[COMPANY NAME] = " & Forms![Form1]![Combo0]
stDocName = "CHF STATUS 2"

DoCmd.OpenReport stDocName, acPreview, strWhere

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

 

[Van T. Dinh]

Try:

strWhere = "[COMPANY NAME] = " & Chr(34) & Forms![Form1]![Combo0] &
Chr(34)

HTH
Van T. Dinh
MVP (Access)

 

[Rick Brandt]

Hello,

When i use the attached code my report always prints all records. I can't
figure out how to make it select only the records in the where option. When
i watch "strWhere" it shows "[COMPANY NAME] = BLAIR".

Any help would be appreciated.

Tony

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stDocName As String
Dim strWhere As String

strWhere = "[COMPANY NAME] = " & Forms![Form1]![Combo0]
stDocName = "CHF STATUS 2"

DoCmd.OpenReport stDocName, acPreview, strWhere

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

I'm surprised you don't get a parameter prompt. If Company name is text then it
needs to have quotes around it. Your Where string is actually saying "show
records where COMPANYNAME is equal to some field named Blair" not the literal
string "Blair".

Try...
strWhere = "[COMPANY NAME] = ' " & Forms![Form1]![Combo0] & " ' "

I put spaces in the above to make it easier to tell the single and double quotes
apart. You would not have those spaces in your actual code.

 

[Marshall Barton]

Hello,

When i use the attached code my report always prints all records. I can't
figure out how to make it select only the records in the where option. When
i watch "strWhere" it shows "[COMPANY NAME] = BLAIR".

Any help would be appreciated.

Tony

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stDocName As String
Dim strWhere As String

strWhere = "[COMPANY NAME] = " & Forms![Form1]![Combo0]
stDocName = "CHF STATUS 2"

DoCmd.OpenReport stDocName, acPreview, strWhere

In addition to the quoting, you need another comma:

DoCmd.OpenReport stDocName, acPreview, , strWhere

 

[Tony Girgenti]

Thanks Marshall, That worked.

Now, can I ask you how to stop the report from displaying nothing if there
are no matches in the table ? I want to display a message box instead.

Thanks,

Tony

Hello,

When i use the attached code my report always prints all records. I can't
figure out how to make it select only the records in the where option. When
i watch "strWhere" it shows "[COMPANY NAME] = BLAIR".

Any help would be appreciated.

Tony

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stDocName As String
Dim strWhere As String

strWhere = "[COMPANY NAME] = " & Forms![Form1]![Combo0]
stDocName = "CHF STATUS 2"

DoCmd.OpenReport stDocName, acPreview, strWhere

In addition to the quoting, you need another comma:

DoCmd.OpenReport stDocName, acPreview, , strWhere

 

[Steve Schapel]

Tony,

You can do this by putting some code on the On No Data event property of
your report. Something like this...
MsgBox "Sorry, no matching records.", vbInformation, "No Data"
Cancel = True

 

[Tony Girgenti]

Hi Steve. That works great.

Except, after i OK the message from msgbox, i get a message that says "The
OpenReport action was cancelled" with an OK button.

Is there someway to keep that from popping up ?

Thanks,
Tony

 

[Steve Schapel]

Tony,

Ah, sorry, I forgot about this bit. You need to put error handling in
your report printing procedure to ignore Err.Number 2501. Please post
back if you need more specific help with this.

 

[Tony Girgenti]

Hi Steve. I think i figured it out, but i'm not sure of why it does what it
does. Attached is my coding. I took it thru debug and when it hits
"Cancel=True", it does not error, it goes to "Exit Sub" here and then goes
to err routine in my forms commandbutton_click and there i have the same
coding that you see in "Err_Report_NoData"

I don't get it, but it works.

Tony

Private Sub Report_NoData(Cancel As Integer)
On Error GoTo Err_Report_NoData
MsgBox vbCrLf & " Sorry, there are no orders on file for customer:
" & Forms![CHF - WIP]![Combo0] & " ", vbInformation, "No Data"
Cancel = True
Exit_Report_NoData:
Exit Sub
Err_Report_NoData:
If Err.Number <> 2501 Then
MsgBox Err.DESCRIPTION
End If
On Error GoTo 0
Resume Exit_Report_NoData
End Sub

 

[Steve Schapel]

Tony,

I think it will work smoother if the error handling to exclude the 2501
is on the procedure you use to print the report, and not the No Data
event of the report itself... it's the cancelling of the OpenReport or
whatever that is throwing the 2501 error in the first place.

 

[Tony Girgenti]

So are you saying i should do it this way ?

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click
Dim stDocName As String
Dim strWhere As String
strWhere = "[COMPANY NAME] = '" & Forms![CHF - WIP]![Combo0] & "'"
stDocName = "CHF STATUS 2"
DoCmd.OpenReport stDocName, acViewPreview, , strWhere
Exit_Command2_Click:
Exit Sub
Err_Command2_Click:
If Err.Number <> 2501 Then
MsgBox Err.DESCRIPTION
End If
On Error GoTo 0
Resume Exit_Command2_Click
End Sub

Tony,

I think it will work smoother if the error handling to exclude the 2501
is on the procedure you use to print the report, and not the No Data
event of the report itself... it's the cancelling of the OpenReport or
whatever that is throwing the 2501 error in the first place.

--
Steve Schapel, Microsoft Access MVP

Hi Steve. I think i figured it out, but i'm not sure of why it does what it
does. Attached is my coding. I took it thru debug and when it hits
"Cancel=True", it does not error, it goes to "Exit Sub" here and then goes
to err routine in my forms commandbutton_click and there i have the same
coding that you see in "Err_Report_NoData"

I don't get it, but it works.

Tony

Private Sub Report_NoData(Cancel As Integer)

" & Forms![CHF - WIP]![Combo0] & " ", vbInformation, "No Data"
Cancel = True
Exit_Report_NoData:
Exit Sub
Err_Report_NoData:
If Err.Number <> 2501 Then
MsgBox Err.DESCRIPTION
End If
On Error GoTo 0
Resume Exit_Report_NoData
End Sub

 

[Steve Schapel]

Tony,

Yes, exactly. And take the If Err.Number <> 2501 line out of the
report's No Data procedure.