Solved QueryDef causing Error_Handler Error

[chizzy42]

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

call Topx(3)

.

This in turn called:

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.

 

[jdraw]

and the LogError is highlighted in the Error_Handler.

indicates LogError is not defined or is not in scope


This sample seems to work

Sub chkTop(n As Integer)
    Dim sql As String
 
    Dim num As Integer
    num = n
    sql = "Select Top " & num & " aname, animalID  from Animal order by animalId desc;"
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef("XYZ2", sql)
    db.QueryDefs.Refresh
End Sub

called with
call chktop(4)

Result:

 

[Gasman]

You are also not using your passed parameter?
I would also expect the query name to be within quotes for the delete and create if not using a parameter for that.

 

[moke123]

Why not just modify the Sql by passing the values to it.

Sub  TopX(TopNum as long, etc, etc)
Sqlstr = "SELECT TOP  " & TopNum & "  Sum(qryFailcode.Qty) . . .

 

[chizzy42]

HI Guys thanks for the replies, i modified the handler code to

Error_Handler:
strMsg = Err.Number & vbCrLf & Err.Description
MsgBox (strMsg)
'            sModName & "/CreateQry", _
'            , True
    Resume Error_Handler_Exit

and it gave a 3075 error in the string code

Why not just modify the Sql by passing the values to it.

Sub  TopX(TopNum as long, etc, etc)
Sqlstr = "SELECT TOP  " & TopNum & "  Sum(qryFailcode.Qty) . . .[/CO
[/QUOTE]

 

[Gasman]

And 3075 is?

 

[chizzy42]

Hi Moke sorry i used the wrong version there i sent the the code without the parameter being used.
Gasman i tried the quotesto but no joy ive attached the fail i now get at the error handler.

thanks all

 

[chizzy42]

i think its to do with the double quotes? when i change

" HAVING(((qryFailcode.[fail code]) <> "")

to

" HAVING(((qryFailcode.[fail code]) <> '')

The error goes but still doesn't produce a query

 

[moke123]

you cant use double quotes within a string. (ie. <>"")

"HAVING(((qryFailcode.[fail code]) <> "") And ((qryFailcode.text1) Like [Forms]![Form1]![text2]) And ((qryFailcode.text3) Like [Forms]![Form1]![Text70])) " & _

 

[chizzy42]

HI , i doubled up on the double quotes and it seems to work, is this the proper way to do this in this situation?

 

[moke123]

If it works then yes.

I still question why you need a query def when you can construct the SQL without it.

Where does this code reside? Form module or standard module? you can either pass the values to the sub or you can use the form references in your sql.

 

[chizzy42]

It was something i was trying to work out initially it was just a query that a report called and I had it at a default of 10. Then tried to see if I could make it more flexible and tried the QueryDef. The code now sits on a standalone mode and is called from the button on the form . I didn't think i could make the SQL value variable without a QueryDef.

 

[moke123]

You can pass the values from your form like

TopX 4, me.DTPicker1, Me.DTPicker2, me.Text2, Me. Text70

Sub Topx(topnum As Integer,DTP1 as date, DTP2 as date, txt2 as string,txt70 as string)

Dim Sqlstr As String

Sqlstr = "SELECT TOP " & TopNum & " Sum(qryFailcode.Qty) AS SumOfQty, qryFailcode.[fail code], qryFailcode.Text1, qryFailcode.cell, qryFailcode.text3" & _
" FROM qryFailcode " & _
" WHERE  qryFailcode.registered  Between  # " & DTP1 & "# and # " & DTP2 & "#  " & _
" GROUP BY qryFailcode.[fail code], qryFailcode.Text1, qryFailcode.cell, qryFailcode.text3 " & _
"HAVING(((qryFailcode.[fail code]) <> "") And ((qryFailcode.text1) Like """ & txt2 & """ And ((qryFailcode.text3) Like """ & txt70 & """ & _
"ORDER BY Sum(qryFailcode.Qty) DESC , qryFailcode.[fail code] DESC;"

DONT copy and paste this as it is aircode just to give you an idea.

 

[chizzy42]

Thanks moke123, i hadn't thought of that.