Print Current Form Record Problem ACC2007

[Me Ted]

I have a form with a command button that is designed to print the current
form record as a report. It works just fine, HOWEVER, it prints all records
for that particular Customer ID. Any clues as to what's wrong with the code
below?

Private Sub cmdPrint_Click()
'Print current record
'using rptJobs.
If IsNull(Me!CustID) Then
MsgBox "Please select a valid record", _
vbOKOnly, "Error"
Exit Sub
End If
DoCmd.OpenReport "rptJobs", , , _
"CustID = " & Me!CustID
End Sub

 

[Allen Browne]

To debug this:
- use a variable for the filter string, so you can see if it's right.
- Use Print Preview, so you can ask the report how it is filtered.
- If this is a bound form, make sure the record is saved.
- Make sure it's not at a new record.

This kind of thing:

Private Sub cmdPrint_Click()
Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If IsNull(Me.CustID) Then 'Check there is a record to print
MsgBox "Select a record with a customer."
Else
strWhere = "CustID = " & Me.CustID
Debug.Print strWhere
DoCmd.OpenReport "rptJobs", acViewPreview, , strWhere
Debug.Print Reports!rptJobs.Filter
Debug.Print Reports!rptJobs.FilterOn
End If
End Sub

When it runs, press Ctrl+G to open the Immediate window.
See if the Filter string is correct.
See if the report's filter is correct, and if the last line gave True (i.e.
the filter really is applied.)

If CustID is a Text field (not a Number field) in your table, you need extra
quotes:
strWhere = "CustID = """ & Me.CustID & """"

 

[Me Ted]

Works like a charm...Thanks a lot, Allen!

To debug this:
- use a variable for the filter string, so you can see if it's right.
- Use Print Preview, so you can ask the report how it is filtered.
- If this is a bound form, make sure the record is saved.
- Make sure it's not at a new record.

This kind of thing:

Private Sub cmdPrint_Click()
Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If IsNull(Me.CustID) Then 'Check there is a record to print
MsgBox "Select a record with a customer."
Else
strWhere = "CustID = " & Me.CustID
Debug.Print strWhere
DoCmd.OpenReport "rptJobs", acViewPreview, , strWhere
Debug.Print Reports!rptJobs.Filter
Debug.Print Reports!rptJobs.FilterOn
End If
End Sub

When it runs, press Ctrl+G to open the Immediate window.
See if the Filter string is correct.
See if the report's filter is correct, and if the last line gave True
(i.e. the filter really is applied.)

If CustID is a Text field (not a Number field) in your table, you need
extra quotes:
strWhere = "CustID = """ & Me.CustID & """"