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).SetFocu s
MsgBox "You must complete the yellow highlighted field!", vbCritical, "Required Data Missing"
Else
lngTabPage = Me!RRISTabControl
End If
End Sub
************************** ********** ******
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).SetFocu
MsgBox "You must complete the yellow highlighted field!", vbCritical, "Required Data Missing"
Else
lngTabPage = Me!RRISTabControl
End If
End Sub
**************************
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
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
Billystyx
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
I have already tried to used the same exact code in each subform. However, I didn't change the "Me.Controls" to "NameOfSubform.Controls"..
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
ASKER
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
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
Pete
ASKER
Hmh, are you "sticking" w/ your original post... this is technically impossible?
tom
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
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
ASKER
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
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.
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.
ASKER
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
... 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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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.
ASKER
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
I'm closing this for right now... will review it at some later point in time again.
Thanks for your help, Shane.
Tom
subformname.controls, where subformname is the name of your subform
Billystyx
Is this what you are looking for?