Hello all,
I have a report that is used for calculating commissions. The record source takes two parameters: "Date of Comms" and "Sales Person". So, depending on what month and which salesperson, I can generate a report for that individual. The report seems to work just fine if I am opening it for just one person. This can be a bit tedious when I am trying to print out for each salesperson, so I took a crack at vba to automate printing each report. I think if I can get it to print then I can probably reuse the code with some tweaks to automatically send emails of the report to each salesperson as well.
I am, as always, a little stuck. The vba gets the parameters from two sources: "Date of Comms" is a user input field, "Sales Person" comes from a table "email" that has the salesperson's information (just two fields, name and email address). When I run my code I get the prompt to enter the date that I want to filter on, but then I get another pop up that asks me again for the date...I suspect that's because within the report, within the record source, I have it set to ask for the date. Then it asks for me the salesperson (again, it is setup to ask in the record source). Then it gives me a run-time error 3071 and debug points to my openreport line. Below is the code.
I read on Microsoft's website something about Report.InputParameters but I didn't fully understand it but it sounds like what I need to use?
Any help is appreciated. Thanks!
Dim db As Database
Dim rs As DAO.Recordset
Dim commDate2 As Date 'As Month Year
Dim paramPass As String
'User input for Date of Commission
commDate2 = InputBox("Enter Date of Commission", "Determine Commission Period", "Month Year")
Set db = CurrentDb
Set rs = db.OpenRecordset("email")
rs.MoveLast
rs.MoveFirst
Do Until rs.EOF 'Cycles through each salesperson in the table to print a report
'requires Date of Commission which is an user input
'requires Salesperson name which comes from the table "email" under field SP1
paramPass = "[DateofComm]= '" & commDate2 & "' And [SP1] = '" & rs![SP1] & "'"
'opens the report and passes the parameter for date and salesperson
DoCmd.OpenReport "Report1", acViewNormal, , paramPass
DoCmd.RunCommand acCmdPrint
DoCmd.Close
rs.MoveNext 'Get next record if not at the end of the table
Loop
rs.Close
Set rs = Nothing
Set db = Nothing