?
Solved

"For Each ctl In Me.Controls" ... also need for subform controls

Posted on 2005-03-18
28
Medium Priority
?
790 Views
Last Modified: 2006-11-17
I have multiple controls on several tabs.   I tagged all required controls with an "R"... now, when changing tabs, the function below kicks in and highlights all tagged controls on all tabs.  

I now tagged two fields in two subforms as well.  

My questions:

1. How can I also include those 2 subform fields to be highlighted as well (if left unselected)?

2. Also, how can I make it that the error message only kicks in tab by tab?  Right now, if I forget a control on tab1 and then go to tab2, I get the message (which is okay because I forgot something on tab1).   However, once I fixed all problems on tab1 and then go again to tab2, I now get the message immediately for unselected controls on tab2.   I'm kinda annoyed to get this message for tab2 before I even had a chance to enter data for tab2 controls.

Any suggestions?

Tom



*****************************
Private Sub Form_BeforeUpdate(Cancel As Integer)
   
    Dim ctl As Control
    Dim NotComplete As Boolean
   
    NotComplete = False
    For Each ctl In Me.Controls
       If ctl.Tag = "R" Then
          If (ctl.Value & "") = "" Then
             ctl.BackColor = vbYellow
             NotComplete = True
          End If
       End If
    Next
   
    If NotComplete Then
       ' Me.Main.SetFocus
       MsgBox "You did not provide data for all required fields.", vbCritical, "Missing Data"
       Cancel = True
       Exit Sub
    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
  • 16
  • 12
28 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13579833
This is going to be a little tricky. What you need is basically to have a validation routine that only looks at controls for each page. You then need to keep track of which page the user was on, and run the validation. If the validation fails, the routine needs to switch the user back to their original page.

To do this, I would give all of the required fields on page 1 a tag of "R0", all of the ones on page 2 "R1" and so forth. The tag therefore matches the page number (counting from 0). Now, your validation code needs to go into the OnChange event of the tab control.

First of all, you need to declare a global variable called something like lngTabPage, a Long. You now need some code like this in the OnChange event of the tab:

Dim ctl As Control
Dim NotComplete As Boolean

NotComplete=False
For Each ctl In Me.Controls
   If ctl.Tag="R" & lngTabPage Then
      If (ctl.Value & "") = "" Then
         ctl.BackColor = vbYellow
         NotComplete = True
       End If
    End If
Next
If NotComplete Then
   MsgBox "You didn't complete all fields!"
   Me!MyTabControl=lngTabPage
Else
   lngTabPage=Me!MyTabControl
End If

To declare the global variable (it only has to be global within the scope of this form), you can put this at the top of the module just after the Dim statements and before any functions or subs:
Dim lngTabPage As Long
0
 

Author Comment

by:TomBock2004
ID: 13579899
Shane,

... sounds very promising... just a few problems though.

I declared the global variable as you suggested on the top of the module.   Then placed the code in the OnChange of the tab control.   The only thing I changed to the control name.

Now, here's what's going on:

1. Open up main form (w/ has some records)
2. Create new record
3. Purposely, leave some R0 fields empty
4. Click on tab 2

5. Now, I get Access' default message "The field 'XYZ' cannot contain a Null value because the Required property for this field is set to True.  Enter a value in this field.
6. Now, I get the custom error message "You didn't complete all fields"
7. Then I jump to the tab0 while highlighted fields are yellow.
9. Although, I already clicked on the custom message, it still is there (2nd time?)


My follow-up questions:

a. How can I get rid of the default Access message?
b. How can I make it so I click on the customized message on once?  

Note:  Once only per "error"... the following is okay

a. I forgot multiple controls on tab0
b. I click on tab1
c. I get the error message
d. I'm sent back to tab0
e. I still forget to complete all controls
f. I click on tab1
g. I get the error message again for tab0...

... the above (a:g) is perfectly fine... currently, however, I get the message on tab1 for tab0, but then it still stays tab0 one time too many....   does that makes sense?


Thanks for your help,
Tom




********************************
Option Compare Database
Option Explicit
Dim lngTabPage As Long

...
... followed by a bunch of function....
...

******************************
Private Sub RRISTabControl_Change()
   
    Dim ctl As Control
    Dim NotComplete As Boolean
   
    NotComplete = False
    For Each ctl In Me.Controls
       If ctl.Tag = "R" & lngTabPage Then
          If (ctl.Value & "") = "" Then
             ctl.BackColor = vbYellow
             NotComplete = True
           End If
        End If
    Next
   
    If NotComplete Then
       MsgBox "You didn't complete all fields!"
       Me!RRISTabControl = lngTabPage
    Else
       lngTabPage = Me!RRISTabControl
    End If

   
End Sub
********************************
0
 

Author Comment

by:TomBock2004
ID: 13579918
Shane:

... I actually think I just fixed the issue w/ the Access default msg.   I jus put the following into the Form_Error function.   The default message is gone now...

But I still get the custom message one too many times.   I'm sure you got the answer for that.

Tom





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

    If DataErr = 3314 Then
        Response = acDataErrContinue    
    End If
   
