Solved Working with Tab Controls (1 Viewer)

[Weekleyba]

I have a subform in a tab control that has some control validation.
The problem I am having is that it fires on a new record.
How do I fix this?

The validation control module looks for the * tag to check which controls to look at.
Currently only one control has a *. SubmmittalNum.

When I click on the tab control "Submittals" and it is a new record, it fires the validation control and sends me the message shown below.
I don't want to see that message yet, since it is obvious the field hasn't been populated yet.

Here's my code on the tab control. It works fine for records that are populated but not for a new record.

Private Sub TabCtProjects_Change()
   'ValidationOfControl = True when certain fields are not populated
  
   If Forms!F_Project!TabCtProjects.Value = 5 Then
    
      If ValidationOfControl(Forms!F_Project.SubmittalF.Form) = False Then
      
         With Forms!F_Project.SubmittalF.Form
          
           .DateReturned.Enabled = True
           .SubmittalNum.Enabled = True
           .Subject.Enabled = True
           .SpecificationSection.Enabled = True
           .NewSubmittal.Enabled = True
           .Resubmittal.Enabled = True
           .AdditionalInfo.Enabled = True
           .ShopDrawing.Enabled = True
           .Administrative.Enabled = True
           .AsBuilts.Enabled = True
           .Sample.Enabled = True
           .ProductData.Enabled = True
           .OM.Enabled = True
           .cmdAddSubmittal.Enabled = True
           .cmdPreviousSubmittal.Enabled = True
           .cmdNextSubmittal.Enabled = True
           .SubmittalItemSF.Enabled = True
           .NoException.Enabled = True
           .ReviewedasNoted.Enabled = True
           .ReviseandResubmit.Enabled = True
           .RejectedSubmittal.Enabled = True
           .Other.Enabled = True
           .CompleteResubmittal.Enabled = True
           .AdditionalInfoToReturn.Enabled = True
           .SubmittalComments.Enabled = True
           .cmdSubmittalTransmittal.Enabled = True
          
          End With
      End If
   End If
    
End Sub

 

[MajP]

Validation of a record gets done in the forms BeforeUpdate event not other events. Makes no sense IMO to do it on a tab control change.

 

[June7]

If Not Me.NewRecord Then

 

[Pat Hartman]

As Maj suggested, the code is in the wrong event. Checking for Me.NewRecord doesn't solve the problem.

Events of the Tab control are used for setting focus and perhaps changing what objects are visible. They are not used for validating data. As you are experiencing, you are getting an error message when you haven't even changed any data. That is a symptom of using the wrong event.

If you have some time, you might want to watch this video that explains where to do validation

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

 

[Uncle Gizmo]

Here's my code on the tab control. It works fine for records that are populated but not for a new record.

Instead of enabling each control individually you might be able to add a flag to the tag property of the controls you want to Enable and utilise some code similar to below. Might save you a bit of typing!

Private Sub TabCtProjects_Change()
'ValidationOfControl = True when certain fields are not populated

    If Forms!F_Project!TabCtProjects.Value = 5 Then
        If ValidationOfControl(Forms!F_Project.SubmittalF.Form) = False Then
        Dim Ctrl As Control
            For Each Ctrl In Me.Controls
                Select Case Ctrl.ControlType
                    Case acTextBox, acCommandButton, acComboBox ', acListBox, acOptionButton, acOptionGroup, acCheckBox, acLabel
                        If Ctrl.Tag = "X" Then
                            Ctrl.Enabled = True
                        End If
                    End Select
            Next Ctrl
        End If
    End If

End Sub     'TabCtProjects_Change

 

[Weekleyba]

Instead of enabling each control individually you might be able to add a flag to the tag property of the controls you want to Enable and utilise some code similar to below. Might save you a bit of typing!

Private Sub TabCtProjects_Change()
'ValidationOfControl = True when certain fields are not populated

    If Forms!F_Project!TabCtProjects.Value = 5 Then
        If ValidationOfControl(Forms!F_Project.SubmittalF.Form) = False Then
        Dim Ctrl As Control
            For Each Ctrl In Me.Controls
                Select Case Ctrl.ControlType
                    Case acTextBox, acCommandButton, acComboBox ', acListBox, acOptionButton, acOptionGroup, acCheckBox, acLabel
                        If Ctrl.Tag = "X" Then
                            Ctrl.Enabled = True
                        End If
                    End Select
            Next Ctrl
        End If
    End If

