Prevent Change Allow New

[jmceuin]

I am putting a simple database to track off site storage boxes. I have a field for the box number. It is set no duplicates. I further need to prevent the user from changing the field if it has data (not null) already but allow a new record to be created. I tried to use undo after update (before and change as well) but that prevents going from NULL on a new record to a defined box number. Suggestions?

 

[Mr. B]

jmceuin,

First, I would suggest that when you get this working where changes can not be made once an entry has been made to this field, you will most likely immediately run into a situation where there is a need to edit the content of this field. Just a word to the wise. It seems to never fail. You may need some kind of "back door" to allow a change when absolutely necessary.

With that said, I would use some VBA code in a couple of places. First use the After update event of the "box number" control to lock the control with code like this:

If not isnull(me.NameOfBoxNumberControl) then
Me.NameOfBoxNumberControl.locked = true
End If

Then in the On Current event of your form place the same code. This will lock the same control each time you arrive at any record where the Box Number had a value.

HTH

 

[ansentry]

Put this code in the OnCurrent event of your form; Change txtBoxNumber to suit your situation

Private Sub Form_Current()

If Me.NewRecord Then
    Me.[B]txtBoxNumber[/B].Locked = False
Else
    Me.[B]txtBoxNumber[/B].Locked = True
End If

End Sub

 

[jmceuin]

John A, thank you so much. That works just as I need it to do.

 

[jmceuin]

Mr. B. Thanks. In this case the Box number must never be changed. Once in the system I don't want the users to ever change it. If needed, I will just go into the table and make a correction, but for audit purposes I need it to stay in once it is there.

 

[ansentry]

jmceuin,

Your welcome.

For what it is worth I agree with Mr B, you should have a "back door" into the data.

However in the end it is your database and this is only 1 ( or 2) persons opinion.

 

[jmceuin]

I do agree, but I do not want the users to have a backdoor. Since it is a database I have direct access to the tables while the users are restricted to the forms provided from the switchboard. If needed I could create another form to modify if not just going straight to the tables, but I find direct modification of the tables to be easy enough.