Results 1 to 3 of 3
  1. #1
    jcright is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Feb 2022
    Posts
    21

    vba passing parameter to report record source

    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

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'd say you're correct as to why you get prompted 2x. Try removing the reference for the 2nd prompt because you're using the Where condition to filter the report anyway. That assumes that both the Where parameter and the what's causing the 2nd prompt apply to the same thing. I'm not seeing how you filter the recordset to the people/dates that you want though.

    Please post code within code tags (# on forum posting toolbar) with proper indentation to facilitate reading and maintain indentation.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    jcright is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Feb 2022
    Posts
    21
    I was trying to avoid removing the parameters from the report's record source because sometimes I only want to view one salesperson and so I would open the report as opposed to having vba open the report. I had hoped to find a way for vba to hijack the user input prompts generated by the parameters. That didn't happen. So, I've removed the prompts from the report's record source. I then created a form to handle all vs. specific requests. So now I can click a button(s) to print/email all or specific reports. In the long run it achieves the same result.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 04-16-2021, 10:59 AM
  2. Passing a query parameter to a report
    By clancy76 in forum Access
    Replies: 3
    Last Post: 07-02-2020, 03:25 PM
  3. Replies: 3
    Last Post: 11-20-2019, 01:52 PM
  4. Passing Parameter to Report
    By greatwhite in forum Programming
    Replies: 3
    Last Post: 08-06-2019, 02:47 PM
  5. Replies: 1
    Last Post: 01-22-2012, 02:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums