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

  • Thread starter Thread starter Tony Girgenti
  • Start date Start date
T

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
 
Try:

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

HTH
Van T. Dinh
MVP (Access)
 
Tony Girgenti said:
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
Click to expand...

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.
 
Tony said:
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
Click to expand...


In addition to the quoting, you need another comma:

DoCmd.OpenReport stDocName, acPreview, , strWhere
 
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

Marshall Barton said:
Tony said:
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
Click to expand...


In addition to the quoting, you need another comma:

DoCmd.OpenReport stDocName, acPreview, , strWhere
Click to expand...
 
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
 
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
 
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.
 
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
 
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.
 
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

Steve Schapel said:
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


Tony said:
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
Click to expand...
Click to expand...
Click to expand...
Click to expand...
 
Tony,

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