Lock down records

[Caddie]

Hello All -

I've created a contract management database. We have several people logging our contracts into it right now, once a contract is entered the data is verified by a 2nd person. There is a "Status" field in the database that is either "Pending" or "Verified". Once the 2nd person verifies the data they switch the status field from Pending to Verified.

I'd like to know, is there a way to "lockdown" or become read-only, that record once the Status field is switched to Verified? Ideally, it would be nice if the status is switched back to Pending then we can edit the data again, then if switched back to Verified it would lock down again.

Thanks.

 

[vbaInet]

Are we talking about a continuous form or a datasheet?

 

[dkinley]

Not sure ... posted in the 'Tables' section .... ??

 

[Caddie]

The users add and edit data using a form.

 

[Caddie]

True dkinley, should this post be moved to the Forms formum?

 

[vbaInet]

It turns out to be form

Is the form continuous or a datasheet?

 

[Caddie]

I'm sorry but what do you mean by continuous? The form I created is as follows:

Customer Info (Parent)
Contract Details (Status field is in this sub form) (Child of Customer info)
Service Address Details (Child of Contract Details)
Service Details (Child of Service Address Details)

Before entering a new contract the user must select the customer # from a combobox at the very top of the form. This auto-populates the customer information. The user then enters the rest of the details.

 

[vbaInet]

Go to the Property Sheet of the form, look at the Default View property under Format tab and tell us what it says there.

 

[Caddie]

It says Single Form.

 

[vbaInet]

On the On_Current event of your form, you would put this type of code:

If [Status] = Verfied Then
    Me.Status.Locked = True
Else
    Me.Status.Locked = False
End If

This isn't locking the record, but it is locking the control that displays the record. Try it out.

 

[Caddie]

That makes perfect sense, except what if a user wants to "unverify" it? So they can edit or update something then re-verify it afterwards?

 

[vbaInet]

You can set a button to Unlock the control. But again, that defies the whole point of locking it in the first place.

 

[Caddie]

Sorry, I just want to clarify something:

In your suggestion above, would this "lock" only the status field or the whole form?

 

[vbaInet]

It would lock the just the control that displays the status field. So no, not the entire form.

 

[Caddie]

Ok, I was not clear in my initial question, my bad. What I want is to be able to lock the form if the status field is set to "Verified". This way a user can't accidentally change verified data. However, I need to be able to empower them to make intentional changes. So, they should be able to go to a verified record, change the status from Verified to Pending thereby unlocking the form so they can once again edit the record. Once complete they would then change the status to Verified again. The one issue I can think of though is if the form is locked how would they access the status field to be able to update it to Pending?

I hope it's clear but it's not easy to describe. This may not even be the best way to do it, but it's one way I thought of.

 

[vbaInet]

In my last post I clarified that the form isn't going to be locked, just the control. Try out the methods provided and you will understand what's going on.

Another option is if you want users to be able to intentionally change the status field if it had already been Verified, then you could prompt them with a message to proceed with the change or drop it.

 

[dkinley]

Put the following in your form Open event.

If Me.cmbComboBoxName = "Verified" Then Me.AllowEdits = False

As vbaInet stated, the users will not be able to roll it back, though. If you wanted to, you could put a password on the combo box to allow certain users access to roll it back. But give that a month or two and everyone would have said password unless it is just you who can authorize the rollback.

-dK

 

[Caddie]

When I type Me.Status. the only option after this that comes up is VALUE.

I'm getting the following Compile Error:

Method or data member not found.

 

[dkinley]

Oops. I am thinking that should be under the OnCurrent event of the form. You may or may not have to use the .Value or .Column(x) of the combo box depending on the bound column, etc.

-dK

 

[vbaInet]

What kind of control is Status?