?
Solved

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

Posted on 2010-01-04
11
Medium Priority
?
513 Views
Last Modified: 2013-11-29
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

0
Comment
Question by:StuBabyAight
  • 6
  • 4
11 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1000 total points
ID: 26178137
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 26178143
   Set db = OpenDatabase(CurrentProject.Path & "\MyComicShopTables.accdb")
set QryDefinition = db.CreateQueryDef("",strSQL)
    qryDefintion.Execute
 
Kelvin


0
 

Author Comment

by:StuBabyAight
ID: 26178156
Cheers, but that then throws up the error "Object variable with block variable not set"  
Could oyu please help with that too
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 75
ID: 26178163
Referring to ?

mx
0
 

Author Comment

by:StuBabyAight
ID: 26178168
My Comment was to DatabaseMX, will try th other now
0
 

Author Comment

by:StuBabyAight
ID: 26178171
I don't knopw what it is referring to.  I'm assuming that is the error handler kicking in
0
 
LVL 75
ID: 26178173
What line of code is the error occurring on?

mx
0
 
LVL 75
ID: 26178180
QryDefinition  is not even being used anywhere ... ?

mx
0
 
LVL 75
ID: 26178185
Comment out On Error GoTo errorhandler to see where the error occurs.

mx
0
 

Author Closing Comment

by:StuBabyAight
ID: 31672796
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
 
LVL 75
ID: 26178216
"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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question