Lock Record when "Add" button is pressed

[jo15765]

I was going to add a textbox to the form of the records that are locked and say something to the effect of "This record is locked, you can only enter notes" since there is a notes field on the form that can be modified/updated at any time.

[RuralGuy]

You will find that you cannot protect the user from themself. You might be able to help just a little bit if you have an "Edit" button that Unlocks *that* record only until they either cancel the edit or save. Then go back to *all* locked records and let them move around.

[jo15765]

I agree, there is no way to completely prevent accidents from happening.

I was going to either add a checkbox or maybe a button, that will be used for unlocking this ONE specific record, but then once the user makes whatever modification they need to make the record will be locked again once the "Add" button is pressed.

I also have it set up to display what user name made the modifications, so then I know who is modifying records.

I am just struggling to figure out how to disable all fields on the form once the button has been pressed, or after "X" amount of time.

[RuralGuy]

What is the matter with pkstormy's suggestion in Post #8?

[jo15765]

I don't see where to input the .AllowUpdates. Is that a VB Command?

[RuralGuy]

It is VBA and you should put it in the CurrentEvent of the form:
Me.AllowEdits = False

[jo15765]

That's what I was after. Thanks!

[RuralGuy]

I'm headed to bed now. Later.

[pkstormy]

I do not want to complicate the process, I am just trying to prevent accidental deletion from the table. I know I can have a backup of the table, and can backup regularly etc. but I was thinking that if I locked the record after entry or maybe even 5 or 10 minutes after entry it would save some grief when it comes to accidental deletion?

I think you're looking at it from the wrong approach. If you want to prevent deletion, then on the form's property you can use the allowdeletions and set this to false. But have you thought about having a delete button on the form and when it is clicked, have it run an append query which appends the current record on the form that's going to be deleted to a 'backup' type of table first? (this would just be a table that has the same fields as the main data table and you'd use criteria in the append query to append only the record with the unique ID value (probably you're autonumber field) that's on the form.) This could simply be done with a couple lines of vba code and would happen automatically and user's wouldn't need to jump through hoops to enter a new record.


Note that in my previous post, I specifically mentioned 'allowEDITS' property for the form. There isn't any property such as 'allowupdates'. You misinterpreted or over-looked the vba example I gave. Look at the form's properties and you'll notice the AllowEdits, AllowDeletions, and AllowAdditions properties you can set for the form. Also notice the DataEntry property which you may be able to utilize in your approach as RuralGuy mentioned. See help on the DataEntry property. This makes it so the form is new entry only. Then iIf a user needs to find an existing record, I'd design a search type form which after criteria is entered, then opens the data form to the record. If you need help on search type forms, there are a lot of examples in the code repository section on this site. This is typically how it's done.

Again, keep in mind that you want to keep things very simple for users. If you want to make things difficult for them, then add in that difficulty when deleting records, not when adding new records. Users will most likely be adding new records a lot more often then they'll be deleting records and you don't want to really slow down the data entry side of things. You need to put yourself in the user's shoes and ask yourself "what would be the easiest if I was a user?".

[jo15765]

on the form's property you can use the allowdeletions and set this to false.

When I open my form and click on View ---> Properties I do not see the allow deletions option?

But have you thought about having a delete button on the form and when it is clicked, have it run an append query which appends the current record on the form that's going to be deleted to a 'backup' type of table first?

I REALLY like this idea, and I know I just would add an 'onClick' event to the properties of my delete button. What VBA Code would work to make this occur? I have written the append query, that was the easy part, however I am still trying to learn VBA and am looking through the Access 2000 VBA Handbook as well!

Note that in my previous post, I specifically mentioned 'allowEDITS' property for the form. There isn't any property such as 'allowupdates'.

I am still not seeing how to get to the 'allowedits' or the 'allowupdates' of the form?

[RuralGuy]

I am still not seeing how to get to the 'allowedits' or the 'allowupdates' of the form?

While looking at the form in Design mode, press the F4 key and then look on the Data tab.

[jo15765]

When I click f4 and then view the properties of the form, the data tab is all gray (with no options at all on it) see screenshot.

[RuralGuy]

You have the Detail Section selected. You need to select the Form by clicking on the little box in the upper left corner of the form.

[jo15765]

I found it now!

[pkstormy]

I REALLY like this idea, and I know I just would add an 'onClick' event to the properties of my delete button. What VBA Code would work to make this occur? I have written the append query, that was the easy part, however I am still trying to learn VBA and am looking through the Access 2000 VBA Handbook as well!

Your onClick event vba coding for the delete button might look like this (note: use the button wizard to quickly create the vba delete part of the coding and then edit):

Private sub cmdDelete_OnClick()
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