Help with passing variables into quries through vba

[vba317]

I am reposting this from another forum, my apologies. I am using access 2003. I have a string of queries that I want to run in sequence. Some of the queries have variables that need to go into them. I have figured out how to calculate the variables but I don't know how to pass tha variables into the queries. I am trying to pass the string strCurMon into .OpenQuery "001_PostPmtsToTemp" . I am trying to pass strCurMonName into .OpenQuery "102_PostChgAmt. Any help would be appreaciated.

'Current Period
strCurMonName = MonShortName(CurMon)
'Current Month
strCurMon = Right(strCurMonName, 2)

Private Sub cmdImport_Click()

Dim strUCI As String
Dim strFileCS As String
Dim strFileAR As String
Dim strFileFullAR As String
Dim liFileMonth As String
Dim strFileMonth As String
Dim strClose As String
Dim strDictUpdt As String
Dim intCurMon As Integer
Dim strCurMon As String
Dim strCurMonName As String


DoCmd.SetWarnings False

Me.txtUCI.SetFocus
strUCI = Me.txtUCI.Text

'On Error GoTo IMP_ERR

If Not IsNull(Me.cboImpMonData.Value) Then
'Me!cboImpMonData.Column (1)
  
    
    liFileMonth = Me.cboImpMonData.Value
    strFileMonth = Me.cboImpMonData.Column(1)
    'Current Period
    strCurMonName = MonShortName(CurMon)
    'Current Month
    strCurMon = Right(strCurMonName, 2)
          
       ' IMPORT FILE & RUN FIXES
    With DoCmd
        .OpenQuery "000_ClearCalcPmts"
        .OpenQuery "000_ClearPatients"
        .OpenQuery "000_ClearTempMarkBilled"
        .OpenQuery "001_PostPmtsToTemp"  ' enter month
        .OpenQuery "002_PostAggregatePmts"
        .OpenQuery "102_PostChgAmt"  'enter period
        .OpenQuery "103_SetChgAmt"
        .OpenQuery "104_PostPatients"
        .OpenQuery "105_ClearMarkBilled"
        .OpenQuery "106_PostBilled"
        .OpenQuery "107_MarkBilled"
    End With

 strClose = "UPDATE DICT_ImportMonthList SET finalpost=1 WHERE (ID=" & (liFileMonth) & ");"
    CurrentDb.Execute strClose
    With Me.cboRptMon
        .SetFocus
        .Requery
    End With
    ' DONE
    MsgBox "SUCCESSFUL IMPORT!", , "WHOO!"
Else
    MsgBox "Please select a month for import.", , "ERROR"
End If

DoCmd.SetWarnings True

Exit Sub

IMP_ERR:
    DoCmd.SetWarnings True
    MsgBox "File Not Found.", , "ERROR"
    Exit Sub

End Sub

 

[Golom]

Since I don't see the SQL for the queries I'll just go with a hypothetical example. Suppose we had this SQL in a query named Q1

PARAMETERS MinAmount As Currency, MaxAmount As Currency;
SELECT ID, SUM(Amount) As SumAmount
FROM myTable
WHERE Amount BETWEEN MinAmount AND MaxAmount
GROUP BY ID

Then in VBA code

Dim db  As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDB

Set qdf = db.QueryDefs("Q1")
qdf("MinAmount") = 100.00
qdf("MaxAmount") = 500.00
DoCmd.OpenQuery("Q1")

 

[vba317]

my sql is as follows:
strSQL = "SELECT DatRpt.uci, DatRpt.rptpd, DAT_Pats.PatName as 1,DAT_Pats.AcctNu as 2," & _
" DatRpt.doschg as 3,DatRpt.ChgAmt as 4,DatRpt.pmts as 5" & _
" FROM DAT_ReportData DatRpt" & _
" INNER JOIN DAT_Pats ON DatRpt.aid = DAT_Pats.aid" & _
" WHERE DatRpt.rptpd=" & CStr(liPdID) & " And DatRpt.bill=" & (intChBx) & _
" ORDER BY DAT_Pats.PatName,DatRpt.doschg"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
If (rst.RecordCount > 0) Then
With rst
.MoveLast
.MoveFirst
End With
End If
iRwCnt = 7 ' Row starts at 7
iBegRow = 7 ' First iBegRow
' 'Counter to loop through all records
For Z = 1 To rst.RecordCount
With goXL.ActiveSheet
.Cells(iRwCnt, 1) = rst![1]
.Cells(iRwCnt, 2) = rst![2]
.Cells(iRwCnt, 3) = rst![3]
.Cells(iRwCnt, 4) = rst![4]
.Cells(iRwCnt, 5) = rst![5]
End With
rst.MoveNext
iRwCnt = iRwCnt + 1
Next Z

 

[Golom]

That's not a query in the Access sense i.e. it exists as an SQL statement in your VBA code and you are running it from code and not as a stored query. Your original question was how to pass parameters to a stored query and I gave you the method for doing that.

What is the issue with this last piece of code that you posted? Other than your needing quotes around CStr(liPdID), I don't see any problem with it.

 

[vba317]