End Sub     'TabCtProjects_Change

Very nice! I will try this out.
Thank you.

 

[Weekleyba]

As Maj suggested, the code is in the wrong event. Checking for Me.NewRecord doesn't solve the problem.

Events of the Tab control are used for setting focus and perhaps changing what objects are visible. They are not used for validating data. As you are experiencing, you are getting an error message when you haven't even changed any data. That is a symptom of using the wrong event.

If you have some time, you might want to watch this video that explains where to do validation

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

Thanks Pat for the reference. I will definitely watch that.

Question though.
In general, when setting up control validation, (i.e. Before the record is created, you want to ensure the user inputs data into certain controls), do you normally set all other controls to enable = false?
If so, do you do that by way of VBA or at the form level?

I will dig into this tomorrow and post my solution...or more questions.

 

[June7]

Yes, BeforeUpdate event for data validation.

Is this a Single View form?

Regardless of view mode, Conditional Formatting might be a benefit for enable/disable textboxes and comboboxes.

 

[Weekleyba]

Ok. Here's what I think is a solid way to do this for a subform that is in a tab control and has three command buttons to 1. Add a new record, 2. go to previous record, and 3. go to next record.
The subform has all controls enabled = false except two. (Submittal Number and Date of Submittal) These two controls have a "*" in the tag property. The ValidationOfControl module looks to see if these two controls have data.
Let me know if this is the best way to handle this as I find myself doing this over and over for many forms.

Private Sub Form_Current()
   'Need this event to validate an existing record and enable all the "X" tagged controls.
   'ValidationOfControl = True when certain fields are not populated.  Looks for an "*" in the tagged property of the control.
   'This changes the control's enable to true if it has an "X" in the tag.

   If Not Me.NewRecord Then
      If ValidationOfControl(Forms!F_Project!SubmittalF.Form) = False Then
         Dim Ctrl As Control
            For Each Ctrl In Forms!F_Project!SubmittalF.Form.Controls
                Select Case Ctrl.ControlType
                    Case acTextBox, acCommandButton, acComboBox, acListBox, acOptionButton, acOptionGroup, acCheckBox, acLabel, acSubform, acTabCtl
                        If Ctrl.Tag = "X" Then
                            Ctrl.Enabled = True
                        End If
                    End Select
            Next Ctrl
      End If
   End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
'ValidationOfControl can be True or False.
'False - all tagged fields have data
'True - one or more tagged fields have missing data

Cancel = ValidationOfControl(Me)

End Sub

Private Sub cmdAddSubmittal_Click()
'Add a new record and sets focus to Submittal Number.
On Error Resume Next

DoCmd.GoToRecord Record:=acNewRec

Me!SubmittalNum.SetFocus

   If Me.NewRecord Then
        Dim Ctrl As Control
            For Each Ctrl In Forms!F_Project!SubmittalF.Form.Controls
                Select Case Ctrl.ControlType
                    Case acTextBox, acCommandButton, acComboBox, acListBox, acOptionButton, acOptionGroup, acCheckBox, acLabel, acSubform, acTabCtl
                        If Ctrl.Tag = "X" Then
                            Ctrl.Enabled = False
                        End If
                    End Select
            Next Ctrl
   End If
End Sub

Private Sub cmdNextSubmittal_Click()

DoCmd.GoToRecord Record:=acNext

Me!SubmittalNum.SetFocus

   If Me.NewRecord Then
        Dim Ctrl As Control
            For Each Ctrl In Forms!F_Project!SubmittalF.Form.Controls
                Select Case Ctrl.ControlType
                    Case acTextBox, acCommandButton, acComboBox, acListBox, acOptionButton, acOptionGroup, acCheckBox, acLabel, acSubform, acTabCtl
                        If Ctrl.Tag = "X" Then
                            Ctrl.Enabled = False
                        End If
                    End Select
            Next Ctrl
   End If
End Sub

Private Sub cmdPreviousSubmittal_Click()
On Error Resume Next  'Need this to prevent error message.

DoCmd.GoToRecord Record:=acPrevious

Me!SubmittalNum.SetFocus

