Deleting Records in the Form View

[Shortstuff12345]

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:

Public Function deleteRecord()
On Error GoTo Err_deleteRecordDoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70Exit_deleteRecord:Exit FunctionErr_deleteRecord:MsgBox Err.descriptionResume Exit_deleteRecordEnd 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

[nico5038]

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:

currentdb.execute ("delete * from tblX")
'or for one row:
currentdb.execute ("delete * from tblX where ID=" & Me!ID)
 

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)