Hi, hope all is well. I was trying to extend a query that I had for getting the top 10 results by making the selection variable i.e. top 3 top 7 . The only way i could see to dit was taking the query and using QueryDef and VBA to run a module that would allow the SQL to be modified reading from a text box. to begin with i tried to use an integer to call the module sub
.
This in turn called:
The problem is whe i run the code i get an error : Compile Error, sub or function not defined , and the LogError is highlighted in the Error_Handler. I tried commenting out the Error handler it then runs but does not produce a Query, is there something wrong with the way I'm trying to use QueryDef.
Any explanations as usual are gratefully received.
Code:
call Topx(3)
This in turn called:
Code:
Sub Topx(topnum As Integer)
Dim Sqlstr As String
Sqlstr = "SELECT TOP 10 Sum(qryFailcode.Qty) AS SumOfQty, qryFailcode.[fail code], qryFailcode.Text1, qryFailcode.cell, qryFailcode.text3" & _
" FROM qryFailcode " & _
" WHERE (((qryFailcode.registered) Between [forms]![Form1]![DTPicker1] And [forms]![Form1]![DTPicker2])) " & _
" GROUP BY qryFailcode.[fail code], qryFailcode.Text1, qryFailcode.cell, qryFailcode.text3 " & _
"HAVING(((qryFailcode.[fail code]) <> "") And ((qryFailcode.text1) Like [Forms]![Form1]![text2]) And ((qryFailcode.text3) Like [Forms]![Form1]![Text70])) " & _
"ORDER BY Sum(qryFailcode.Qty) DESC , qryFailcode.[fail code] DESC;"
On Error Resume Next
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
With db
.QueryDefs.Delete (qryFailcodeD)
On Error GoTo Error_Handler
Set qdf = .CreateQueryDef(qryFailcodeD, Sqlstr)
End With
db.QueryDefs.Refresh
Error_Handler_Exit:
On Error Resume Next
Set qdf = Nothing
Set db = Nothing
Exit Sub
Error_Handler:
LogError Err.Number, Err.Description, _
sModName & "/CreateQry", _
, True
Resume Error_Handler_Exit
End Sub
The problem is whe i run the code i get an error : Compile Error, sub or function not defined , and the LogError is highlighted in the Error_Handler. I tried commenting out the Error handler it then runs but does not produce a Query, is there something wrong with the way I'm trying to use QueryDef.
Any explanations as usual are gratefully received.