End Sub

 

[Pat Hartman]

do you normally set all other controls to enable = false?

Absolutely not. This just annoys users and makes useless programming (and modification) tasks for you. It also makes no sense because only ONE control can ever have the focus at any one time. What does it matter if a user skips over a control because he doesn't have a value? If the field is required, you aren't going to let him save the record so there is no harm. If he gets to the final field and he still doesn't have the value, he can't save. This won't happen more than once and it might never happen if he understands the rules going in. If you want to give the user visual clues, make the background of required fields light pink or light green so he knows what is required. You can use conditional formatting to facilitate this so you don't even need any code. Just set the conditional formatting to show/hide the color depending on whether the required field is empty or not.

for a subform that is in a tab control

Whether a subform is embedded in a tab control or not has NOTHING to do with anything. You can tell that if you examine how controls on subforms are referenced from outside of the main form:

Forms!mainformname!subformCONTROLname.Form!subformcontrol

Notice that the name of the tab is NOT in the hierarchical path to the control on the subform. Also notice the absence of the actual name of the subform. You use the Name property of the subform control.

1. Access has embedded navigation controls. Why do you think you can do this better? At least copy some code that has all 5 buttons and put that in a procedure that can be used from any form.
2. Locking the controls is completely unnecessary.

When you use a RAD (Rapid Application Development) tool such as Access, it isn't cool or better practice to repeat the functionality built into the tool because you think you know better. In fact, you should sit back and let Access be Access and you do only those things that Access cannot do which revolves primarily around data validation when we are talking about update forms.

If you are properly validating data in the form's BeforeUpdate event and cancelling the event to prevent saving invalid data, there is NO added value to locking controls ahead of time. You are just writing and testing and later changing vanity code. It doesn't add even a minimal level of extra "protection".

Also, it doesn't look like your validation is correct. You seem to be calling it from the Current event --- ValidationOfControl(Forms!F_Project!SubmittalF.Form). Why?

Why is the - For Each Ctrl In Forms!F_Project!SubmittalF.Form.Controls - loop in THREE places? If you are going to lock the fields, that only needs to be done in a SINGLE procedure - the Current event. This event runs every time the form moves to a new record regardless of what caused the record pointer to advance so there is no need to run it from your new/previous events.

It is REALLY important to understand the event model of a form before you try to impose your will on Access. And if you don't understand the event model (which I can see that you don't), you should remove all your code except for the validation code in the form's BeforeUpdate event. See how the form behaves in its natural state. Then we can discuss options for making the form behave differently.

People who have programming experience in other environments make the worst Access developers because they feel that THEY need to be in control because they know how they think a form should work because of how they worked in the other environment regardless of how Access thinks a form should work. Sit back, relax. Take your hands off the keyboard. Writing code is never the first option. It should always be the last option. It is absolutely critical to ensue that bad data doesn't get saved. The place to do that is the form's BeforeUpdate event. Locking controls does not contribute anything to this effort. It just wastes your time and your employer's money (unless that is your objective).

To give you some perspective. An Access form(or report) is a class module. Behind the scenes is code written by Microsoft that controls how the form gets rendered and how recordsets get manipulated, how controls get filled, how data gets saved and WHEN. Form and control events are hooks into the class module so that you can add custom code at certain points in the process. It is important that you understand your place in the greater scheme of things. At best, you are a submodule called by the main line of the class module. Look at the names of the events. They relate to something that happened from the interface or something along the continuum of reading/writing data.

When you try to control the main line of the class module from a subroutine as you are trying to do, you are fighting with Access for control of the form. You will find this very frustrating and you will come to hate Access.

 

[Weekleyba]

Absolutely not. This just annoys users and makes useless programming (and modification) tasks for you. It also makes no sense because only ONE control can ever have the focus at any one time. What does it matter if a user skips over a control because he doesn't have a value? If the field is required, you aren't going to let him save the record so there is no harm. If he gets to the final field and he still doesn't have the value, he can't save. This won't happen more than once and it might never happen if he understands the rules going in. If you want to give the user visual clues, make the background of required fields light pink or light green so he knows what is required. You can use conditional formatting to facilitate this so you don't even need any code. Just set the conditional formatting to show/hide the color depending on whether the required field is empty or not.

