kylerogers
asked on
Errors when using RunCommand > DeleteRecord in a macro after SP3 installed
This worked fine before I installed SP3 for Office XP. I had SP2 and all pre-SP3 patches installed before.
My form [fForm] is tied to a temp table [tTemp]. Data is entered into the form and then the user pushes the "Save" button that I have placed on the form. This button runs a macro mFormSave which does the following:
RunCommand > SaveRecord
OpenQuery > qAppend (this is a simple query that appends [tTemp] to [tProduction]
RunCommand > DeleteRecord
Now, The macro appears to complete because the record is saved, appended, and then deleted. But then Access throws up an error where you can choose "halt" to halt the macro. The error code is 223, but I can't find any info on it. Here is the weird thing. If I remove the DeleteRecord from the macro, the record is saved and appended and the macro ends without arror. Clearly a DeleteRecord problem. BUT, if I run the "no DeleteRecord" macro and then hit the ">x" (Delete Record) button on the toolbar, it deletes the record without incident. I just doesn't like it when I do this in a macro. I even tried creating a second macro that only had RunCommand > DeleteRecord and running that at the end of the first macro. Same error. Then tried adding a button to the form that only ran the "DeleteRecord" macro and tried hitting it after running the shortened first macro via the "Save" button. Same error. It only happens when DeleteRecord is used in a macro.
One other piece of information that might be useful. I recently installed the post-SP3 Access patch hoping it would fix the problem. But it made it worse. Now, I still get the 223 error that I have to "Halt", but after that, I also get another error that says "no current record"
I need to find out what is causing this and how to get around it. I can't just hit the ">x" button on the toolbar because I have to enter hundreds of records in a 1 hour sitting and halting a macro error followed by clicking OK on the second error for each record would take up way too much time and be rather annoying. Any help would be greatly appreciated. Thank you.
My form [fForm] is tied to a temp table [tTemp]. Data is entered into the form and then the user pushes the "Save" button that I have placed on the form. This button runs a macro mFormSave which does the following:
RunCommand > SaveRecord
OpenQuery > qAppend (this is a simple query that appends [tTemp] to [tProduction]
RunCommand > DeleteRecord
Now, The macro appears to complete because the record is saved, appended, and then deleted. But then Access throws up an error where you can choose "halt" to halt the macro. The error code is 223, but I can't find any info on it. Here is the weird thing. If I remove the DeleteRecord from the macro, the record is saved and appended and the macro ends without arror. Clearly a DeleteRecord problem. BUT, if I run the "no DeleteRecord" macro and then hit the ">x" (Delete Record) button on the toolbar, it deletes the record without incident. I just doesn't like it when I do this in a macro. I even tried creating a second macro that only had RunCommand > DeleteRecord and running that at the end of the first macro. Same error. Then tried adding a button to the form that only ran the "DeleteRecord" macro and tried hitting it after running the shortened first macro via the "Save" button. Same error. It only happens when DeleteRecord is used in a macro.
One other piece of information that might be useful. I recently installed the post-SP3 Access patch hoping it would fix the problem. But it made it worse. Now, I still get the 223 error that I have to "Halt", but after that, I also get another error that says "no current record"
I need to find out what is causing this and how to get around it. I can't just hit the ">x" button on the toolbar because I have to enter hundreds of records in a 1 hour sitting and halting a macro error followed by clicking OK on the second error for each record would take up way too much time and be rather annoying. Any help would be greatly appreciated. Thank you.
ASKER
I don't really know VB. It's on my "to-do" list, but I haven't gotten to it yet. Unless you have a way to create the code for the DeleteRecord and I could run that code at the end of the macro instead of the DoCommand > DeleteRecord. But doesn't that macro just create the VB in the background to do that?
Kinda sorta...which, incidentally, is one of the reasons VB is recommended over macros. VB is not that hard to learn, especially in respect to Access. A good into is "Beginning Access 2002 VBA" by Wrox Publishing. That text will take you from basic concepts and structure, through advanced techniques. VERY good book.
For the VB code, I would say it's actually easier, and better design, to translate the whole thing into VB rather than just one line. Try the other suggestion I posted, and any other suggestions posted by other experts, in the meantime. If we still can't get it working, post your macro and I'll see if I can't hook you up. :)
For the VB code, I would say it's actually easier, and better design, to translate the whole thing into VB rather than just one line. Try the other suggestion I posted, and any other suggestions posted by other experts, in the meantime. If we still can't get it working, post your macro and I'll see if I can't hook you up. :)
You can convert your macro to VB simply by;
On the Database window
Select Macros
Select the macro name
On the menu Tools>Macro>Convert macro to VB
Note: You will not loose your original macro, what will happen is a Module will be created containing
the equivalent codes in VB of your Macro.
Select modules on the Database window and select the module that was created and double click on it
to view the VB codes.
Copy the codes and post here so we could see waht is really happening
On the Database window
Select Macros
Select the macro name
On the menu Tools>Macro>Convert macro to VB
Note: You will not loose your original macro, what will happen is a Module will be created containing
the equivalent codes in VB of your Macro.
Select modules on the Database window and select the module that was created and double click on it
to view the VB codes.
Copy the codes and post here so we could see waht is really happening
ASKER
The record being entered is the only record on the temp table. The table never grows larger than the one record being entered, so I'm not sure that I can change records. Also, could you explain a little more about requerying the recordset?
ASKER
Cool. Thanks Capricorn1. I will do that and post the code shortly
Good call, cap. I woulda been sittin here writing some air-code for the chap. :) Instead? Time for lunch! WOOHOO!
routinet
Have a nice lunch!!!
Have a nice lunch!!!
ASKER
Here is the code. It should be pretty simple. Which is why I don't understand where Access is running into trouble
Option Compare Database
Option Explicit
'-------------------------
' mTapesReceivedSave
'
'-------------------------
Function mTapesReceivedSave()
On Error GoTo mTapesReceivedSave_Err
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenQuery "qAppendTapesReceived", acViewNormal, acEdit
DoCmd.RunCommand acCmdDeleteRecord
mTapesReceivedSave_Exit:
Exit Function
mTapesReceivedSave_Err:
MsgBox Error$
Resume mTapesReceivedSave_Exit
End Function
Option Compare Database
Option Explicit
'-------------------------
' mTapesReceivedSave
'
'-------------------------
Function mTapesReceivedSave()
On Error GoTo mTapesReceivedSave_Err
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenQuery "qAppendTapesReceived", acViewNormal, acEdit
DoCmd.RunCommand acCmdDeleteRecord
mTapesReceivedSave_Exit:
Exit Function
mTapesReceivedSave_Err:
MsgBox Error$
Resume mTapesReceivedSave_Exit
End Function
Change the OpenQuery line to:
CurrentDB.Execute "qAppendTapesReceived"
Also, do you need to return a value from this function? If not, replace the word "Function" with "Sub". Otherwise, specify a return type in the header, and a return value in the code. The DoCmd statements generated by Access for the save and delete SHOULD work. If you're using a bound form, there is another syntax you can use that is just as simple.
CurrentDB.Execute "qAppendTapesReceived"
Also, do you need to return a value from this function? If not, replace the word "Function" with "Sub". Otherwise, specify a return type in the header, and a return value in the code. The DoCmd statements generated by Access for the save and delete SHOULD work. If you're using a bound form, there is another syntax you can use that is just as simple.
ASKER
I will try that, but the macro appeared to be executing the query without incident. It wasn't until the DeleteRecord that I was receiving an error. If I remove the DeleteRecord from the macro, it runs without error.
If the current record is going to be the only record on the table, you can alter the code to do this:
DoCmd.RunCommand acCmdSaveRecord
CurrentDB.Execute "qAppendTapesReceived"
CurrentDB.Execute "DELETE * FROM MyTableName"
DoCmd.RunCommand acCmdSaveRecord
CurrentDB.Execute "qAppendTapesReceived"
CurrentDB.Execute "DELETE * FROM MyTableName"
Question.
Which record do you want to delete? From where?
Please explain what you are trying to do
regards
rey;-)
Which record do you want to delete? From where?
Please explain what you are trying to do
regards
rey;-)
ASKER
I didn't mention this in my previous post, but I basically did this by creating a query "Delete * FROM TableName". It ran without error, but then when I went back to the form, a lot of the fields had #DELETED in them. The form, tables, and queries we're dealing with here are used to track backups at sites. There are other tables, forms, etc in the database that do other things too, but those work fine. Since there is almost no change from one record to the next (95% of the backups are successful, tapes were switched out, etc.), all someone had to do on the form in the past was pick the next site on the list and click "save", pick the next site and "save", etc. for about 90 sites. Then go back and enter the info on the ones that had backup issues. But when I do a DELETE *, it erases a lot of the fields, so I can't do that. I hate to keep shooting stuff down. I'm just trying to get at least close to the simplicity that I had pre-SP3. The problem seems to have to do with running DeleteRecord in a macro, but only in a macro.
ASKER
When typing data into the form, you are entering a record into the temp table. After appending that record to the production table, I want to delete the record out of the temp table (thus emptying it without using a DELETE * FROM Table, which causes other problems). This used to work before SP3. It still works if I hit the Delete Record toolbar button, but not when DeleteRecord is used via a macro
what is the other usage of the temp table? maybe you can do without it and just append the
table production directly from the form.
table production directly from the form.
ASKER
We (in my company) had talked about it. The temp tables are to keep people from accidentally entering erroneous records or messing up existing records. That way if they mess something up, it only messes up the temp table which gets a DELETE * on form close anyway.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry. Today is the first day I have been back in the office since Friday. Thank you for your suggestions routinet. I will try some out and post my results
My other suggestion is to try changing records before attempting the delete. Access handles deleted records in a funny way (at least, to me!), and my normal work-arounds all involve requerying the recordset.