Microsoft Access - Openargs Problem - Asked By Jason on 04-Jan-12 11:41 AM

This is kind of a continuation of the last problem I had but it's slightly different.  Here is the code i have so far



Private Sub Report_Open(Cancel As Integer)
Me.Filter = "SAE=" & Me.OpenArgs
Me.FilterOn = True
End Sub


 Sub RunReport2()
 Dim dbs As DAO.Database
 Dim rst As DAO.Recordset
  
 Set dbs = CurrentDb
 Set rst = dbs.OpenRecordset("SELECT * FROM [SAE]", dbOpenDynaset)
  msgtitle = ""
 msgbody = ""
 MyPath = "G:\Jason\Blueline\"
 
 Do Until rst.EOF
 saename = rst.Fields(1).Value
 saeemail = rst.Fields(2).Value
 MyFilename = "Blueline Report - " & saename
   DoCmd.OpenReport "WIP", acViewPreview, , , , saename
   DoCmd.OutputTo acOutputReport, "WIP", acFormatPDF, MyPath & MyFilename, True
   DoCmd.SetWarnings False
   DoCmd.SendObject acSendReport, "WIP", acFormatPDF, saeemail, , , msgtitle, msgbody, False
   DoCmd.Close acReport, "WIP"
   DoCmd.SetWarnings True
   rst.MoveNext
 Loop
 End Sub

The yellow line is where I believe I am having a problem.  What I am doing is cycling through a list of names, opening a report based on that name/filtering for that name in the query, saving it to my hard disc and then emailing it out.  I need this to be automated with no popups as I will be distributing 200+ reports.  Currently as this code stands, I keep receiving a popup for the saename.  It keeps displaying the value, lets say jason, as a parameter and asks me to enter a value.  I don't know if this is by design but from what I understood, it should just pass the argument on to the on open event for the report and filter for that.  I'm not sure what I could be doing wrong. Any ideas?  Also I was curious if I am able to have a space or a comma in the argument.  I tried Jason Doe and Doe, Jason and got an error.  When I use just the first name I don't get an error but i get the parameter popup for the first name.
Pat Hartman replied to Jason on 04-Jan-12 09:53 PM
I replied to your other post with a more detailed solution.  For this problem, remove the line of code.  There is no reason to open the report in print preview.  The code I posted in the other thread tells you how to control the filter for the OutputTo method so you don't need to open the report in print preview.  The error is being caused because your syntax is incorrect.  The argument is a where clause without the "where" so it needs to be in the format - "FieldName = " & variablename

Since you didn't take my advice and use actual column names rather than field(x) reference, I still don't know what the key field name is and I can't guess its data type so I'll just use RecID again.


DoCmd.OpenReport "WIP", acViewPreview, , , , "RecID = " & saename

You also didn't dim your variables.  VBA is way too flexible in this regard.  It is poor practice to not declare variables.
Sri K replied to Jason on 05-Jan-12 12:21 AM
You could export the necessary objects to a blank db, then zip and attach that here. You just need whatever objects will recreate the problem.

The typical way I've heard of handling multiple values in OpenArgs is putting some sort of delimiter in between values (like ";"), then parsing it back out at the other end.
Jason replied to Pat Hartman on 05-Jan-12 12:17 PM
Looks to be a success.  Thank you for your help