I got distracted, sorry. My question is this:
When the query .OpenQuery "001_PostPmtsToTemp" executes, the query asks to enter the month. In VBA the variable for month is strCurMon. Is there a way to enter the variable strCurMon into the query 001_PostPmtsToTemp automaticaly without prompting?

If there is, the query .OpenQuery "102_PostChgAmt needs to have the variable strCurMonName entered in with vba code.

 

[dhookom]

The only reason you would need to open any of the queries would be if they were action queries. I would use the code at faq701-7433 to dynamically change the SQL property of the saved query.

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

You do need to know the parameter name(s) inside the query's SQL. Then

Dim qdf As DAO.QueryDef
Dim db  As DAO.Database

Set db = CurrentDB
Set qdf = db.QueryDefs("001_PostPmtsToTemp")
qdf.Paramteres("CurrentMonth").Value = strCurMon
qdf.Execute "001_PostPmtsToTemp"

I have assumed that the name of the parameter in the query's SQL is 'CurrentMonth'. Change it to the correct value. Note that you can use "qdf.Execute" only on action queries ... not on select or union queries.

Alternatively, you can use Duane's suggestion to replace the query's SQL.

Finally, you can create a temporary query

Dim qdf     As DAO.QueryDef
Dim qdfTemp As DAO.QueryDef
Dim db      As DAO.Database
Dim SQL     As String

Set db = CurrentDB
Set qdf = db.QueryDefs("001_PostPmtsToTemp")
SQL = qdf.SQL
[red]'- Make changes to the SQL here[/red]
Set qdfTemp = db.CreateQueryDef("", SQL)
qdfTemp.Execute

Temporary queries (note the blank name) are not added to the QueryDefs collection.

 

[vba317]

001_PostPmtsToTemp" is an append query so I should not have a problem using the qdf.execute method. I am getting a runtime error 3421 Data type conversion error. The parameter "Enter Current Month" is what is currently the criteria of the query. I also tried to change the CStr to CInt the error did not change. The code that defines the string is as follows:

       'Code that defines the string
       strCurMon = Right(strCurMonName, 2)
       strCurMon = CStr(strCurMon)

        Set db = CurrentDb
        Set qdf = db.QueryDefs("001_PostPmtsToTemp")
        qdf.Parameters("Enter Current Month").Value = strCurMon
        qdf.Execute "001_PostPmtsToTemp"

 

[vba317]

I realized that my variable was dimmed incorrectly, I changed it to Dim intCurMon As Integer the code reads as follows:
When I stop the code and hover over intCurMon I am getting an 8 instead of an "8" which is what I want. But I am still getting the 3421 error.

Dim intCurMon As Integer
 'Current Month
    strCurMon = Right(strCurMonName, 2)
    intCurMon = CInt(strCurMon)

 

[Golom]

In a small test I did this

Set qdf = db.QueryDefs("Insert Value Into AAA")

qdf.Parameters("Enter X").Value = 999
qdf.Parameters("Enter B").Value = "ABC"
qdf.Execute

Where the SQL in the query is

INSERT INTO aaa ( x,b )
VALUES ([Enter X], [Enter B] )

and all is well.

Can you post the SQL that is in "001_PostPmtsToTemp"? You might also do

Debug.Print strCurMon

and set a breakpoint on the line where you are attempting to set the property. Look at the immediate window (Ctrl-G) to see what you are actually sending to the query.

 

[vba317]

The sql code for the query is as follows:

INSERT INTO _Calc_Pmts ( uci, rptpd, trantype, doschg, aid, eid, slid, tid, cptcode, cptcomp, pmts )
SELECT "WMG" AS u, dbo_bi_Transactions.rptpd, dbo_bi_Transactions.trantype, dbo_bi_Transactions.doschg, dbo_bi_Transactions.aid, dbo_bi_Transactions.eid, dbo_bi_Transactions.slid, dbo_bi_Transactions.tid, dbo_bi_CPT.cptcode, dbo_bi_CPT.cptcomp, (CDbl([amt])) AS pmts
FROM dbo_bi_Transactions INNER JOIN dbo_bi_CPT ON (dbo_bi_Transactions.cptid = dbo_bi_CPT.cptid) AND (dbo_bi_Transactions.clntid = dbo_bi_CPT.clntid)
WHERE (((dbo_bi_Transactions.rptpd)=[Enter Current Month]) AND ((dbo_bi_Transactions.trantype)=4) AND ((dbo_bi_CPT.cptcode)="91110") AND ((dbo_bi_CPT.cptcomp)<>"P") AND ((dbo_bi_Transactions.clntid)=73));

I added the debug statement and the stop
intCurMon = 8
When I hover over
qdf.Parameters = ("Enter Current Month"),.value ="8"
ctrl G window says 08

Set db = CurrentDb
        Set qdf = db.QueryDefs("001_PostPmtsToTemp")
        qdf.Parameters("Enter Current Month").Value = intCurMon
        Debug.Print intCurMon
        qdf.Execute "001_PostPmtsToTemp"

 

[Golom]

From your table name dbo_bi_Transactions, dbo_bi_CPT I infer that this is a pass-through query. Is that the case? If it is then you can't use the above method because Access doesn't process the SQL in pass-through queries.

If it is a pass-through then look at this KB article for how to simulate parameters in a pass-through query.

 

[vba317]

Thanks for the help you gave me.