messge box for certain codes on data entry into a form

[Sheilard]

Staff have to enter data between A000 and Z999 into a field in an access form. I need a pop up box for any code between J000 and J999 stating that they need to check the authorisation for this item. T

[jimatqsi]

Pop up messages are really annoying. Okay if you must, but I would rather turn the box red or display something on the screen without requiring a click. Anyway, add some code to the After Update event of the field being entered. If the text is being entered into textbox Item_txt, then in the after update event put

    me.item_txt.backcolor=vbwhite
if me.item_txt >= "J000" and me.item_txt <= "J999" then
    msgbox "whatever"
    me.item_txt.backcolor=vbred
end

Of course you don't need to do both the red and the msgbox, choose what you like. And red is awfully harsh, I nearly never use plain red.

Jim

[Sheilard]

hmm didn't seem to like that

I got a complie error : Method or data member not found..

.item_txt is highlighted

[Sheilard]

@Sheilard

The field is called subjective

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
Me.subjective.BackColor = vbWhite
If Me.subjective >= "J000" And Me.item_txt <= "J999" Then
MsgBox "This is J code, please check authorisation"
Me.subjective.BackColor = vbRed
End
 
End Sub
 
End Sub

[Sheilard]

Thank you so much Jimatqsi. That now works a treat