Need additional help w/ "For Each ctl In Me.Controls" function

A few days ago, shanesuebsahakarn helped me to come up w/ a great "validation function".

Here's what I have:
- Main form w/ tab control
- Tab control has 3 tabs
- Tab0 and Tab1 have required controls (both textfield and combos); Tab2 doesn't have any required controls at this time

Here's what the function does:
- If I do NOT complete all required controls on e.g. tab0 and click on tab1, all required controls on Tab0 are highlighted in yellow; moreover, the function sets focus to the "1st invalid" (no data) control vs. to the "1st control on page" (whether valid or invalid)
- the same process for tab1... if I completed everything on tab0 and then move to tab1 but forget to complete all required controls on tab1, I get the error message when clicking on tab2

Moreover, I cannot create a new record until all required controls have received a value.  That in itself is a HUGE PLUS!

Okay, here's the small problem:
=====================
Strangely, the "timing of the error message" as well as the "sending back to tab w/ errors" does not always work consistently the same.   Let me provide some scenarios that describe the inconsistent behavior.  

I have run the record creation and the "error-making" over and over again... I couldn't say though and it always works "consistently incosistent".  Sometimes it works as expected... other times it doesn't.  

Okay, here's what I have noticed thus far:

Scenario 1:
- I create a new record
- purposely, I leave 1st required control empty but complete the rest on tab0
- click on tab1
- I get the error message and I'm sent back to tab0 and 1st required control has focus
- ... that's a perfect scenario

Scenario 2:
- I repeat scenario 1 (but now leave multiple required controls empty on tab0) ....
- at this time, I should have been sent back to tab0 but I'm not
- instead, I can go to tab1 and complete all required controls (or leave some blank)
- then I click on tab2
- now I'm being sent back to tab0
- ... again, record completion of scenario1 and scenario2 is identical, the error message activation is differnt

Scenario 3:
- I repeat scenario 1 a 3rd time....
- now I don't even get the error message when clicking on tab2.
- Instead I can leave all required fields empty and manquever freely from tab to tab... and I don't get any errror messages whatsover
- Luckily, however, the error message eventually kicks in when attempting to create another record


Now, maybe I'm trying to make this "too perfect"... however, I'm wondering if there is something minor that can be done that ensures consistent error handling, which is:
- I forget some required control on a tab and I cannot select another tab until I fixed the control on the current tab


++++++++++++++++++++++++++

So, my questions are:

1. Does anyone know of an easy solution?

2. Also -- and this goes beyond everything listed previously -- how can I include required fields in a subform as well?

++++++++++++++++++++++++++


Thanks,
Tom





******************************************

Option Compare Database
Option Explicit
Dim lngTabPage As Long

******************************************
Private Sub Form_BeforeUpdate(Cancel As Integer)
   
    Dim ctl As Control
    Dim NotComplete As Boolean
    Dim strControlName As String
    'The global variable "lngTabPage" is declared at the top of the module
    'lngTabPage keeps track of the required controls per tab (tabs 0,1,2 have values: R0, R1, R2)

    'Keeps track of the "next invalid" control (unselected/no data)
    strControlName = ""
   
    NotComplete = False
    For Each ctl In Me.Controls
       If ctl.Tag = "R" & lngTabPage Then
          If (ctl.Value & "") = "" Then
             ctl.BackColor = vbYellow
             NotComplete = True
             If strControlName = "" Then strControlName = ctl.Name
           End If
        End If
    Next
   
    If NotComplete Then
       Cancel = True
       Me!RRISTabControl = lngTabPage
       Me(strControlName).SetFocus
       MsgBox "You must complete the yellow highlighted field!", vbCritical, "Required Data Missing"
    Else
       lngTabPage = Me!RRISTabControl
    End If
End Sub  
******************************************  
TomBock2004Asked:
Who is Participating?
 
shanesuebsahakarnConnect With a Mentor Commented:
Hmm...ok, try this in the OnChange instead:

On Error Resume Next
Me.Dirty=False
0
 
TomBock2004Author Commented:
BTW, to differentiate between the tabs, Shane suggested to tag each of them differently:

Tab0: TagValue = R0
Tab1: TagValue = R1
Tab2: TagValue = R2
0
 
