My query isn't updating....

[ineedahelp]

Do I need to use the REQUERY method in my code below? The query strSQL isn't updating with new information when my variables change. Can someone help me with the correct code to "refresh" my query? Also, is my code correct for setting up a query? Thank you for your help!!

[code]
Private Sub comboExcelForVendor_AfterUpdate()
On Error GoTo Err_MyProc
Dim dbs As DAO.Database, qdf As DAO.QueryDef, strSQL As String
Set dbs = CurrentDb
Dim strTableName, strClient, strExcelName, strDate As String
Dim myPos


strTableName = Forms!frmDailyClient!comboExcelforVendor
myPos = InStr(1, strTableName, "_", vbTextCompare) - 1
strExcelName = Left(strTableName, myPos)
strDate = Right(strTableName, 7)

strSQL = "SELECT TOP 20" & strTableName & ".Symbol, " & strTableName & ".Cusip, " & strTableName & ".TotalQty FROM " & strTableName & " " & _
"GROUP BY " & strTableName & ".Symbol, " & strTableName & ".Cusip, " & strTableName & ".TotalQty, " & strTableName & ".ML_DailyRate " & _
"HAVING (((" & strTableName & ".ML_DailyRate) < 4.5 Or (" & strTableName & ".ML_DailyRate) = 4.5)) " & _
"ORDER BY " & strTableName & ".ML_SMV ASC;"

Set qdf = dbs.QueryDefs("qryCreateExcel")
'qdf.SQL = strSQL
qdf.Close

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryCreateExcel", "c:\documents and settings\Leslie\My Documents\Vodia Group\" & strExcelName & "\" & strExcelName & " Rate Request" & strDate & ".xls", True
MsgBox ("File successfully exported")
Set qdf = Nothing
Set dbs = Nothing

Exit_MyProc:
Set dbs = Nothing
Set qdf = Nothing
Exit Sub

Err_MyProc:
Resume Exit_MyProc

End Sub

[NeoPa]

The first bit of the SQL (SELECT TOP 20) is missing a space after the 20 - this may well cause a problem.
In SELECT queries with only one recordsource, it's not necessary to quote the source before each field.
It does no harm in the general course of events, but makes something like this more complicated than it needs (IMHO) to be.
As for the SQL - I can see nowhere that it's used so I'm unable to help with refreshing it I'm afraid.

[ineedahelp]

Thank you for noticing the missing space! What I need this code to do is update the query qryCreateExcel each time my variable strTableName changes. Although my variable is changing, the expression strSQL isn't CHANGING qryCreateExcel. Any thoughts? Thanks!

[ineedahelp]

Oh, I forgot...my TRANSFERSPREADSHEET command is using qryCreateExcel...should I forget about qryCreateExcel and use strSQL directly in the TRANSFER SPREADSHEET command. I thought I had to use a "legitimate" table or query in this command.

[NeoPa]

Now we have some light on the problem.

Set qdf = dbs.QueryDefs("qryCreateExcel")
'qdf.SQL = strSQL
qdf.Close

From your posted code, you have the important line removed by the comment character (') at the start the line.
Try removing the comment char.

If this concept is faulty (editing the SQL of a saved query) then create a new query instead with the SQL and just delete it when finished.

[ineedahelp]

I have included my NEW code...I have changed the order and placement of some of my commandes and have changed my strSQL statement. Should I use the .CREATEQUERYDEF or just .QUERYDEF? Also, my code doesn't run and when I try to open qryCreateExcel, I get an error message #3122 "You tried to execute a query that does not include the specified expression variable name.ML_SMV as part of an aggregate function. Any thoughts? thanks!!!!!

[ineedahelp]

I got it working! thank you for hanging in there with me!!!