End Sub
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13579929
Ok, first of all, to get rid of the default message, you'd really need to set the Required property of the field to No. You can trap the error in the form's Error event if you know what the error number is (I don't recall off the top of my head).

To deal with the error, we'd need another global variable:
Dim bValidate As Boolean

The reason it is firing twice is because the code is being run once when you move off the tab and once again when the code moves you back on. We need to change the code to read as follows:
    Dim ctl As Control
    Dim NotComplete As Boolean
   
    If bValidate Then
       NotComplete = False
       For Each ctl In Me.Controls
          If ctl.Tag = "R" & lngTabPage Then
             If (ctl.Value & "") = "" Then
                ctl.BackColor = vbYellow
                NotComplete = True
              End If
           End If
       Next
   
       If NotComplete Then
          MsgBox "You didn't complete all fields!"
          bValidate=False
          Me!RRISTabControl = lngTabPage
       Else
          bValidate=True
          lngTabPage = Me!RRISTabControl
       End If
    End If

I *think* that will do it.
0
 

Author Comment

by:TomBock2004
ID: 13579976
Okay,

since I figured out the work-around for the required property, I haven't changed them yet... 'doubt that this has an impact on your updated solution.

Okay, I added the 2nd global variable and overwrote the code w/ your updated function...

Now, nothing happens!    I can leave required blank and can freely move from tab to tab... the function never fires.

Isn't that odd?   Or did I miss something here?  I left the function in the tab's OnChange event.

Tom
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13579999
Whoops:

    Dim ctl As Control
    Dim NotComplete As Boolean
   
    If bValidate Then
       NotComplete = False
       For Each ctl In Me.Controls
          If ctl.Tag = "R" & lngTabPage Then
             If (ctl.Value & "") = "" Then
                ctl.BackColor = vbYellow
                NotComplete = True
              End If
           End If
       Next
   
       If NotComplete Then
          MsgBox "You didn't complete all fields!"
          bValidate=False
          Me!RRISTabControl = lngTabPage
          bValidate=True
       End If
    Else
       bValidate=True
       lngTabPage = Me!RRISTabControl
    End If

You'll also need this in the OnOpen event:
bValidate=True
0
 

Author Comment

by:TomBock2004
ID: 13580080
Shane:

Almost there I think...

- function kicks in now
- I changed the order of SetFocus w/ Msgbox (so, it's set focus to tab0 first, then I get the message)


Good news:
- the process of tab0 is perfect

Bad news:
- nothing happens on tab1 when clicking on tab2


Additional info:
- tab0 has a bunch of required controls
- tab1 has 3 required controls
- tab2 has no required controls at all

Now, once I "took care" of tab0 (via proper messaging), and I then "mess up" on tab1 and click tab2, I should go through the same process... "You forgot something on tab1..."... but that doesn't happen.   Tab1's controls are not highlighted nor do I get the error message.

Since there aren't any required controls on tab2 (well, eventually I want to include fields in a subform later on, but for right now I'm not there yet), does this cause a problem w/ this approach?

Tom


0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13580106
Odd - what if you rewrite the code slightly like this:
    Dim ctl As Control
    Dim NotComplete As Boolean
   
    If bValidate Then
       NotComplete = False
       For Each ctl In Me.Controls
          If ctl.Tag = "R" & lngTabPage Then
             If (ctl.Value & "") = "" Then
                ctl.BackColor = vbYellow
                NotComplete = True
              End If
           End If
       Next
   
       If NotComplete Then
          MsgBox "You didn't complete all fields!"
          bValidate=False
          Me!RRISTabControl = lngTabPage
       End If
    Else
       lngTabPage = Me!RRISTabControl
    End If
    bValidate=True

Have you confirmed that all of the controls on tab1 have a tag of R1?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13580113
Also, when you changed the code, did you put:
Me!RRISTabControl=lngTabPage
bValidate=False
MsgBox ....
bValidate=True
?

If you did, try:
bValidate=False
Me!RRISTabControl=lngTabPage
MsgBox ....
bValidate=True
0
 

Author Comment

by:TomBock2004
ID: 13580121
Yes, all 3 controls are tagged R1... and no, the new code doesn't work either.  8(
0
 

Author Comment

by:TomBock2004
ID: 13580144
No change... just to make sure... this is what I currently have:

    If bValidate Then
       NotComplete = False
       For Each ctl In Me.Controls
          If ctl.Tag = "R" & lngTabPage Then
             If (ctl.Value & "") = "" Then
                ctl.BackColor = vbYellow
                NotComplete = True
              End If
           End If
       Next
   
       If NotComplete Then
            bValidate = False
            Me!RRISTabControl = lngTabPage
            MsgBox "You didn't complete all fields!"
            bValidate = True
       End If
    Else
       lngTabPage = Me!RRISTabControl
    End If
    bValidate = True
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 2000 total points
ID: 13580146
Just out of interest, try this in the form's BeforeUpdate event instead:
    Dim ctl As Control
    Dim NotComplete As Boolean
   
    NotComplete = False
    For Each ctl In Me.Controls
       If ctl.Tag = "R" & lngTabPage Then
          If (ctl.Value & "") = "" Then
             ctl.BackColor = vbYellow
             NotComplete = True
           End If
        End If
    Next
   
    If NotComplete Then
       MsgBox "You didn't complete all fields!"
       Cancel=True
       Me!RRISTabControl = lngTabPage
    Else
       lngTabPage = Me!RRISTabControl
    End If

You *might* need this in the tab control's Change event:
Me.Dirty=False
0
 

Author Comment

by:TomBock2004
ID: 13580218
Shane:

That's perfect!!!!   Thanks so much.

Wrap-up questions:

1. I thought we had to use the tab's OnChange for this process... how is it working differntly now?

2. I guess a can delete the "Dim bValidate As Boolean" & "bValidate = True" in the OnOpen function?   (I like to keep the code as clean as possible and get rid of stuff which isn't needed.    Do I need to keep the code in the OnChange function?   (The code in the BeforeUpdate isn't related to the OnChange, right?)

3.  Final teaser... sorry... right now, I set focus to the page that has errors.   By default the first control gets focus (whether is has data or not).   Is there a chance to fine-tune the function so that the 1st "incomplete control" gets the focus rather than the 1st-listed control on the tab?


Thanks so much already!!!

Tom
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13580240
1) It's working differently, but it works :)

