Form field not validating

[RickRo]

I have an inventory form where I have To and From fields (moving a device From somewhere To somewhere else). I have an unbound search field that sets up the form, and in the AfterUpdate I some house cleaning by moving the current To values to the From values, then clearing the To values with Null's.

'Write To values then clear the values
     Me.ImpactTicket = Null
     Me.Status = Null
     Me.FromBuilding = Me.ToBuilding
     Me.ToBuilding = Null
     Me.FromFloor = Me.ToFloor
     Me.ToFloor = Null
     Me.FromRoom = Me.ToRoom
     Me.ToRoom = Null

In the form I want to ensure that the user puts information into the fields so I have tried to check the field value by:

Setting the Validation Rule (Is Not Null) & Validation Text - didn't work

Putting code into the Before Update event - didn't work

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.ImpactTicket) Then
    MsgBox "Please fill in the Impact Ticket."
    Me.ImpactTicket.SetFocus
End If

End Sub

What am I missing? Why don't either of these two methods fire?

Rick

 

[John Big Booty]

Try;

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.ImpactTicket) Then
    MsgBox "Please fill in the Impact Ticket."
    Me.ImpactTicket.SetFocus
    Cancel = True
End If

End Sub

You require the Cancel = True to stop the Update process otherwise it just continues merrily on it's way

 

[RickRo]

It still goes merrily on its way - that didn't work.

Interestingly I found if I just put a space in and try to tab to the next field, that fires the damn thing - but just tabbing past it does nothing.

Rick

 

[Pat Hartman]

Putting a space in the field results in a ZLS (Zero-Length_String) which is not a null. To check for either ZLS or Null you can use one of the following two expressions:
If Len(Me.somefield) = 0 Then 'field is null or ZLS
or
If Me.somefield & "" = "" Then 'field is null or ZLS

Interestingly I found if I just put a space in and try to tab to the next field, that fires the damn thing - but just tabbing past it does nothing.

Code in a control event won't detect "missing" data if the user doesn't actually change the value in a field. Just passing through won't trigger the control's BeforeUpdate event. That is why the FORM's BeforeUpdate event is the only viable option for identifying missing data or performing validation that involves multiple controls.

 

[RickRo]

Ahh now we are getting somewhere...I had changed the code to the field property taking it off of the form property.

So once I got the code settled back in where it belongs - the message box does pop up telling me that I need to fill in the blank field.

However...it looks like the rest of the code that is on the button continues to run, closing my from ( I have a DoCmd.Close acForm, Me.Name at the end of the button code). If I comment that out, the form doesnt close, but it doesnt return focus to the field either.

It seems as though I'm damned if I do or damned if I don't lol

How can I get the error check - return to the form if its not right, and if it is, save the current data, clear the current data and be ready for the next serial number scan?

 

[Pat Hartman]

It can be done easily enough if you put your code in the correct events. I just answered this question in this post:

http://www.access-programmers.co.uk/forums/showthread.php?t=243572&highlight=close+form