Link to home
Start Free TrialLog in
Avatar of TomBock2004
TomBock2004

asked on

Need to include subform fields in "For Each ctl In Me.Controls"

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
- Tab1 and Tab2 have subforms; each subform will have a single required field (control); Tab0 does not have a subform

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

Question/Problem:  I need to modify the function (or come up w/ additional code) that will allow me to also highlight a field that resides in a subform.   Does anyone know how to include the highlighting of required controls in a subform?

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  
******************************************  
Avatar of Billystyx
Billystyx

You can add much the same as above to also check the subform with
subformname.controls, where subformname is the name of your subform

Billystyx
Is this what you are looking for?
Avatar of peter57r
Hi TomBock2004,

Is all this using unbound forms?

If not, then it sounds to me that what you are asking for is technically impossible.
You cannot move between main form and subforms without saving the data in the form you are leaving; so you could not get to the subform if all your main form required fields were not completed.


Pete
But if he just needs an additional function on the beforeupdate event of the subform - just put the same code there. (I doubt that's what Tom is looking for though). It would be an idea to convert the sub to a function and just call from every form it is needed in .

Billystyx
Avatar of TomBock2004

ASKER

Billystyx:

I have already tried to used the same exact code in each subform.   However, I didn't change the "Me.Controls" to "NameOfSubform.Controls"...  thought that if code is in the subform itself than "Me" is the appropriate reference.

So, are you suggesting the following in Mainform:

Change...
For Each ctl In Me.Controls

to...
For Each ctl In Me.Controls And NameOfSubform.Controls

If yes, the syntax is probably incorrect?   Or do you suggested something like this for just the subform?

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

Pete:
All forms are bound to their tables.   However, in one of Shane's messages, he recommended to change the field properties in the tables (Required = no; Allow-zero-length = yes).   Since the popping up the error message is based on the "tag value", would it then be possible to move from main form to subform?

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

tom


Billy...

here's what I need... let's say the following data entry occurs

- user pays attention and adds data to all required controls on either page
- user however misses to select data (combo box) from required field in subform
- before can create new record, the tagged combo box on either of the 2 subforms should be highlighted

... basically, I just want to make sure that all fields get filled.

Tom
no, sorry that was a separate idea, you are correct you use Me, not the name of the subform, if you are placing the subs inside each form.

>All forms are bound to their tables.   However, in one of Shane's messages, he recommended to change the field properties in the tables (Required = no; >Allow-zero-length = yes).   Since the popping up the error message is based on the "tag value", would it then be possible to move from main form to >subform?


Yes but Access will automatically save the main form record as soon as you move to the subform (and vice versa).

Pete
And so any code in your beforeupdate event procedure will run as you save the record.

Pete
Hmh, are you "sticking" w/ your original post... this is technically impossible?

tom
TomBock2004,
Using bound forms/subforms it is not possible to move the cursor from the main form to the subform or from the subform to the main form without saving the record you are moving from.  So The idea of seeing which 'required' fields are not filled on both a main form and a subform BEFORE saving at least one of the records is impossible.

This situation, if it cannot be resolved by modifying requirements, is one reason why you might use unbound forms (which is why my first resposne asked that question).

Pete
I have used an unbound form once... that one included DLookups for every single field... it was a "pain to maintain".... maybe, I'm missing something in respect as to how I can use an unbound form most efficiently, but I doubt that I would wanna go that experience again.

Thanks for the comments... I'll leave this question open for a bit longer...

Tom
Tom,

If you wish to validate the subform, you can also put similar code in the subform's BeforeUpdate event. You would also need to include a line in the main form's BeforeUpdate to check that a record has been created for that main form record. Note though that placing the code in the subform's BeforeUpdate will cause the function to fire when you try and move off the subform.
So, I would place the same exact code (including the declaration of the global variable "'lngTabPage ") in the subform's BeforeUpdate function?

... one more thing though... what if a user never enters the subform?    I simply want to ensure that every user will create a complete record.... if I wouldn't "remind" them to enter values in the subform, I'm afraid that they sometimes may not even enter it and select data.

If the latter is no problem and a "single line" (in mainform) will fire the subform's "highlighting of missed combos", what's the exact syntax of the single line?

tom

ASKER CERTIFIED SOLUTION
Avatar of shanesuebsahakarn
shanesuebsahakarn
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Shane:

The tables are linked via junction table... is it sufficient to include the FK of the Junction table in order to see if a records was created in the subform.    Essentially, mainform data is stored in table1; while subform data is stored in table2.   There a M:M relationship between table1 & table2... again, they're joined via junction table.

If yes, should I expect see a value in "DCount"?   I put a breakpoint after DCount and it didn't show any value?

Tom
If creating a record in the subform creates a record in the junction table then yes, you can use the junction table in the dcount (the dcount must lookup from the junction table and not the subform's table in this case). If a record is created, the DCount will evaluate to a non-zero value.
This doesn't seem to work out for me...

I'm closing this for right now... will review it at some later point in time again.

Thanks for your help, Shane.

Tom