Error in report code - blank report

[gconeuk]

Hi,

I'm trying to run a report based on the current record. Having looked at Allen Browne's website (wow that guy knows his stuff!), I've created the following:

 Private Sub cmdCreateFileCheck_Click()
     Dim strWhere As String
     If Me.Dirty Then    'Save any edits.
        Me.Dirty = False
    End If
     If Me.NewRecord Then 'Check there is a record to print
        MsgBox "Select a record to print"
    Else
        strWhere = "[Client Ref]" = " & Me.ClientRef"
        DoCmd.OpenReport "rptCombinedProductsFileCheck", acViewPreview, , strWhere = "ClientRef = """ & Me.ClientRef & """"
    End If
 End Sub [\CODE]
  
 I want to use the current record contained in the field ClientRef to produce the report (using a cmdbutton) but the report is blank when the cmdbutton is clicked.
  
 Can anyone see what I'm doing wrong here?

 

[JHB]

You are mixing something up

DoCmd.OpenReport "rptCombinedProductsFileCheck", acViewPreview, , strWhere = "ClientRef = """ & Me.ClientRef & """"

Try the below instead:

DoCmd.OpenReport "rptCombinedProductsFileCheck", acViewPreview, , strWhere

 

[gconeuk]

Many thanks for replying. I tried as you suggested, but the report is still blank?

The report is based on a query but I was looking to not have to put a parameter in to run the report. Would this have something to do with the code not running?

Also, the cmdbutton clicked to create the report is on a subform, does this also make a difference?

 

[JHB]

What is the value of strWhere after you have apply a value, use a MsgBox.
Check if the table/query have a [Client Ref] equal to what the MsgBox shows.

 

[Minty]

Me.ClientRef will need to be on the sub form if that's where the command is called from.
Or you need to refer to it on the main form as Me.Parent!ClientRef assuming it's not a continuous form.

 

[gconeuk]

Hi there,

Yes, ClientRef is on the subform as well as the mainform.

Main form = frmClients
Sub form = frmCombinedProducts

Both of these have ClientRef as it's a one to many relationship.


Private Sub cmdCreateFileCheck_Click()
Dim strWhere As String
If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If
If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[ClientRef]" = " & Me.frmClient!ClientRef"
DoCmd.OpenReport "rptCombinedProductsFileCheck", acViewPreview, , strWhere
End If
End Sub

I still can't get this working, the report does not show the record details.

Sorry, I'm a real squib.

Thanks!

 

[JHB]

Did you read post #4?
Still not get it, post your database with some sample data, zip it.

 

[Minty]

You still have this bit wrong;

strWhere = "[ClientRef]" =[COLOR="Red"] "[/COLOR] & Me.frmClient!ClientRef[COLOR="red"]"[/COLOR]

Assuming clientRef is a number should be

strWhere = "[ClientRef] = " & Me.frmClient!ClientRef

Or if it's text as stated above

strWhere = "[ClientRef] = '" & Me.frmClient!ClientRef & "'"

Also as stated above if you add the following code before you open your report

MsgBox "strWhere = " & strWhere

You'll see Exactly what is happening.