Creating a Pass-Through Query Using Values From UI Controls

[RunTime91]

Greetings...

Today I am trying to create a pass-through query using variables which derive their values from controls on my userform.

I am able to get the below to work which derives its values from a local SQL table

INSERT INTO tri.TktRqsts (ReqID, EmpID, EmpName, RsnForRqst, RqstdBy, RqstDate)
SELECT tri.TmpTktRqsts.ReqID, tri.TmpTktRqsts.EmpID, tri.TmpTktRqsts.EmpName,
            tri.TmpTktRqsts.RsnForRqst, tri.TmpTktRqsts.RqstdBy, tri.TmpTktRqsts.RqstDate
FROM tri.TmpTktRqsts;

But when I try the same idea using variables what I get is the literal string instead of the string value


fwiw - what I am doing is running the code in Access to set the variable values then running the pass-through query outside of the Access VBA Code.

This is the PThrough query that sends the literal string values - (which makes me think I'm close...but)

INSERT INTO tri.TktEmailTmp (EmpID, EmpName, TempAgency)
VALUES('" & EmpId & "', '" & EmpName & "', '" & TAgncy & "');

As always - Thank you very much...

[RunTime91]

I think I have solved this one - But would still like some advice & guidance if anyone see's anything wrong with my solution.

And I do have a question which I will post at the end...

Apparently, I need to create a QDef to accomplish this.

Below is the QDef and the Function it runs

EmpId = Me!CmboAgentList.Column(1)
EmpName = Me!CmboAgentList.Column(0)
        
If Me.CmboAgentList.Column(2) = "" Then
  TAgncy = "FTE"
    Else
  TAgncy = "TEMP"
End If
Dim QDef As DAO.QueryDef
Dim StrSQL As String
Dim Boo As String

Boo = "TktEmailTmp"
StrSQL = "INSERT INTO tri.TktEmailTmp (EmpID, EmpName, TempAgency) " & _
              "VALUES('" & EmpId & "', '" & EmpName & "', '" & TAgncy & "')"
 
QDefTktBuild Boo, StrSQL
 
DoCmd.OpenQuery ("TktEmailTmp")

Function QDefTktBuild(strQueryName As String, strQDefSQL As String)

On Error GoTo ErrorHandler
   
    Dim qdfPassThrough As QueryDef
        
        If QryExists(strQueryName) = True Then
            CurrentDb.QueryDefs.Delete strQueryName
            GoTo BuildItNow
        Else
BuildItNow:
           Set qdfPassThrough = CurrentDb.CreateQueryDef(strQueryName)
            With qdfPassThrough
                .Connect = strConnection
                 .SQL = strQDefSQL
                 .ODBCTimeout = 60
                .ReturnsRecords = False
            End With
        End If
    
Exit Function
ErrorHandler:
   'It's another error, display message
   MsgBox Err.Description

End Function

My question lies with using DoCmd.OpenQuery - In this case I do not want to 'Open' the query per say, I just want to 'Run' it
But DoCmd.Run or DoCmd.Execute do not exist - Any Idea's

Thank You...

[pbaldy]

You could try:

CurrentDb.Execute "TktEmailTmp", dbFailOnError

I personally wouldn't delete/recreate the query, I'd have it saved and just change its SQL.

Edit: I should add I've used OpenQuery to execute an action query too, it works fine.

[RunTime91]

Paul - You're always out there for us rooks - And I certainly appreciate it

Thank You, Sir

[pbaldy]

Happy to help!