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?
Prevent Change Allow New (1 Viewer)
- Thread starter jmceuin
- Start date
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
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
Access amateur
- Local time
- Tomorrow, 01:19
- Joined
- Jun 1, 2003
- Messages
- 995
Put this code in the OnCurrent event of your form; Change txtBoxNumber to suit your situation
Code:
Private Sub Form_Current()
If Me.NewRecord Then
Me.[B]txtBoxNumber[/B].Locked = False
Else
Me.[B]txtBoxNumber[/B].Locked = True
End If
End SubMr. 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.
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.
