Form/Subform print to report

[Trilback]

I have a DB with a form and subform. My form is for adding the clients and my sub form is the Work Invoices for the clients. My two table are linked. What I want to be able to do is selct a client and WorkID (Work Invoice) and print a report (Invoice) with the clients info that I selected and the WorkID that I have selcected. I have the report already made and have added the Print invoice button on my MainForm and not the subform. But the code I found online is not work to print my invoice. Code below

Dim strWhere As String

strWhere = "[WorkID] = " & Forms!tblWorkSubForum!WorkID
DoCmd.OpenReport "rptWorkOrderInvoice", acViewPreview, strWhere


What am I doing wrong? I have tried a few codes I found online.

 

[David R]

Stupid question, but does your report have WorkID as a control in it? It's not enough that it be in the recordsource, it needs to be on the report... though you can set it to .Visible = False if you want.

 

[Trilback]

Stupid question, but does your report have WorkID as a control in it? It's not enough that it be in the recordsource, it needs to be on the report... though you can set it to .Visible = False if you want.

Duh me for forgetting that important part. Now I just got to get the code right.

 

[Trilback]

Have you tried this?

Dim strWhere As String
strWhere = "[WorkID] = " & Me.[WorkID]
DoCmd.OpenReport "rptWorkOrderInvoice", acViewPreview,, strWhere

I have but for some reason it does not like me. Attached is the error message and my database. FYI is a 2007 - 2013 database.

 

[Trilback]

the linking fields are clientID not workID in forms and reports. You have to change it behind the print button and in the reports record source.

That what confused me. I am trying to print out a spefic workID (Work Invoice) for the Client. Why would need to change it to clientID? Trying to figure out the logic behind it.

 

[Trilback]

Here is what you need behind your button.

Private Sub print_invoice_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 = "[clientID] = " & Me.[ClientID]
        DoCmd.OpenReport "rptWorkOrderInvoice", acViewNormal, , strWhere 'Prints report
 
    End If
 
    End Sub

Tried the above code but got this message.

 

[David R]

Is ClientID a field in your report? You can make it Hidden if necessary.

 

[Trilback]

Is ClientID a field in your report? You can make it Hidden if necessary.

It works now but not what I wanted. As you see in my database if I pick Nick Codd I want to be able to select a spefic WorkID or more than one and print it to my invoice report as one or more entries in a single report print for that client if that makes sense.

 

[David R]

Does

strWhere = "[WorkID] = " & Me.[WorkID]

not work?

 

[Trilback]

Does

strWhere = "[WorkID] = " & Me.[WorkID]

not work?

When I get to work I will try that again but I think I had tried that but was not printing right. I do need to fix the template of my report

 

[Trilback]

Trilback,

Try clicking on the workID in my sample.

That a good idea and will work for now. I still want to figure out how to make it work like I want it to.