VBA not passing parameters to query

[PRMiller2]

I have a query with two paremeters that is called in code. I have used DAO to pass paremeters from code to the query in the past successfully. For some reason, my code is not passing parameters in this case. Stepping through in debug mode, Access highlights "For Each prm In qdf.Parameters" and then jumps to the "Set rst..." line.

Here's the code:

Public Sub TestIt()
On Error GoTo Err_Handler
    
    Dim dbCurrent As DAO.Database
    Dim lngResult As Long
    Dim prm As DAO.Parameter
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset

    Set dbCurrent = CurrentDb
    Set qdf = dbCurrent.QueryDefs("qryQAAppliedPriceSchedule")
    
    For Each prm In qdf.Parameters
        prm.value = Eval(prm.Name)
    Next prm
    
    Set rst = qdf.OpenRecordset(dbOpenDynaset)

    With rst
        lngResult = rst.Fields("lonQAAppliedPriceSchedulesID_pk")
    End With
            
    
Exit_Handler:
    Set dbCurrent = Nothing
    Set rst = Nothing
    Set qdf = Nothing
    Exit Sub

Err_Handler:
    Call LogError(Err.Number, Err.Description, "frmProcessAudit.MailAudit()")
    Resume Exit_Handler
    
End Sub

And here's the query:

SELECT tblQAAppliedPriceSchedules.lonQAAppliedPriceSchedulesID_pk, tblQAClientsQALOBGroupTiers.lonQAClientID_fk, tblQAPriceScheduleDetail.lonQAPriceScheduleNamesID_fk
FROM (tblQAAppliedPriceSchedules 
LEFT JOIN tblQAClientsQALOBGroupTiers ON tblQAAppliedPriceSchedules.lonQAClientsQALOBGroupTiersID_fk = tblQAClientsQALOBGroupTiers.lonQAClientsQALOBGroupTiersID_pk) 
LEFT JOIN tblQAPriceScheduleDetail ON tblQAAppliedPriceSchedules.lonQAPriceScheduleDetailID_fk = tblQAPriceScheduleDetail.lonQAPriceScheduleDetailID_pk
WHERE (((tblQAClientsQALOBGroupTiers.lonQAClientID_fk)=GetQAClientID()) AND ((tblQAPriceScheduleDetail.lonQAPriceScheduleNamesID_fk)=GetQAPriceScheduleNameID()));

Here's the code for the GetQAClientID() and GetQAPriceScheduleNameID() functions:

Function GetQAClientID() As Long

    GetQAClientID = glngQAClientID

End Function
Function GetQAPriceScheduleNameID() As Long

    GetQAPriceScheduleNameID = glngQAPriceScheduleNameID

End Function

Not sure why Access is skipping the "prm.value" step. Does it not recognize the fact that I'm calling for parameters in the query?

 

[dhookom]

Assuming your field names are unique I would use:

    Dim dbCurrent As DAO.Database
    Dim lngResult As Long
    Dim rst As DAO.Recordset
    Dim stSQL as String
    strSQL =" SELECT lonQAAppliedPriceSchedulesID_pk, " & _
      " lonQAClientID_fk, lonQAPriceScheduleNamesID_fk " & _
      "FROM (tblQAAppliedPriceSchedules LEFT JOIN " & _
      "tblQAClientsQALOBGroupTiers ON " & _
      "tblQAAppliedPriceSchedules.lonQAClientsQALOBGroupTiersID_fk = " & _
      "tblQAClientsQALOBGroupTiers.lonQAClientsQALOBGroupTiersID_pk) " & _
      "LEFT JOIN tblQAPriceScheduleDetail ON  " & _
      "tblQAAppliedPriceSchedules.lonQAPriceScheduleDetailID_fk = " & _
      "tblQAPriceScheduleDetail.lonQAPriceScheduleDetailID_pk  " & _
      "WHERE lonQAClientID_fk= " & GetQAClientID() & " AND  " & _
      "lonQAPriceScheduleNamesID_fk= " & GetQAPriceScheduleNameID()
    Set dbCurrent = CurrentDb
    Set rst = dbCurrent.OpenRecordset(strSQL)

Duane
Hook'D on Access
MS Access MVP

 

[PRMiller2]

Thanks Duane. Sometimes I think it will be more efficient to reference a query rather than define the SQL inside the procedure, only to end up creating more work for myself and thus defeating the purpose. At some point I'll actually follow the most efficient route -- hopefully sooner rather than later!