Prevent User Input if there's a record in a field

[ychousa]

Hi. I was wondering if there's a way to prevent user input if there's already a record in the field. I would like to give an error message something like: MsgBox ("You can't modify this record") on the after update event of a field. What I can think of now is a logic like this.

After Update:

If Not IsNull(Me![Serial Number]) Then

Do whatever!

Else

MsgBox ("You can't delete this record")

End If

But I need to give the error msg before the data is changed, or somehow need to prevent the change. How could I achieve this? Thanks for any comment.

 

[PHV]

Have you tried the BeforeUpdate event procedure ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GingerR]

Hi. I'm a little confused by your statement

"...if there's already a record in the field..."

Do you mean if there's already DATA in the field?

In order to prevent any change, how about simply locking the field? Put in the control's OnEnter event:

if not isnull(me.[Serial Number]) then
    me.[Serial Number].locked = true
else
    me.[Serial Number].locked = false
end if

 

[ychousa]

Thanks for your comments.
Yes, I meant "if there's already DATA in the field." I tried the code on both onEnter and BeforeUpdate events, but it doesn't seem it's working. Basically, a user can enter Serial Number if the field is null, but he cannot do so if there's already data in that field-that means if someone already entered a Serial Number on that field.

The code suggested by GingerR locks the record regardless of the DATA existance. Did I do something wrong? I tried to change "Me.[Serial Number]" to "Me![Serial Number]," but it didn't work either.

 

[Edski]

I think GingerR meant to say: put it in the "On Current" Event for the Form, right? ;-)

 

[GingerR]

No, I put it OnEnter of the text box [Serial Number]. It doesn't lock the entire record, it just locks the text box. Maybe you also have to check for

...isnull(me![Serial Number]) or me![Serial Number] = ""...

 

[ychousa]

GingerR's code was right. The field that I tested was not null, even though it didn't have any data in it. I changed to Me![Serial Number]="" then it's working perfect. Thank you.

 

[PHV]

To test the control for either null, empty or blank:
If Trim(Me![Serial Number] & "") = "" Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

How are ya ychousa . . . . .

To be more operationally correct, I would use the fields [blue]GotFocus or OnEnter[/blue] event to determine wether to lock/unlock:

[blue]   If Trim(Me![Serial Number] & "") <> "" Then
      Me.[Serial Number].locked = True
   End If[/blue]

Then in the [blue]OnExit or LostFocus[/blue] event, turn locking off.

[blue]Me.[Serial Number].locked = False[/blue]

See Ya! . . . . . .