Lock Record when "Add" button is pressed

[jo15765]

I added this line of code for the 'onClick' expression for my delete button:

Private Sub CMD_DeleteButton_Click()

Dim QI As Integer
QI = MsgBox("Are you sure you want to delete this record?", vbYesNo)
If QI = vbNo Then Exit Sub 'exit routine if user selects No.

DoCmd.SetWarnings False
DoCmd.OpenQuery "MyQueryToAppendTheRecordOnTheFormToAnotherTab le"

'For creating the below vba coding to delete the record, use the wizard since it is different depending on your MSAccess version
'ie. use the wizard to first create a delete button to delete the record (which will have coding similar to that below), and then edit the OnClick() vba code putting in the above coding.

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

'Below is extremely important! You want to make sure you turn on error warnings when done!

DoCmd.SetWarnings True 'Note: to turn back on error checking!! - Important.

End Sub

But when I click on the delete button on my form, I receive the following error (see screenshot)

[pkstormy]

Did you follow my instructions and first create a button via the wizard which deletes a record?

Your error indicates that there is a syntax problem with the code which is most likely the lines:
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Since I use MSAccess 2003 on my computer, unless you also have 2003, your code to delete the record would look different.

You NEED to use the code to delete a record that corresponds with your MSAccess version. The easiest way to do this is to use the wizard when creating your delete button. Select Record Operations, then Delete Record for the wizard.

Also make sure to debug/compile the code (ie. in the coding window, select Debug then Compile.) You always want to do this any time you make any coding changes.

Also note: You need to design your append query to do the backup and then change the line:
docmd.openquery "MyQueryToAppendTheRecordOnTheFormToAnotherTab le" to whatever your query name is.

[jo15765]

To add the delete button, I added a command button, then chose Record Operations --> Delete Record.

The error was actually in another line of VBA code I was working on, not this one. Sorry!

Altho, when I delete a record, it is backing up all records in the database, not taking the one record I am deleting to the "deleted" table. Is there a way to only append the one record, that is attempting to be deleted to the "deleted" table

[pkstormy]

I expected your post on the append query copying all records versus the 1 record.

You need to put in criteria in your append query. Criteria for the 'ID' field or whatever your unique identifier is (the autonumber field is often the best choice.) Please tell me that you have an autonumber field type in the table. If not, you can easily add one to the table. You should always have an autonumber field type in every data table (unneeded on lookup type tables but to me, a necessity for any tables that are being constantly updated. This will also save you problems in the future.)

1. Make sure the 'ID' or unique identifier field is on the form.
2. Then for criteria in the append query under that field, put in something like: =Forms!MyFormName!IDFieldName.

This way it will only append the 1 record from the form that matches that criteria.

Note: You may also want to add some vba code in your cmd_Delete onClick() event that checks to make sure the autonumber (or unique identifier) field is not blank in the beginning of the code for this event. Ex: If isnull(Forms!MyFormName!MyIDFieldName) then exit sub

KEY things to do in every data table (that is actively being updated by users):

1. Have an autonumber field type (you can only have 1 per table.) - MSAccess will then create a unique number value for every record entered. You wouldn't want to use this field though as an indicator of how many records are entered or to do any counts in a query since MSAccess doesn't 'back-fill' in deleted autonumber values. BUT you would want to use this field in queries such as your append query to identify a specific record. Again, make sure the autonumber field is on the form (you can make this field locked = true and enabled = false or visible = false since it won't actually be updated by the user but it is a good reference which user's can identify the record with. I personally call my autonumber field: PersonID for my data table which has the first/last name fields but you can call the field anything.) You would then have criteria in your append query under the PersonID field such as: =Forms!MyFormName!PersonID to identify a specific record to copy in the query. It will then only append 1 record versus all the records which your query currently is doing.

2. Set the Primary Key on the field or fields that you don't want duplicated (ie. if you create a primary key on the Name_First AND Name_Last fields, a user would never be able to duplicate a first and last name - ideally though you'd want create a Primary key on other fields versus the First and Last name fields since it's most likely that user's would be entering the same first and last name but it would actually be 2 separate persons - ie. there are multiple John Doe's in the phone book.) Hence you wouldn't want to prevent a duplicate first/last name to be entered. Some designers will set the primary key on the first, last, and city field but chances are that there is someone with the same first and last name in the same city. This is a 'your' call type of thing where you'd want to set the primary key on the field(s) that are best for your application.

3. NEVER use the Lookup tab for a field. (just not a good idea - more confusing than anything - bad feature Microsoft put into MSAccess.) - if you're not sure what I mean - just remember to never use the 'Lookup' tab for a field in your table design.

4. Have a 'DateEntered' field (date/time field type) and set the defaultvalue of this field on the form to =Now(). (so you can tell when the record was entered.)

5. Have an 'EnteredBy' field (text) and set the defaultvalue of this field on the form to the loginID of the user (see here to get the default user: https://www.accessforums.net/code-re...thod-7675.html). This again, will save you time in the future as you'll then know who entered that new record.

And lastly, NEVER use spaces or odd characters (such as $#@!) in a field or table name. Also never start a field with a number (ie. 1FirstName). MSAccess doesn't like this and it will cause problems in the future. You can get away with using spaces but save yourself some future hassle with vba coding and don't put spaces in the field or table name (otherwise you'll always need to put brackets [some field name] around any code referencing that field or table.)

I hope this helps.

[jo15765]

I did change the query name, I realized that was an issue after I posted, but that has been corrected.

1 I have the 'ID' for each entry on my form.
2 I put the criteria in my query [Forms]![Table1]![ID] but whenever I Try to run the query it asks me to input this information?

I added the "if null" entry into the cmd_Delete_onClick event

I have set input =Now() in my entered by, and date & Time entered 'inputMask' field, but it is not populating either one for whatever reason, so I am attempting to resolve this. But yes I am trying to add those fields to the database as well. (i tried to open your database for reference but its an unrecognizes format...)