Solved QueryDef causing Error_Handler Error (1 Viewer)

chizzy42

Registered User.
Local time
Today, 10:13
Joined
Sep 28, 2014
Messages
115
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
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.
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:13
Joined
Jan 23, 2006
Messages
15,364
and the LogError is highlighted in the Error_Handler.
indicates LogError is not defined or is not in scope


This sample seems to work
Code:
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:
 

Attachments

  • sampleTopn.png
    sampleTopn.png
    7.4 KB · Views: 173
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 10:13
Joined
Sep 21, 2011
Messages
14,050
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

AWF VIP
Local time
Today, 06:13
Joined
Jan 11, 2013
Messages
3,852
Why not just modify the Sql by passing the values to it.

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

chizzy42

Registered User.
Local time
Today, 10:13
Joined
Sep 28, 2014
Messages
115
HI Guys thanks for the replies, i modified the handler code to
Code:
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.

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

chizzy42

Registered User.
Local time
Today, 10:13
Joined
Sep 28, 2014
Messages
115
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
 

Attachments

  • Screenshot 2022-04-25 141406.png
    Screenshot 2022-04-25 141406.png
    6.6 KB · Views: 185

chizzy42

Registered User.
Local time
Today, 10:13
Joined
Sep 28, 2014
Messages
115
i think its to do with the double quotes? when i change
Code:
" HAVING(((qryFailcode.[fail code]) <> "")
to
Code:
" HAVING(((qryFailcode.[fail code]) <> '')
The error goes but still doesn't produce a query
 

moke123

AWF VIP
Local time
Today, 06:13
Joined
Jan 11, 2013
Messages
3,852
you cant use double quotes within a string. (ie. <>"")

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

chizzy42

Registered User.
Local time
Today, 10:13
Joined
Sep 28, 2014
Messages
115
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

AWF VIP
Local time
Today, 06:13
Joined
Jan 11, 2013
Messages
3,852
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

Registered User.
Local time
Today, 10:13
Joined
Sep 28, 2014
Messages
115
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

AWF VIP
Local time
Today, 06:13
Joined
Jan 11, 2013
Messages
3,852
You can pass the values from your form like
Code:
TopX 4, me.DTPicker1, Me.DTPicker2, me.Text2, Me. Text70

Code:
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.
 

Users who are viewing this thread

Top Bottom