Using OpenArgs in Access 2010 DoCmd.OpenReport

[pt60]

I have not been able to pass my OpenArg in my DoCmd.OpenReport VB script correctly. What I have opens all my records, and sends a complete report to each file record, when I want individual pdf's for each group of Service_Dealer_Loc that I have.

Here is what I am using:

Sub pst()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim str As String
str = "Select Service_Dealer_Loc From ptable"
Set db = CurrentDb
Set rs = db.OpenRecordset(str, dbOpenDynaset)
Do Until rs.EOF
stArg = "Service_Dealer_Loc = " & rs!Service_Dealer_Loc

DoCmd.OpenReport "preport", acViewPreview, , , , stArg
DoCmd.OutputTo acOutputReport, "preport", "*.pdf", "C:\Users\Documents\" & rs!Service_Dealer_Loc & ".pdf"
DoCmd.Close acReport, "preport"
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub



And here is the ON OPEN property of the report I am using:

Private Sub Report_Open(Cancel As Integer)
Dim strArg As String
strArg = Me.OpenArgs
Me.FilterOn = True
'MsgBox strArg
End Sub


Any suggestions are welcome

 

[pbaldy]

You never set the filter in the Open event.

 

[pt60]

Just getting back to this, I have added a Me.Filter statement in the ON Open property of the report, but have the same result.

strArg = Me.OpenArgs
Me.Filter = "Service_Dealer_Loc =" & Me.OpenArgs
Me.FilterOn = True

I have the Record Source Property of the report set for the query that gathers all the data.

I have also hardcoded one of the Service_Dealer_Loc records (254-770-77000) into the filter and still the report preview open with all the records.


strArg = Me.OpenArgs
Me.Filter = "Service_Dealer_Loc = 254-770-77000"
Me.FilterOn = True

Paul

 

[pbaldy]

Well, your first effort will fail because OpenArgs already contains the first part, unless you've changed your code. Based on the value, it is text and would require delimiters. Try

Me.Filter = "Service_Dealer_Loc = '254-770-77000'"
Me.FilterOn = True

 

[pt60]

OK, placing the hard coded "254-770-77000" in the Me.Filter statement worked, and when it looped through all the different Service_Dealer_Loc records, it made each one have a pdf of "254-770-77000"

So when I changed the MeFilter statement to:

Me.Filter = "Service_Dealer_Loc = 'Me.OpenArgs'"

It opened up a blank report, and saved that blank report for each of the Service_Dealer_Loc records.

I even added a MsgBox with Me.OpenArgs, and each MsgBox that opened up in the loop had the correct Service_Dealer_Loc number as it went through the loop.

Private Sub Report_Open(Cancel As Integer)

Dim strArg As String
strArg = Me.OpenArgs
Me.Filter = "Service_Dealer_Loc = 'Me.OpenArgs'"
Me.FilterOn = True

MsgBox Me.OpenArgs
End Sub


There must be something simple, I have not done correctly to make this not work.

Paul

 

[pbaldy]

No, the correction is made in the other code:

stArg = "Service_Dealer_Loc = '" & rs!Service_Dealer_Loc & "'"

 

[pt60]

OK, that was it.....everything looks good now. I am going to transfer the codeing into a form, and add options to send out emails as well, but I wanted to get this part working first.

Thanks!

Paul

 

[pbaldy]

No problemo!