Whether a subform is embedded in a tab control or not has NOTHING to do with anything. You can tell that if you examine how controls on subforms are referenced from outside of the main form:

Forms!mainformname!subformCONTROLname.Form!subformcontrol

Notice that the name of the tab is NOT in the hierarchical path to the control on the subform. Also notice the absence of the actual name of the subform. You use the Name property of the subform control.

1. Access has embedded navigation controls. Why do you think you can do this better? At least copy some code that has all 5 buttons and put that in a procedure that can be used from any form.
2. Locking the controls is completely unnecessary.

When you use a RAD (Rapid Application Development) tool such as Access, it isn't cool or better practice to repeat the functionality built into the tool because you think you know better. In fact, you should sit back and let Access be Access and you do only those things that Access cannot do which revolves primarily around data validation when we are talking about update forms.

If you are properly validating data in the form's BeforeUpdate event and cancelling the event to prevent saving invalid data, there is NO added value to locking controls ahead of time. You are just writing and testing and later changing vanity code. It doesn't add even a minimal level of extra "protection".

Also, it doesn't look like your validation is correct. You seem to be calling it from the Current event --- ValidationOfControl(Forms!F_Project!SubmittalF.Form). Why?

Why is the - For Each Ctrl In Forms!F_Project!SubmittalF.Form.Controls - loop in THREE places? If you are going to lock the fields, that only needs to be done in a SINGLE procedure - the Current event. This event runs every time the form moves to a new record regardless of what caused the record pointer to advance so there is no need to run it from your new/previous events.

It is REALLY important to understand the event model of a form before you try to impose your will on Access. And if you don't understand the event model (which I can see that you don't), you should remove all your code except for the validation code in the form's BeforeUpdate event. See how the form behaves in its natural state. Then we can discuss options for making the form behave differently.

People who have programming experience in other environments make the worst Access developers because they feel that THEY need to be in control because they know how they think a form should work because of how they worked in the other environment regardless of how Access thinks a form should work. Sit back, relax. Take your hands off the keyboard. Writing code is never the first option. It should always be the last option. It is absolutely critical to ensue that bad data doesn't get saved. The place to do that is the form's BeforeUpdate event. Locking controls does not contribute anything to this effort. It just wastes your time and your employer's money (unless that is your objective).

To give you some perspective. An Access form(or report) is a class module. Behind the scenes is code written by Microsoft that controls how the form gets rendered and how recordsets get manipulated, how controls get filled, how data gets saved and WHEN. Form and control events are hooks into the class module so that you can add custom code at certain points in the process. It is important that you understand your place in the greater scheme of things. At best, you are a submodule called by the main line of the class module. Look at the names of the events. They relate to something that happened from the interface or something along the continuum of reading/writing data.

When you try to control the main line of the class module from a subroutine as you are trying to do, you are fighting with Access for control of the form. You will find this very frustrating and you will come to hate Access.

Well....I'm still a hack, when it comes to developing databases. I'm just self taught but really appreciate all that you and others here on this forum have done by way of teaching me. Thank you for your patience and time!

I had my reasons for enable = false as the default, but I can see your point and have remove that.
I also swapped out my VBA for the embedded navigation controls. Very nice.
Just these changes really minimized my code for that subform.

Part of my reasoning for the enable = false was to prevent the user from inputting data into the subform without inputting any data on the parent form, thereby creating an orphan record. (ie. Below inputting data into DescriptionofItem before SubmittalF record is created.)
How do I prevent that?
They are linked Master/Child field. (SubmittalID)
The record source is different. Parent Form - Q_Submittal and Child Form - Q_SubmittalItems.

 

[Weekleyba]

To prevent orphans in this case, can I simply change the SubmittalID in the child form's table to Required Yes?
The seems to work for me.
When I input something into the child form only, prior to a record being created, Access throws an error at me.
The only way to get out of it is to hit Esc. So that works but as I've learned, it may not be the best way. Thoughts?

I do need to learn how to deal with deletions as right now I do them manually for all the engineers. It's rare so not a big deal.
I suppose it has something to do with Cascade Deletions but I don't this I've set this up properly to do those.

 

[Pat Hartman]

The way to solve that problem is easy. You just need to understand the event model The BeforeInsert event runs ONCE as soon as the user types a character into any "new" record. So you capture the first character typed and raise error

