Results 1 to 12 of 12
  1. #1
    shinydiamond is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2016
    Posts
    31

    Automatically pass list of parameter values from a table to an append query

    Hi,


    I've been searching for the best way to automatically pass a list of parameter values to an append-table query I have. I have a separate table that has about 30 sales rep names in it and I want to be able to run my sales performance query for each rep without having to type their names one by one into the query prompt. The SQL for this sales performance append query is about a mile long and I'm guessing my only solution would be to embed the SQL into VBA and somehow pass the query parameter values through a loop but I can't seem to find any information on doing this.

    Any help would be appreciated.

    Thanks

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    When I do this sort of thing there a couple of things I do, depending on the situation.

    1. Create a form that has the 'print' button
    Put a hidden text box control on the form
    Write some VBA to cycle through the primary key field of your sales reps and populate the text box with that primary key
    Run the report process
    move to the next sales rep.

    2. Create one long report with page breaks instead of having the report limited to 1 rep at a time. Insert the page breaks after each sales rep footer

    other folks may suggest filters, but you'd still need a way to apply the filter to the report as you open it which leads you back to option 1, creating a dynamic SQL is also an option but really unnecessary.

  3. #3
    shinydiamond is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2016
    Posts
    31
    Thanks for the reply, rpeare.
    If I'm understanding your approach, I would have to push the print button 30 times, once for each rep? I was hoping for something requiring no user intervention. However, it seems that if I could write some vba code to cycle through the primary key field of my sales rep table, I could use the same code to just pass the parameter directly to the SQL query?
    I'm not creating a report - just dumping it all into a table that has a column which contains the sales rep name but I understand your #2.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    with either approach it's a single button click.

    If you want to pass a parameter to your SQL query you do it in a couple of ways

    1. reference a field on a form (i.e. [forms]![formname]![fieldname])
    2. dynamically create your SQL at time you run the report

    In either case it comes back to being able to cycle through your list of sales reps, I prefer the former because it's just easier.

  5. #5
    hapm is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    197
    Additional to rpeare's approaches here are some more:

    3. Do it all in one insert query by joining the "sales rep names"-table (if this works highly dependent on how your current insert query looks like, but if your lucky its the easiest and fastest [in sence of performance] solution)
    4. Use the QueryDef object of your current query in vba to apply the wanted parameters (wouldn't requery a form field only to specify the parameters)
    5. Use a macro to apply the wanted parameters (applying query parameter values in macros is available since access 2010, but I never tried looping over a recordset in a macro)

  6. #6
    shinydiamond is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2016
    Posts
    31
    Thanks, hapm,
    I actually got rpeare's method working last night but I am still hoping to find a more elegant approach that doesn't require having a dummy form (must be open) to pass the variables in and out of. I've got a looping DAO recordset search through my sales rep table working in VBA but wasn't aware that I could then pass a query parameter straight from the code instead of pulling from a form. Do you know the VBA syntax for passing a query parameter into my SQL code which is embedded in my VBA?

    For reference, my code looks like this:

    Option Explicit
    Public Sub RUN_Query()
    Dim SQL_Text As String
    Dim rst As DAO.Recordset
    Dim salesrepname As String
    Dim active As Variant

    Set rst = CurrentDb.OpenRecordset("tbl salesrep")
    Do Until rst.EOF
    salesrepname = rst(1)
    active = rst(3)

    If active = "Active" Then

    [Forms]![frmSalesreps]![Text0] = salesrepname
    SQL_Text = "bunch of complicated SQL goes here with [Forms]![frmSalesreps]![Text0] inserted where my query parameter is needed"

    DoCmd.RunSQL (SQL_Text)

    End If

    rst.MoveNext

    Loop
    rst.Close

    End Sub

    -I didn't include the actual SQL because it's way too long.

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    if you're dynamically building your sql statement you don't need to have a form involved, that would only be if you have a static query with a parameter value it's expecting.

    Code:
    if active = "active" then
      ssql = "STUFF '" & salesrepname & "' Rest of Stuff"
      currentdb.execute (ssql)
    endif

  8. #8
    shinydiamond is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2016
    Posts
    31
    I had tried that approach with the concatenation in the middle of my sql statement but I was still prompted to enter the variables. Although I was using a DoCmd to run the SQL. I'm trying to use the currentdb.execute command you suggest but am getting a "Run-time error 3061: Too few parameters. Expected 3"

  9. #9
    shinydiamond is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2016
    Posts
    31
    Hmmm, I think I may still have some form references embedded in the SQL, which might be causing that error I just posted. I will attempt to hunt them down and replace with variables.

  10. #10
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    one of the key differences between a database designer/developer and a general programmer is that we work in terms of record sets. Rather than loop the sales rep name as a parameter the database approach would be to join the performance query to the sales rep table.

    it does depend on the structure of the data fundamentally but your overall requirement appears to be a common database role, and so typically this should not be an issue

  11. #11
    shinydiamond is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2016
    Posts
    31
    Thanks for the feedback. I confess, I am a former programmer masquerading as a database designer, but this SQL statement already has a bunch of joins. Bottom line is that I need this query to run over and over again with different parameters, each time appending the results to an existing table so I thought code was the best way to go. I've removed all the form references in the SQL but have had no luck getting rid of the Run-time error 3061: Too few parameters. Expected 1.

  12. #12
    shinydiamond is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2016
    Posts
    31

    Thumbs up

    found the source of the error. Apparently you have to enclose the parameter you're passing to the SQL in single quotes. Problem solved. Using the Currentdb.execute instead of DoCmd.RunSQL is what made all of the difference in automating this smoothly. Thanks all for your suggestions!

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

Similar Threads

  1. Replies: 5
    Last Post: 04-27-2015, 02:40 PM
  2. Replies: 7
    Last Post: 03-11-2015, 12:48 PM
  3. Replies: 13
    Last Post: 05-14-2013, 06:01 PM
  4. Replies: 3
    Last Post: 11-26-2010, 12:38 PM
  5. Pass list as parameter to in operator
    By bliever in forum Queries
    Replies: 5
    Last Post: 11-11-2009, 03:15 AM

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