Protecting data once form completed

[Antoinette]

Hi, I hope someone can help me. In my database I have a Client_Details form and need to find a way of locking individual records (i.e. once clients data has been entered in form view) to avoid accidental changes being made, but still need to be able to use the same form to create new records. Ideally I would like to place a check box on the form, that can be selected/deselected to allow editing if required. I have put a boolean field (FormCompleted: Yes/No) in the main table but cannot find a way of utilising it! Any help would be gratefully received but I am not very good at VB

 

[John Big Booty]

You could put some code like the following in your form's On Current event;

   If Me.Dirty = False Then
        Me.AllowEdits = False
    Else
        Me.AllowEdits = True
    End If

You could then have a button call allow edits that then fired the following;

Me.AllowEdits = True

 

[missinglinq]

The idea of having a command button is right, because if the record is locked using
Me.AllowEdits = False, then you can't untick the FormCompleted checkbox to allow editing.

But

If Me.Dirty = False Then
        Me.AllowEdits = False
    Else
        Me.AllowEdits = True
    End If

in the Form_Current event will always leave the record locked, because a record is never Dirty when the Form_Current event fires!

This code will do the job:

Private Sub FormCompleted_AfterUpdate()
 If FormCompleted = -1 Then
  Me.AllowEdits = False
 Else
  Me.AllowEdits = True
 End If
End Sub

Private Sub Form_Current()
If FormCompleted = -1 Then
  Me.AllowEdits = False
 Else
  Me.AllowEdits = True
 End If
End Sub

Private Sub cmdUnlockRecord_Click()
 Me.AllowEdits = True
End Sub

 

[John Big Booty]

Sorry missinglinq the On Current event as out lined above seems to work for me

 

[missinglinq]

As I said, your code will always lock the record, because Me.Dirty = False will always be true in the Form_Current event. You're simply locking every record without making any provision to check to see whether or not the record has been marked as "completed." It will lock all records, completed or not.

What your code does could be accomplished by simply setting the AllowEdits Property to Yes in the Properties Sheet for the form.

 

[John Big Booty]

OK I think I misunderstood what you where saying.

As you say, what my code does is to assumes that any record that contains data is complete, hence the allow edits button. Given that using the Allow Edits = False method necessitates the use of a button, it rather make a check box redundant (as well as complicating the code, slightly), unless of course it is being used for some other purpose elsewhere.

 

[missinglinq]

FormCompleted is not redundant at all. The checkbox is being used to indicate that the record is complete. Just because the record contains some data doesn't necessarily mean that it contains all the data that is necessary for the record to be considered as complete. Data is often entered in multiple sessions before a record is complete. According to the OP's original post, this a decision that has to be made by the user.

 

[John Big Booty]

I hear what you are saying or have I understood what you have written
However I see no danger in simply locking any record that contains data given that it can simply be unlocked by the button that is required anyway. It simply adds an additional layer of protection against inadvertent changes.

The only reason I can see for using the check box, is if you are going to restrict the ability to edit "Completed" records (which was not, at this point, a concern the OP expressed).

I suspect that this is simply a case of, There's ALWAYS more than one way to skin a cat! and we should simply agree to disagree

 

[Antoinette]

Hi John

Thanks for the prompt reply. How simple - when you know what you're doing First code set worked great, once record completed chages couldn't be made but new records could be created, but unfortunately I couldn't get the Me.AllowEdits = True to work, any other ideas?

Antoinette

 

[John Big Booty]

You will need to put a button on the form and then put that code in the On Click event behind the button, it will look like the last piece of code in Missinglinq's first post, or alternatively have a look at the sample DB I uploaded.

 

[Antoinette]

Thanks John (& Missingling)

Finally cracked it!! I misinterpreted your instructions but eventually got there, sooo easy - once you know how!

Really appreciate the help (& found your interactions to be interesting)

Kind regards

Antoinette

 

[John Big Booty]

Glad we could help

 

[John Big Booty]

Glad we could help

BTW very remiss of both of us not to have welcomed you to the forum, earlier

 

[anski]

i have a form with a subform. the codes that you gave work on the MAIN form. my SUBFORM is in DATASHEET VIEW. how will i be able to add the 'allow edit' button to my subform and make that 'allow edit' button to work as a whole in the subform (not on a per record basis because this is tedious).

or do you know of any other way that changes in the SUBFORM will prompt the user to save or undo changes.