Why do I get "Invalid Qualifier" with the following code

Hello experts, in an effort to gain a greater understanding of coding in access, I'm trying to remove all my docmd. statements.  While they were handy initially, I feel they have reduced my knowledge curve.  That said, please tell me what I'm doing wrong in the code below and why do I get the message "Invalid Qualifier".  The DoCmd query was simply a delete everything from a table type.
Sub SubEmptyPreviousWeeksData()

    Dim db As DAO.Database
    Dim QryDefinition As DAO.QueryDef
    Dim strSql As String
    
    On Error GoTo errorhandler
    
    strSql = "DELETE tmptblWeeklyInvoice.* FROM tmptblWeeklyInvoice;"
    
    Set db = OpenDatabase(CurrentProject.Path & "\MyComicShopTables.accdb")
    strSql.Execute
    
    db.Close
    Set db = Nothing
    
    QryDefinition.Close
    Set qrydefintion = Nothing
    
    Exit Sub
    
errorhandler:
    
    MsgBox Err.Number & ":" & Err.Description
    
End Sub

Open in new window

StuBabyAightAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
I believe this:

    strSql = "DELETE tmptblWeeklyInvoice.* FROM tmptblWeeklyInvoice;"
   
    Set db = OpenDatabase(CurrentProject.Path & "\MyComicShopTables.accdb")
    strSql.Execute

Should be:

    strSql = "DELETE tmptblWeeklyInvoice.* FROM tmptblWeeklyInvoice;"
   
    Set db = OpenDatabase(CurrentProject.Path & "\MyComicShopTables.accdb")
    db.Execute strSql

mx
0
 
Kelvin SparksCommented:
   Set db = OpenDatabase(CurrentProject.Path & "\MyComicShopTables.accdb")
set QryDefinition = db.CreateQueryDef("",strSQL)
    qryDefintion.Execute
 
Kelvin


0
 
StuBabyAightAuthor Commented:
Cheers, but that then throws up the error "Object variable with block variable not set"  
Could oyu please help with that too
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Referring to ?

mx
0
 
StuBabyAightAuthor Commented:
My Comment was to DatabaseMX, will try th other now
0
 
StuBabyAightAuthor Commented:
I don't knopw what it is referring to.  I'm assuming that is the error handler kicking in
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
What line of code is the error occurring on?

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
QryDefinition  is not even being used anywhere ... ?

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Comment out On Error GoTo errorhandler to see where the error occurs.

mx
0
 
StuBabyAightAuthor Commented:
This answer was complete and I should have focused completely on it.  Getting two responses so quickly, I was trying to be too accomodating.  Thanks to both of you for your help, but this is the what I needed for an answer.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"This answer was complete and I should have focused completely on it. Getting two responses so quickly, I was trying to be too accomodating. Thanks to both of you for your help, but this is the what I needed for an answer."

You are welcome ...

Gotta LOVE EE ... Real TIme :-)

mx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.