Filtering Report from form

[gleave]

Hi. im trying to setup a filter for a report. Let me explain what I would like to achieve. I have a form called Invoicing, here all the data is put in about the invoice, I then have a report also called invoicing. I would like to be on the invoicing form and click a button saying view invoice, this then takes the invoice number from the form and filters out all the other invoices so only that 1 invoice is shown.

Details of the fields, forms and reports:
Report Details:
Report name: Invoicing
The invoice number field is called: Invoice Number

Form details:
Form name: Invoicing
The invoice number field is called: Invoice Number

I have tried putting the following code into the reports on open event procedure:

Private Sub Report_Open(Cancel As Integer)
Me.Filter = "Invoice Number = " & Form_Invoicing.[Invoice Number].Value
Me.FilterOn = True
End Sub

Can some one please help me.

[CaptainD]

I assume you're using MS Access, I would (through the click event) pass the Invoice Number to a report query and create the report off of that, or, if you want to use a Filter, set up the report based off the same query.
(I placed mine in a Sub and call it from different forms and filter by selected choices)
'Calling print report sub

Call PrintReports("Form_01", sFilterBy, iFilterBy)
 

Sub:

Private Sub PrintReports(sReportName As String, sFilterBy As String, iFilterField As Integer)
Dim sFieldName As String
On Error GoTo ErrHandler
 
Select Case iFilterField
    Case 1
        sFieldName = "Submitted = '"
    Case 2
        sFieldName = "ProgramSupervisor = '"
    Case 3
        sFieldName = "ProgramManager = '"
End Select
 
     If sFilterBy = "All Request" Or sFilterBy = "" Then
        DoCmd.Maximize
        DoCmd.OpenReport sReportName, acPreview
        DoCmd.RunCommand acCmdZoom75
        'DoCmd.Maximize
    Else
         'DoCmd.OpenReport stDocName, acViewPreview, , sFilterBy
        DoCmd.OpenReport sReportName, acViewPreview, , sFieldName & sFilterBy & "'"
        DoCmd.RunCommand acCmdZoom75
    End If
 
 
Exit Sub
ErrHandler:
    MsgBox "Error printing reports, Error # " & Err.Number & ", " & Err.Description, vbOKOnly, "Error"
End Sub
 

Hi. im trying to setup a filter for a report. Let me explain what I would like to achieve. I have a form called Invoicing, here all the data is put in about the invoice, I then have a report also called invoicing. I would like to be on the invoicing form and click a button saying view invoice, this then takes the invoice number from the form and filters out all the other invoices so only that 1 invoice is shown.

Details of the fields, forms and reports:
Report Details:
Report name: Invoicing
The invoice number field is called: Invoice Number

Form details:
Form name: Invoicing
The invoice number field is called: Invoice Number

I have tried putting the following code into the reports on open event procedure:

Private Sub Report_Open(Cancel As Integer)
Me.Filter = "Invoice Number = " & Form_Invoicing.[Invoice Number].Value
Me.FilterOn = True
End Sub

Can some one please help me.

[gleave]

Hi, yes I am using MS Access, I actually managed to figure out the code before I saw the post from CaptainD (Thanks anyhow). This is the code I ended up using:

Private Sub Command51_Click()
On Error GoTo Err_Command51_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Invoices"

stLinkCriteria = "[Invoice Number]=" & Me![Invoice Number]
DoCmd.OpenReport stDocName, A_PREVIEW, , stLinkCriteria

Exit_Command51_Click:
Exit Sub

Err_Command51_Click:
MsgBox Err.Description
Resume Exit_Command51_Click

End Sub