2) Yes, you can delete bValidate. You don't need to include any of the code in the OnChange.

3) You could do it, by recording the name of the control that was invalid and then just putting a setfocus in to set the focus to that control just after the line that changes the tab page.
0
 

Author Comment

by:TomBock2004
ID: 13580247
Oops...

maybe I spoke a bit too early.   During this process, I have always tested this on a single new record.   I made errors, I fixed them, then I closed the form.

In the last few minutes, I went through the process to enter a few new records before closing the form.   Sometimes it works all the way and no matter how I navigate from tab to tab I get the proper error messages.   Other times (on a new record), I don't get any error messages on e.g. tab0 but I get some on tab1.  

What's going on?

tom
0
 

Author Comment

by:TomBock2004
ID: 13580251
I guess first things first... how do I record an invalid control?

0
 

Author Comment

by:TomBock2004
ID: 13580252
sorry for asking all these follow on questions.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13580260
To record an invalid control, you need an additional Dim:

Dim strControlName As String

Put this line as the first line of code:
strControlName=""

Then just before the line that sets the back colour, put this:
If strControlName<>"" Then strControlName=ctl.Name

Now, just after the line that changes the tab page, do this:
Me(strControlName).SetFocus
0
 

Author Comment

by:TomBock2004
ID: 13580328
Did you mean this:

    strControlName = ""
   
    NotComplete = False
    For Each ctl In Me.Controls
       If ctl.Tag = "R" & lngTabPage Then
          If strControlName <> "" Then
          strControlName = ctl.Name
          'If (ctl.Value & "") = "" Then
             ctl.BackColor = vbYellow
             NotComplete = True
           End If
        End If
    Next
   
    If NotComplete Then
       'MsgBox "You didn't complete all fields!"
       Cancel = True
       Me!RRISTabControl = lngTabPage
       Me(strControlName).SetFocus
       MsgBox "You didn't complete all fields!"
    Else
       lngTabPage = Me!RRISTabControl
    End If



... now, nothing happens again... I don't want to message to be extraordinarily long... you have helped me out already a great deal.   If this is getting too long, don't bother.... if you think it's a quick fix, then I certainly would appreciate your suggestion.

Thanks so much again,
Tom

0
 

Author Comment

by:TomBock2004
ID: 13580332
btw, some of my required fiels on tab0 are combos... not just textfields
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13580394
No, try this:

    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
       'MsgBox "You didn't complete all fields!"
       Cancel = True
       Me!RRISTabControl = lngTabPage
       Me(strControlName).SetFocus
       MsgBox "You didn't complete all fields!"
    Else
       lngTabPage = Me!RRISTabControl
    End If
0
 

Author Comment

by:TomBock2004
ID: 13580435
Oh man, now it says that "it can't find the field 'strControlName' referred to in your expression"
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13580449
That's strange....did you put it in exactly as is? strControlName should not have any quotes around it or anything.
0
 

Author Comment

by:TomBock2004
ID: 13580463
copied it straight from your message...

0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13580473
Hmm very strange - when the error arises, what's the value of strControlName?
0
 

Author Comment

by:TomBock2004
ID: 13580474
well, I must correct my last message...

yes, I copied it as is... then got some error, and I changed it to

Me!strControlName.SetFocus

which gave me the error as posted previously.

I now copied as it, but I still get the error but now it says: "it can't find the field " referred to in your expression"

Does that help?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13580484
Yes, sorry, this line:
If strControlName <> "" Then strControlName = ctl.Name

should read:
If strControlName = "" Then strControlName = ctl.Name
0
 

Author Comment

by:TomBock2004
ID: 13580507
ABSOLUTELY PERFECT!!!

Thousand thanks to you!!!

Have a great weekend,
Tom

P.S.  I'll open another message (probably on Monday) to figure out how to include fields in a subform.   Looking forward to that one.

0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

800 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