Solved

Errors when using RunCommand > DeleteRecord in a macro after SP3 installed

Posted on 2004-04-30
21
956 Views
Last Modified: 2008-03-10
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.
0
Comment
Question by:kylerogers
  • 9
  • 6
  • 4
21 Comments
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
My first suggestion to you is to migrate your macro to VB.

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.

0
 

Author Comment

by:kylerogers
Comment Utility
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?
0
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
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.  :)

0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
0
 

Author Comment

by:kylerogers
Comment Utility
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?
0
 

Author Comment

by:kylerogers
Comment Utility
Cool.  Thanks Capricorn1.  I will do that and post the code shortly
0
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
Good call, cap.  I woulda been sittin here writing some air-code for the chap.  :)  Instead?  Time for lunch!  WOOHOO!
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
routinet
Have a nice lunch!!!
0
 

Author Comment

by:kylerogers
Comment Utility
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
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
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.

0
 

Author Comment

by:kylerogers
Comment Utility
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.
0
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
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"
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
Question.
Which record do you want to delete? From where?
Please explain what you are trying to do

regards
rey;-)
0
 

Author Comment

by:kylerogers
Comment Utility
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.  
0
 

Author Comment

by:kylerogers
Comment Utility
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
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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.
0
 

Author Comment

by:kylerogers
Comment Utility
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.
0
 
LVL 50

Accepted Solution

by:
Steve Bink earned 500 total points
Comment Utility
Sidenote on the DELETE query: You can get rid of the #DELETED fields in the form by simply requerying the form, or moving to a new record after save.  That is only a cosmetic issue.  And now for something completely different.....(my .02 on database design)

Using a temp table does not really help you since the user could easily mess up the entry in the temp table, still save it to the master table, and you are back at square one.  The moment they hit the save button and the macro runs, it does ALL of the steps without consideration for data correctness.  Instead, the temp table is just an extra step that a) is unnecessary, b) adds processing time to the user activities, and c) adds unneeded complications into a step that should be REALLY simple.

My new recommendation is to remove the temp table entirely, and proceed accordingly.  If you MUST MUST MUST keep the temp table, the #DELETED fields on your form from using the DELETE query can be solved by simply requerying the form's recordset and then moving to a new record.  Since your form is bound to the temp table, of course it is going to show #DELETED when you delete the current record.  You can requery and move to a new record like this:

Me.Requery
DoCmd.GoToRecord , , acNewRec

If you feel you have data integrity issues, research the use of transaction-based SQL.  It provides for a built-in 'temp table', so to speak.  When a user enters a record, they can put in whatever they want, but it will not actually make it to the master table until they (or you, or someone else with the appropriate authority) COMMIT the transaction.  This gives you plenty of opportunity for form verification, data checking, whatever you want to do.  If the new record fails in any way, you can rollback the entire transaction in just one command.

I know you want to keep things as simplistic as possible, and I definitly agree with you on that point.  However, the design you are moving forward with is not simplistic, and as the db grows in size and scope, those design flaws will grow worse and worse.
0
 

Author Comment

by:kylerogers
Comment Utility
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
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now