BeforeUpdate not working right

[AmigoMobility]

I not sure what I'm doing wrong here. I have searched several forums and "think" I'm doing this the same way.

I'm trying to validate some fields before allowing the record to be saved, which if the user doesn't fill in the appropiate fields then the msgbox does fire, and the record is not saved but my problem is that I don't want the form to close. I would like to force the user back to the form and fill in the required fields. Any idea's as to why the forms closes?

Here's my code.

Dim ctl As Control

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
With ctl
If ctl.Tag = "Required" Then
If ctl.Value = "" Or IsNull(ctl.Value) Then
Me.PartNumber.SetFocus
MsgBox "Missing data"
Cancel = True
ctl.Undo
End If
End If
End With
End If

 

[ZmrAbdulla]

Why not reading
Form - looping thru the controls faq702-5010 ?

________________________________________________________________________
Zameer Abdulla
Visit Me
A sweater is usually put on a child when the parent feels chilly.

 

[AmigoMobility]

Thanks ZmrAbdulla. I'm confused as to why you would want me to read this article. Everything in my code works, so looping through my controls, is working. The only thing that I can not figure out is why my form closes anyway. I'm trying to find out if I've left something out of my code or somthing, that does not stop the form from closing.

 

[ZmrAbdulla]

In which event you are trying to validate the fields? You can disable the close button until you validate the data. Some time Close Event is too late to validate the data
Also I think the sequence of code is not correct it should be something like below
[tt]
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
With ctl
If ctl.Tag = "Required" Then
If ctl.Value = "" Or IsNull(ctl.Value) Then
MsgBox "Missing data"
Me.PartNumber.SetFocus
'ctl.Undo (do you need this?)
End If
End If
End With
End If
Cancel = True 'This to cancel from form to close should be
'after the validation area
[/tt]

________________________________________________________________________
Zameer Abdulla
Visit Me
A sweater is usually put on a child when the parent feels chilly.

 

[RoyVidar]

I don't think your code is directly involved with the closing or not of the form. That event is "finished" when the record is either saved or not, and other events come into play, determined by what action triggered the before update event in the first place.

My guess is that you are probably allowing your useres to close the form through hitting the upper right "x"? If so, the "close process" continues after the attempted save (possibley throwing the form error (dataerr) 2169) in the process)... I'd suggest disallowing the "x" and use your own custom close button.

The technique could be something like
[ul][li]hit your custom close button, where you trigger the before update, by doing an explicit save[/li]
[li]the before update will then return a value (exception/error or a variable), for which to test in the "close button click".[/li][/ul]

I think there are some versions of solving such posted around here. Here is one that seems to have some of these elements, having a couple of suggestions, and perhaps an interesting discussion thread702-866344.

Roy-Vidar

 

[ZmrAbdulla]

I was just trying your code...
Ofcourse it is not a working one..It was throwing many errors..
See the sequence here with added lines..
[tt]
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control
For Each ctl In Me.Controls
With ctl
If ctl.ControlType = acTextBox Then
If ctl.Tag = "Required" Then
If ctl.Value = "" Or IsNull(ctl.Value) Then
MsgBox "Missing data"
ctl.SetFocus
Cancel = True
Exit Sub
End If
End If
End If
End With
Next
End Sub
[/tt]
hope this helps

________________________________________________________________________
Zameer Abdulla
Visit Me
A sweater is usually put on a child when the parent feels chilly.

 

[AmigoMobility]

Thanks Roy and Zameer.
Zameer, I appreciate your efforts but unfortunately your code identifies that a field has been left blank but lets the form go ahead and close.

Roy, I appreciate you pointing to something to read. I'm going to read it again and see if I can come up with an alternative.

Thanks again,
Shane