How to run parameterized query from VBA. Parameters sourced from recordset

Question

I have a form where a user selects a vendor's name from a combobox, whose catalog file is to be imported. The combobox selection then drives a query to create a one-record recordset (rsProfile) containing several profile variables queried from a table of all vendor profiles. These variables are then used in a series of different queries to reformat, translate and normalize the vendor's uniquely structured files to a standardized format that can be imported into our system.

I am frustrated that I can't figure out how to build my stored queries that will use one or more parameters that are automatically populated from the profile recordset.

Here is my rsProfile harvesting code. It works. Note that intVdrProfileID is a global variable set and used in other places.

Private Sub btn_Process_Click()

Dim ws As Workspace
Dim db, dbBkp As DAO.Database
Dim qdf As DAO.QueryDef
Dim rsProfile, rsSubscrip As Recordset
Dim strSQL As String
Dim strBkpDBName As String
Dim strBkpDBFullName As String

strBkpDBName = Left(strVdrImportFileName, InStr(strVdrImportFileName, ".") - 1) & "BkpDB.mdb"
strBkpDBFullName = strBkpFilePath & "\" & strBkpDBName

Set db = CurrentDb
Set ws = DBEngine.Workspaces(0)

MsgBox ("Vendor Profile ID = " & intVdrProfileID & vbCrLf & vbCrLf & "Backup file path: " & strBkpFilePath)

' Harvest Vendor Profile fields used in this sub
strSQL = "SELECT VendorID, Div, VPNPrefix, ImportTemplate, " & _
                 "VenSrcID, VenClaID, ProTyp, ProSeq, ProOrdPkg, ProOrdPkgTyp, JdeSRP4Code, " & _
                 "PriceMeth, " & _
                 "ProCost1Frml, ProCost2Frml, " & _
                 "ProAmt1Frml, ProAmt2Frml, ProAmt3Frml, ProAmt4Frml, ProAmt5Frml " & _
         "FROM tZ100_VendorProfiles " & _
         "WHERE VendorID = " & intVdrProfileID & ";"

Set qdf = db.QueryDefs("qZ140_GetProfileProcessParms")
qdf.SQL = strSQL
Set rsProfile = qdf.OpenRecordset(dbOpenSnapshot)
DoCmd.OpenQuery "qZ140_GetProfileProcessParms"
' MsgBox (qdf.SQL)

I have used QueryDefs to rewrite stored queries at runtime, and although it works, it is quite cumbersome and does not work for everything.

I was hoping for something like the sample below as a stored query using DLookups. I can get this to work in VBA, but I can't get anything to work with stored queries. I am open to other suggestions.

Stored Query "qP0060c_DirectImportTape":

SELECT 
    DLookUp("[VPNPrefix]","rsProfile","[VendorID]=" & intVdrProfileID) & [PartNo] AS VenPrtId,
    Description AS Des,
    DLookup("[Jobber]","rsProfile",[VendorID=" & intVdrProfileID) AS Amt1,
INTO tP006_DirectImportTape
FROM tJ000_VendorFileIn;

ADDENDUM: Let me adjust the problem to make it a bit more complex. I have a collection of about 40 queries each of which use a different collection of parameters (or none). I also have a table containing the particular set of queries that each vendor 'subscribes' to. The goal is to have a database where a non-coding user can add new vendor profiles and create/modify the particular set of queries which would be run against that vendor file. I have almost 100 vendors so far, so coding every vendor seperately is not practical. Each vendor file will be subjected to an average of 14 different update queries.

Simplified Example: Vendor1 file needs to be processed with queries 1, 2 and 5. Vendor2 file might need only update queries 2 and 4. The parameters for these queries might be as follows:

query1 (parm1) query2 (parm1, parm4, parm8, parm11) query4 (parm5, parm6, parm7, parm8, parm9, parm10, parm11) query5 () -no parms required

This is the core query processing that loops through only the queries relevant to the current vendor file. rsSubscrip is the recordset (queried from a master table) containing this filtered list of queries.

' Run all subscribed queries
MsgBox "Ready to process query subscription list."
With rsSubscrip
    Do While Not .EOF
        db.Execute !QueryName, dbFailOnError
        .MoveNext
    Loop
    .Close
End With

Your conclusion is correct. Your question is also quite the right question. I am looking to code DLookup into the stored queries for each field requiring a parameter, thereby removing the necessity of having parameters. Upon further reflection, If I had only one parameter identifying the unique vendorID, perhaps the DLookups would work, regardless of the actual number of profile variables needed in the query? — Thomas Kerber
– Thomas Kerber, Commented Jun 26, 2015 at 15:48
You've grasped the idea perfectly. I think your right in suggesting that the rsProfile recordset is redundant and unnecessary. I was unsuccessfully attempting to use it as the lookup source, but Access didn't like it. If I reference an actual table (tblVendorProfile) for the lookup source... it might not balk so much. — Thomas Kerber
– Thomas Kerber, Commented Jun 26, 2015 at 16:16

jhTuppeny · Accepted Answer · 2015-06-26 08:07:24Z

1

You can set the parameters of a predefined query using the syntax;

Set qdf = CurrentDB.QueryDefs(QueryName)
qdf.Parameters(ParameterName) = MyValue

To add parameters to the query, add the following before the SELECT statement in the sql

PARAMETERS [ParameterOne] DataType, [ParameterTwo] DataType;
SELECT * FROM tblTest;

answered Jun 26, 2015 at 8:07

jhTuppeny

9901 gold badge12 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Thomas Kerber Over a year ago

I have added some other functional criteria to the post.

Thomas Kerber Over a year ago

I'll have to think about how to use this when I don't know which query is being called and the identity and number of parameters being used is dependent on the particular query.

Thomas Kerber Over a year ago

The code stops on the line: "qdf.Parameters(Parm1) = intVdrProfileID". I get "Item not found in this collection". I used the same ParameterName as in my query. I tried "Dim Parm1 as Integer", and "as Parameter" both result in the same error. Also tried without "Dim" statement and got "variable not defined". Not sure what do do to fix. Help?

Thomas Kerber Over a year ago

Ok, fixed that problem, but now need to solve this one: "Err: too few parameters. Expected (1)". Here is the code. Halts on "execute" statement. Dim Parm1 As Long Set qdf = db.QueryDefs(!QueryName) qdf.Parameters(Parm1) = intVdrProfileID MsgBox (!QueryName) & "(" & qdf.Parameters(Parm1) & ")" db.Execute !QueryName, dbFailOnError

Christine · Accepted Answer · 2025-07-03 04:53:40Z

0

In reference to: The code stops on the line: "qdf.Parameters(Parm1) = intVdrProfileID". I get "Item not found in this collection"....

Parm1 needs to be a string variable which is the name of the parameter. Dim Parm1 as string and set it to the name of the parameter.

answered Jul 3 at 4:53

Christine

1

1 Comment

M-- Jul 3 at 4:59

Please edit and format your answer properly.

Collectives™ on Stack Overflow

How to run parameterized query from VBA. Parameters sourced from recordset

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related