?
Solved

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

Posted on 2005-03-20
16
Medium Priority
?
556 Views
Last Modified: 2008-03-06
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  
******************************************  
0
Comment
Question by:TomBock2004
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
16 Comments
 

Author Comment

by:TomBock2004
ID: 13587301
BTW, to differentiate between the tabs, Shane suggested to tag each of them differently:

Tab0: TagValue = R0
Tab1: TagValue = R1
Tab2: TagValue = R2
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13587507
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
 

Author Comment

by:TomBock2004
ID: 13587515
Oops, I didn't... is this the only line that goes in the tab's OnChange event?

tom
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13587539
Yep, that should be the only line you need.
0
 

Author Comment

by:TomBock2004
ID: 13587549
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13587563
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
 

Author Comment

by:TomBock2004
ID: 13587580
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
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 2000 total points
ID: 13587595
Hmm...ok, try this in the OnChange instead:

On Error Resume Next
Me.Dirty=False
0
 

Author Comment

by:TomBock2004
ID: 13587620
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
 

Author Comment

by:TomBock2004
ID: 13590978
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13591123
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
 

Author Comment

by:TomBock2004
ID: 13591940
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13592192
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
 

Author Comment

by:TomBock2004
ID: 13592902
Shane:

Sure, no problem.  All controls are bound.

Tom
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13592922
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
 

Author Comment

by:TomBock2004
ID: 13606764
... same as other question... I'll get back to this question later on.

tom
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question