By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,628 Members | 1,175 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,628 IT Pros & Developers. It's quick & easy.

Deleting Records in the Form View

P: 11
This can be ignored... I did a bunch more searching and tried a few more things listed on different posts. I was able to get the delete query to work properly by adding a statement in the code (Me.Requery) to refresh the information on the form and the #deleted went away.

--------------------------------------------------------------------------------------------------------------


First of all, I want to say thanks for help on my previous posts. This site has been really helpful.

I have a database (DB1) that contains information about drawing markups with records dating back to 2005. In order to keep the size of the database manageable, I am archiving the older records to a separate database (DB2) while keeping a record in DB1 of the markup IDs and the drawing numbers of records that are in DB2. In the form that is used for data entry for the drawing markups, I added a button that runs a macro with the following steps:

1. Displays a message that you are archiving a record.
2. Opens an append query (aqry1) to copy the information in the record from DB1 to DB2.
3. Opens an append query (aqry2) to copy the markup ID and drawing number from the current record to a new table in DB1.
4. Closes the two append queries.

I've gotten the two queries to work properly, but I also want to be able to delete the current record from DB1. I've tried a couple of different ways to do this, but none have been truly successful yet. First I tried adding a step 5 to the macro that used the RunCode action and ran the following procedure:

Expand|Select|Wrap|Line Numbers
  1. Public Function deleteRecord()
  2. On Error GoTo Err_deleteRecord
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    Exit_deleteRecord:
    Exit Function
    Err_deleteRecord:
    MsgBox Err.description
    Resume Exit_deleteRecord
    End Function
For whatever reason, when the macro runs, it never deletes the record. Because this didn't work, I also tried using a delete query. I added two steps to the macro to open the delete query and then close the delete query. While this works to delete the information in the record, it doesn't truly delete the record. The fields for the record all say #Deleted, but the record physcially remains attached to the database.

Is there a way to get the record to truly delete and be removed from DB1?

I've also tried using different delete record commands and none of them seem to work. On one or two of the other delete commands, I got a message saying that the delete record option wasn't available. Any advice on how to get this working would be really helpful.

Thanks,
Sarah
Jun 18 '07 #1
Share this Question
Share on Google+
1 Reply


nico5038
Expert 2.5K+
P: 3,072
I try to prevent to use the DoCmd.DoMenuItem command as it won't work when the menuitem(s) aren't available.

Instead I use the currentdb.execute statement like:

Expand|Select|Wrap|Line Numbers
  1. currentdb.execute ("delete * from tblX")
  2. 'or for one row:
  3. currentdb.execute ("delete * from tblX where ID=" & Me!ID)
  4.  
Thus the table is managed directly and also no warningmessages will appear.
They can be activated when you want by adding the appropriate parameter to the command, but in general it's better to suppress them :-)

Nic;o)
Jun 18 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.