IsNull or Less Than (1 Viewer)

[Tim Bedborough]

Hi all
May just be wrong end of day but can you help with this one please.

I'm trying to make sure that an end user can not enter a value which is either Null or less than the existing value.

This code brings up a message box if null or less than 19500 but rather than specify a number, i.e. 19500 I just want to refer to current field value.

Thanks

Private Sub RPRRef_LostFocus()
If IsNull(Me.RPRRef) Or Me.RPRRef < 19500 Then
MsgBox "The job reference number cannot be left blank or lower than 19500 or a previously allocated number." & vbCrLf & _
"The deleted number will be reinstated.", vbExclamation, "RPR JOB NUMBER"
DoCmd.GoToControl "RPRRef"
DoCmd.RunCommand acCmdUndo
End If
End Sub

 

[Minty]

You need to use the before update event. Something like

Dim iOld as Long
iOld = Me.RPRRef.OldValue
If IsNull(Me.RPRRef) OR Me.RPRRef < iOld THEN ....

 

[Tim Bedborough]

Thanks Minty

I ended up with following which seemed to do the trick.

Private Sub RPRRef_LostFocus()

If IsNull(Me.RPRRef) Or Me.RPRRef < Me.RPRRef.OldValue Then
MsgBox "The job reference number cannot be left blank or lower than the last number in the sequence." & vbCrLf & _
"" & vbCrLf & _
"The deleted number will be reinstated.", vbExclamation, "RPR JOB NUMBER"
DoCmd.GoToControl "RPRRef"
DoCmd.RunCommand acCmdUndo
End If

End Sub

 

[Minty]

I would not use on lost focus simply because you will check every time someone moves through the field, rather than only when it's been updated.
But that's just personal taste.

 

[Tim Bedborough]

I have tested and it only brings up the error message if the field (RPRRef) is blank or user tries to change it. The field is a sequential number (not autonumber) that I don't want changed once created. I have a new record wizard which pre populates this field but I was struggling to lock it for other reasons so needed a way of avoiding it being changed and keeping it enabled and unlocked. Seems to work for what I needed it for (so far!) but your words of wisdom appreciated. It's been a bit of trial and error putting the right code in the right event procedure so far.

 

[gemma-the-husky]

if the number is automatically generated, why enable the field at all

simply in the forms beforeupdate event

 if isnull(rprref) then
    rprref = getnextnumber
 end if

 

[Tim Bedborough]

Thanks for tip.

I might have done this hard way but various pointers suggested that I create a separate table for the next sequential number to avoid multiple users creating 2 records at once and causing corruption. When my new record wizard runs it opens the separate table for the next sequential number locking other users out until the process has finished (that's the theory from what I read elsewhere anyway).

 

[gemma-the-husky]

either.

a) get the number from a separate table OR
b) get it with a dmax in the form's beforeupdate event. that way the risk of two users getting the same number is minimised, as the table is in use for a microsecond only.