3

I have a quirk with my Access app that I cannot debug. I have a form that essentially makes an insertion into a 'completed' table and a deletion from a 'pending' table. Design preferences aside, this is how I'm doing it rather than having some sort of 'Status' column.

I'll summarize my code with the relevent lines.

First it makes the insertion into the completed table, then the simple deletion:

    qr = "DELETE * FROM tblPendingClasses WHERE ((tblPendingClasses.ID = " & curr         & "));" 'build the query
    Debug.Print qr
    MsgBox (qr)
    dbMine.Execute qr, dbFailOnExecute

     Form.Refresh 'refresh,requery
     Form.Requery
     MsgBox ("Class review complete.")
     docmd.close

The query is built fine, from the debug statement and msgbox, but it just doesn't do anything. I look at the table for pending classes, and the class with the ID I'm trying to delete is just sitting there. No meaningful errors, nothing. This used to work, and works sometimes, but is not working at the moment.

Ideas?

Improve this question
3
  • what is your datatype for the curr variable? Also, what is it for ID field in tblPendingClasses. If it is not a number, you will need to put 's arround the curr variable, like so: ((tblPendingClasses.ID = '" & curr & "'));" Commented Jan 2, 2013 at 19:27
  • ID and curr are both numbers, matching data types. I found out what the problem is, thanks. Commented Jan 2, 2013 at 19:33
  • @Scotch Just a quick observation, as delete queries only action rows and not individual columns, you don't need to specify DELETE * FROM, DELETE FROM will be sufficient. Commented Jan 3, 2013 at 10:45

1 Answer 1

Reset to default
4

If your module includes Option Explicit, you should get a compile error, "Variable not defined" on this line:

dbMine.Execute qr, dbFailOnExecute

Probably you intended dbFailOnError instead of dbFailOnExecute as the option for the .Execute method. Add Option Explicit to the Declarations section of your code module so the VBA compiler will alert you when you attempt to use the name of a constant which doesn't exist (dbFailOnExecute). Then compile and fix any other issues the compiler complains about.

The Database.Execute Method help topic recommends:
"... always use the dbFailOnError option when using the Execute method to run an update or delete query. This option generates a run-time error and rolls back all successful changes if any of the records affected are locked and can't be updated or deleted."

So dbFailOnExecute will give you the run-time error message which explains why the expected deletions didn't happen.

After making those changes, if you still don't get any rows deleted and no error message, make sure you don't have SetWarnings switched off.

DoCmd.SetWarnings True
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

I changed it to failOnError. I still didn't get any informative error message. I did just figure out what the problem was. Somehow a blank record (aside from a foreign key) was added to a related table. When I manually went in to attempt to delete the record, it warned me that it cannot be deleted because there are related records in "xxx". I'm not sure why it wouldn't have told me that at runtime though. Anyways, that was the problem -- a random junk record with a foreign key to the record in question.
Check SetWarnings has not been switched off.
Suggest you also find out who turned SetWarnings off and find some way to effectively persuade them never to do it again. ;-)
Read the rest of that help topic (emphasis mine): "In a Microsoft Access workspace , if you provide a syntactically correct SQL statement and have the appropriate permissions, the Execute method won't fail — even if not a single row can be modified or deleted. Therefore, always use the dbFailOnError option when using the Execute method to run an update or delete query. This option generates a run-time error and rolls back all successful changes if any of the records affected are locked and can't be updated or deleted."
+1 --exactly what I was wondering. This quote summarizes the problem in question. Maybe add that to your answer since it's being collapsed in the comments?
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.