shanesuebsahakarnCommented:
Tom, in my original suggestion for the BeforeUpdate code, I suggested that you might need this line:
Me.Dirty=False

in the tab control's OnChange event - did you include that?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
TomBock2004Author Commented:
Oops, I didn't... is this the only line that goes in the tab's OnChange event?

tom
0
 
shanesuebsahakarnCommented:
Yep, that should be the only line you need.
0
 
TomBock2004Author Commented:
Okay, I just place that single line into the OnChange event.

Now, once I new record is created, and click on Tab1 (after having left some controls empty) the following error pops up:

Run-Time Error 2115: The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Access from saving data in the field.

BTW, I have not changed the field property in the table... they are still set to "Required" & "No zero-length characters".  
The "Response = acDataErrContinue" prevents me from getting the default Access message before the customized message.    

Is the error related to the field property in the table?


Private Sub Form_Error(DataErr As Integer, Response As Integer)

    'Prevents the Access default message to pop up
    If DataErr = 3314 Then
        Response = acDataErrContinue
    End If
   
End Sub
0
 
shanesuebsahakarnCommented:
Possibly - try trapping for error 2115 in your Error event as well and see if it correctly validates. All we're doing is forcing the BeforeUpdate to fire.
0
 
TomBock2004Author Commented:
I added the same for 2115.... also, I just changed (temporarily) the Required to "no" and and "zero-length allowed" to "yes".

Either way (whatever the field properties are), RTE 2115 pops up.   Once I click on Debug, the Me.Dirty line is highlighted.

tom
0
 
TomBock2004Author Commented:
Okay, the error message doesn't pop up any longer...

Now, having just entered 3 or 4 test records in the last minute or so, I still see the "inconsistent" error handling is still there.   Sometimes the message box pops up immediately... other times it doesn't.

Having added the 2 lines to the OnChange event also results now in getting the customized error message sometimes twice.  

So, it appears that nothing has changed so far... well, I get the message box twice (well, I don't want that either).


Okay, gotta run now... I'll check it later on tonight.   Maybe we can continue this tomorrow morning.   If you have any additional suggestions, I certainly will try them and let you know how it goes.

Thanks, Shane.

Tom
0
 
TomBock2004Author Commented:
Shane:

I tried the 2 lines in the tab's OnChange event:
On Error Resume Next
Me.Dirty=False

The behavior is still a bit inconsistent, but it appears to be a bit more regular.    Now, however, I get the customized error twice in certain instances.  

Hmh, unless I can prevent the popping up of the message two times, I'll probably take those 2 lines out again and live w/ the odd behavior.

Any final recommendation before I close this question and award you the points?

Tom


P.S.   As mentioned on Friday, I have opened a related question that deals with required fields in a subform.   I'd be thrilled if you have any suggestions on that... here's the link:

http://www.experts-exchange.com/Databases/MS_Access/Q_21358461.html
0
 
shanesuebsahakarnCommented:
You mention that page 3 does not have any required controls - do you notice if the behaviour is to do with the movement to/from page 3 at all?
0
 
TomBock2004Author Commented:
Yes, I believe so... well, if I forget something on page 1 or page 2, clicking on page3 doesn't really kick the error message off.    

I've seen though that sevaral clicks on "new record" will eventually make the message appear.   Again, I have not been able to see a consistently wrong behaviour.   I understand that such lack of "error flow" makes the solving of the problem very difficult.

Would you be willing to look at a small sample db that I could post on the web?

Tom
0
 
shanesuebsahakarnCommented:
Sorry Tom, I can't really do that (for reasons that I've mentioned before), bearing in mind that I'm at work as well.

How many of your controls (if any) are unbound, and which pages are they on?
0
 
TomBock2004Author Commented:
Shane:

Sure, no problem.  All controls are bound.

Tom
0
 
shanesuebsahakarnCommented:
I think you'll need to try and see where the event is firing and when it isn't. Try putting a message box into the beforeupdate so that you can see when exactly the code is firing, and see if you can narrow it down to a set of circumstances.
0
 
TomBock2004Author Commented:
... same as other question... I'll get back to this question later on.

tom
0
All Courses

From novice to tech pro — start learning today.