none
How Do I run a parameter Query Using Access VBA and then assign the result set to a variable? RRS feed

  • Question

  • I want to run a parameter query using Access 2016 VBA and then assign the result of a certain column to a variable. Below, between the = signs is my code and after the code are my questions about an error I'm getting:

    =============================

    Dim MyDb As DAO.Database Dim Myqdf As DAO.QueryDef Dim rst As DAO.Recordset Dim MyQueryName As String Dim strEquipName As String MyQueryName = "GetQtyByEquipName" strEquipName = Me.cboEquipName.Value Set MyDb = CurrentDb() Set Myqdf = MyDb.QueryDefs(MyQueryName) Myqdf.Parameters("Equipment Name") = strEquipName Set rst = Myqdf.OpenRecordset() If rst.EOF = False Then MsgBox rst!Quantity End If

    rst.Close

    Myqdf.Close MyDb.Close

    ===============================

    I'm getting the following error on this line of code - Myqdf.Parameters("Equipment Name") = strEquipName

    Runtime error 3265
    Item not found in collection

    I am not sure what 'item not found in collection' is referring to

    Here is my query, SQL view:

    SELECT Equipment.EquipName, Equipment.Quantity 
    FROM Equipment 
    WHERE (((Equipment.EquipName)=[Equipment Name:])); 

    Why am I getting this error? and am I accessing the value that is returned by the query correctly?

    Thanks
    Keith


    Keith Aul

    Saturday, September 24, 2016 10:59 AM

Answers

  • 1) You have to spell the parameter exactly the same way as in the WHERE clause:

    Myqdf.Parameters("[Equipment Name:]") = strEquipName

    2) You have to declare the parameter explicitly in the query.

    One way to do this is to open the query in Design View, and click Parameters in the Show/Hide group of the Design tab of the ribbon. Enter [Equipment Name:] (exactly as spelled in the Criteria row) in the Parameter column, and select Text (or Short Text) in the Data Type column. Then click OK and save the query.

    Another way is to open the query in SQL view. Insert the following line at the top, before the SELECT ... part:

    PARAMETERS [Equipment Name:] Text ( 255 );

    Again, the parameter must be written exactly as it is in the WHERE clause. So it'd become

    PARAMETERS [Equipment Name:] Text(255);
    SELECT Equipment.EquipName, Equipment.Quantity
    FROM Equipment
    WHERE (((Equipment.EquipName)=[Equipment Name:]))


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by Stefan Hoffmann Saturday, September 24, 2016 11:36 AM
    • Marked as answer by KeithAul Sunday, September 25, 2016 11:48 PM
    Saturday, September 24, 2016 11:15 AM

All replies

  • 1) You have to spell the parameter exactly the same way as in the WHERE clause:

    Myqdf.Parameters("[Equipment Name:]") = strEquipName

    2) You have to declare the parameter explicitly in the query.

    One way to do this is to open the query in Design View, and click Parameters in the Show/Hide group of the Design tab of the ribbon. Enter [Equipment Name:] (exactly as spelled in the Criteria row) in the Parameter column, and select Text (or Short Text) in the Data Type column. Then click OK and save the query.

    Another way is to open the query in SQL view. Insert the following line at the top, before the SELECT ... part:

    PARAMETERS [Equipment Name:] Text ( 255 );

    Again, the parameter must be written exactly as it is in the WHERE clause. So it'd become

    PARAMETERS [Equipment Name:] Text(255);
    SELECT Equipment.EquipName, Equipment.Quantity
    FROM Equipment
    WHERE (((Equipment.EquipName)=[Equipment Name:]))


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by Stefan Hoffmann Saturday, September 24, 2016 11:36 AM
    • Marked as answer by KeithAul Sunday, September 25, 2016 11:48 PM
    Saturday, September 24, 2016 11:15 AM
  • That seems a laborious way of achieving the objective.  Why not simply call the DLookup function, e.g.

    Dim strCriteria As String
    Dim varQuantity As Variant

    strCriteria = "EquipmentName = """ & Me.Me.cboEquipName & """"
    varQuantity = DLookup("Quantity","Equipment", strCriteria)

    If Not IsNull(varQuantity) Then
        MsgBox "Quantity for " & Me.cboEquipName & " = " varQuantity, vbInformation, "Item Found"
    Else
        MsgBox "No matching record.", vbExclamation, "Item Not Found"
    End If

    If you need to return multiple values from rows in a table then establishing a recordset object is likely to be more efficient as you can then loop through it, but to return a single value calling the DLookup function, while domain functions are not the fastest kids on the block, has been shown by benchmarking generally to be a quicker solution.

     

    Ken Sheridan, Stafford, England

    Saturday, September 24, 2016 1:17 PM
  • Thank you for posting this solution. This worked fine. I did not realize that the square brackets [] around the parameter name was needed in the VBA. I just thought the [] brackets were needed in design view. 

    Plus, since I'm new to this, I did not realize about the second step you told me about(You have to declare the parameter explicitly in the query.)

    Keith


    Keith Aul

    Sunday, September 25, 2016 11:53 PM
  • Ken,

    Thanks for alerting me to this function, DLookup. I'm fairly new to all this and that is why I'm posting a lot of questions. 

    I'll try this out and I'll post back later with the results.

    Keith


    Keith Aul

    Monday, September 26, 2016 12:02 AM