Search code examples
vbams-access

MS.Access - VBA Print Report From Form with 2 filter criteria in the DoCmd.OpenReport [where condition]

I'm trying to print a batch ID data sheet from a form without opening the report. Using the below code I can open the report showing just the data I want

Private Sub PrntbLblV1_Click()   
Dim str As String
str = "[BatchID] = " & Me.BatchID
DoCmd.OpenReport "RprtLblPrint", acViewPreview, , str
DoEvents
End Sub

This opens the record I want in print preview... however I want to just send the record to printer.. I've tried

 DoCmd.OpenReport "RprtLblPrint", acNormal, , str
    DoCmd.RunCommand acCmdPrint
   DoEvents

But it prints every batch transaction not just the current one... I think the problem is I have a further filter in the report property that filters on load to remove completed transactions [Complete] = False which is a true/false tick box.. I can't seem to nest the completed & batch criteria into the where part of the DoCmd.OpenReport It keeps throwing a type mismatch error

str = "[BatchID] = " & Me.BatchID And [Complete] = False

and I've tried separating it

Dim str As String
Dim Cmplt As String
str = "[BatchID] = " & Me.BatchID
Cmplt = [Complete] = False
    DoCmd.OpenReport "RprtLblPrint", acNormal, , str And Cmplt
    DoCmd.RunCommand acCmdPrint

Any suggestion on how I can get the 2 filter criteria into the DoCmd.OpenReport where condition??

Solution

  • It needs to be part of the filter string as in:

    str = "[BatchID] = " & Me.BatchID & " And [Complete] = False"