Help with Validation code on exit event

[thebionicredneck]

Hi guys,
I need some help with this code snippet. I am trying to perform a validation on exit of a specific tab of my form and I am calling this function in the form exit event

[FONT=Verdana][FONT=Arial][SIZE=2]Public Function fnValidateForm(Form_2 As Form) As Boolean[/SIZE][/FONT][/FONT]
[FONT=Verdana][FONT=Arial][SIZE=2]Dim ctl As Control[/SIZE][/FONT][/FONT]
[FONT=Verdana][FONT=Arial][SIZE=2]‘Dim Msg, Style, Title, Response, MyString[/SIZE][/FONT][/FONT]
[FONT=Verdana][FONT=Arial][SIZE=2]fnValidateForm = True[/SIZE][/FONT][/FONT]
[FONT=Verdana][FONT=Arial][SIZE=2]For Each ctl In Form_2.Controls[/SIZE][/FONT][/FONT]
[FONT=Verdana][FONT=Arial][SIZE=2]'value in the control is required[/SIZE][/FONT][/FONT]
[FONT=Verdana][FONT=Arial][SIZE=2]If InStr(1, ctl.Tag, "Required") > 0 Then[/SIZE][/FONT][/FONT]
[FONT=Verdana][FONT=Arial][SIZE=2]' no value entered or value is null[/SIZE][/FONT][/FONT]
[FONT=Verdana][FONT=Arial][SIZE=2]' or zero for numeric fields[/SIZE][/FONT][/FONT]
[FONT=Verdana][FONT=Arial][SIZE=2]If (IsNull(ctl.Value)) Or (Len(ctl.Value) = 0) Then[/SIZE][/FONT][/FONT]
[FONT=Verdana][FONT=Arial][SIZE=2]ctl.SetFocus[/SIZE][/FONT][/FONT]
[FONT=Verdana][FONT=Arial][SIZE=2]MsgBox "Please complete the highlighted control", vbCritical + vbOKOnly[/SIZE][/FONT][/FONT]
[FONT=Verdana][FONT=Arial][SIZE=2]Cancel = True[/SIZE][/FONT][/FONT]
[FONT=Verdana][FONT=Arial][SIZE=2]fnValidateForm = False[/SIZE][/FONT][/FONT]
 
[FONT=Verdana][FONT=Arial][SIZE=2]Exit For[/SIZE][/FONT][/FONT]
[FONT=Verdana][FONT=Arial][SIZE=2]End If[/SIZE][/FONT][/FONT]
[FONT=Verdana][FONT=Arial][SIZE=2]'Next[/SIZE][/FONT][/FONT]
[FONT=Verdana][FONT=Arial][SIZE=2]End If[/SIZE][/FONT][/FONT]
[FONT=Verdana][FONT=Arial][SIZE=2]Next[/SIZE][/FONT][/FONT]
[FONT=Verdana][FONT=Arial][SIZE=2]End Function[/SIZE][/FONT][/FONT]

The problem I have is that I would like the user to remain on the tab until they fix the error, but this code block allows the user to go to the next tab without completing the errors which defeats the entire purpose of the validation.
I have been on this for quite some time, so I just need a fresh pair of eyes to tell me what I need to change to get this working.

Thanks

 

[CJ_London]

To be clear - you are talking about the tabbed document style beloved of Access 2007 and later, not tabs on the form

Also, the code which could prevent the user leaving the form is not showing (i.e. the calling code from your exit event)

In your table definition - have you set the required property to true? This would stop someone leaving the form (although without the highlighting etc)

If you are talking about tabbed documents style, have you confirmed the On Exit event is triggered (I don't use them and can't actually see one for a form anyway - do you mean the close event?) when the user clicks on a different tab? My assumption would be it wouldn't (since you can return to it unchanged, ergo you haven't exited it), more likely the lost focus event is triggered.

Finally I would think you actually want the before update event because your data will be updated at that point - not necessarily when you exit the form

 

[thebionicredneck]

Hi CJ,

I mean tabs on a form i.e. different tabs and you can click on each tab and each tab contains a different page with different information.

I know I can set the required property to yes, but it would be much nicer to have an event that is triggered when a user tries to go from tab 1 to tab 2.

That's the code for th exit event

Private Sub Ctl2_frm_Exit(Cancel As Integer)
initalizeCommon
Call fnValidateForm(Form_2)
End Sub

Hope this makes things clearer

 

[CJ_London]

OK,, I understand.

The only problem is I can't see an exit event for the tab control or tab page - is it a case that each tab page contains a subform? If so, I would put your call in the beforeupdate event of the subform and set Cancel=true

Also, for completeness what it initialisecommon?

 

[thebionicredneck]

Thanks again for your reply.

InitializeCommon makes all my code in the Common module available to use in other parts of my code.

The subform only has two events. One for On Enter and one for On Exit, so I am stumped.

 

[CJ_London]

I was referring to the form which is used by the subform control - in design view, click on the subform control, then click on the small square box top left of the control to view properties for this form. If you can't see the proerties either right click and select properies or clcik property sheet in the design ribbon.

Use the subform before update event