In the subform's BeforeInsert event, check the PK field in the parent record for a value.

If Me.Parent.txtPKID & "" = "" Then
    msgbox "Please enter a parent record first.", vbOKOnly
    Cancel = True
    Me.Undo    '' remove any data that was entered
    Me.Parent.txtFirstField.SetFocus
    Exit Sub
End If

Take a look at the list of events for a Form. Picture what interface or data event would cause them to fire. The events are not arbitrary. Every event was created by MS with a specific use in mind. There are situations where it's 6 of one, half dozen of the other when it comes to choosing which event to use but if you dig deeply, one will always turn out to be better than another. If you were to choose to use the on Dirty event for this purpose, you would need an additional If statement so you could determine if you were on a new record or an existing record. So even though you can use the Dirty event, the BeforeInsert takes less code because this is one of the problems it was intended to catch.

 

[Weekleyba]

The way to solve that problem is easy. You just need to understand the event model The BeforeInsert event runs ONCE as soon as the user types a character into any "new" record. So you capture the first character typed and raise error

In the subform's BeforeInsert event, check the PK field in the parent record for a value.

If Me.Parent.txtPKID & "" = "" Then
    msgbox "Please enter a parent record first.", vbOKOnly
    Cancel = True
    Me.Undo    '' remove any data that was entered
    Me.Parent.txtFirstField.SetFocus
    Exit Sub
End If

Take a look at the list of events for a Form. Picture what interface or data event would cause them to fire. The events are not arbitrary. Every event was created by MS with a specific use in mind. There are situations where it's 6 of one, half dozen of the other when it comes to choosing which event to use but if you dig deeply, one will always turn out to be better than another. If you were to choose to use the on Dirty event for this purpose, you would need an additional If statement so you could determine if you were on a new record or an existing record. So even though you can use the Dirty event, the BeforeInsert takes less code because this is one of the problems it was intended to catch.

I give this a try tomorrow and report back.
Thanks again!

 

[Weekleyba]

Ok some great lessons learned here for me.
Here's the nuggets:
1. Use the embedded navigation controls for New Record, Next Record, Previous Record, instead of building it in code.
2. Do not use Enable = False for controls on a new record in order to guide the user to controls you want them to provide. Just bank on the Before Update event with the appropriate code there.
3. Use the Before Insert event on a subform to ensure a user does not enter data into a subform, prior to creating a record in the parent form.

Thanks Pat! This has made a big difference in my database development procedures.

 

[Pat Hartman]

Great but to be more specific for #2, we are talking about the FORM's BeforeUpdate event.

Each control also has a beforeUpdate event. The thing to consider is that Control events do NOT run unless the control receives focus and in the case of the update events is actually updated. Therefore, if you had a validation rule that said fldB is required and you put the code into the BeforeUpdate event of fldB but the user never changed the value in that control, the event wouldn't fire. However, the FORM's BeforeUpdate event ALWAYS fires if the record is dirty. So, certain types of validation such as "required" MUST be done at the form level rather than at the field level.

Personally, I only ever use the control's BeforeUpdate event in a couple of very limited cases AND, the code always needs to be repeated in the Form level events anyway in case the control level event was never activated. So, I simply don't bother with code in the control events and instead put all validation in the form's BeforeUpdate event so I don't have to go searching everywhere for it.

 

[Weekleyba]

Great but to be more specific for #2, we are talking about the FORM's BeforeUpdate event.

Each control also has a beforeUpdate event. The thing to consider is that Control events do NOT run unless the control receives focus and in the case of the update events is actually updated. Therefore, if you had a validation rule that said fldB is required and you put the code into the BeforeUpdate event of fldB but the user never changed the value in that control, the event wouldn't fire. However, the FORM's BeforeUpdate event ALWAYS fires if the record is dirty. So, certain types of validation such as "required" MUST be done at the form level rather than at the field level.

Personally, I only ever use the control's BeforeUpdate event in a couple of very limited cases AND, the code always needs to be repeated in the Form level events anyway in case the control level event was never activated. So, I simply don't bother with code in the control events and instead put all validation in the form's BeforeUpdate event so I don't have to go searching everywhere for it.

Very good point. Thanks Pat for the clarification.