4 Unbound controls require data - validation event triggers without command button

[Rx_]

There are 4 unbound dropdown list box that will have a default value of empty string.
All 4 must have values entered (data is required).

Once all 4 are required - the bottom 5 text box will become enabled (optional data).

AVOID: Forcing the user to click on a Validation Button

Some users type then use the mouse to select the next.
Other users type to select then tab.

The trick is: After filling in the 4th listbox and validating all 4 have values, the event must trigger code to enable the 5 optional text boxes below.

This provides a seamless data entry environment.

I have been doing this in other places, but the transisition after the last required field is complete gets ugly. Would appreciate suggestions.

If Trim(cmbBoxname1 & "") = vbNullString ' check all 4
If it is the 4th one, the event seems not to happen quick enough for the bottom 5 text boxes to be enabled so the user can continue with data entry.

 

[James Deckert]

do you have your check for all four drop downs only in the fourth drop down in the after update event? Maybe you could show your code pertaining to this question.

 

[KenHigg]

To start with I would do one local sub proceedure that gets called on each of the combo boxes on change events which enables the other controls if the conditions are true...

 

[Rx_]

Will create a function that gets called in the Change Event of each.
It might need to be True or False in case someone deleted a value too.
I may have tried this a couple of years ago and was hesitant that the event didn't fire in time to un-lock the 5 below for the Tab order.
Will do that now and find out!

 

[KenHigg]

Picking the best event can be tricky sometimes. I may even throw in a DoEvents...

I like the Trim(cmbBoxname1 & "") trick and use it sometimes as well...

 

[Rx_]

Didn't work out too well.
When using the mouse to pick from the dropdown, it works out well.
Using the Tab Key - it didn't work at all.
On the last dropdown, I type a partial name, something on the list is highlighted.... use tab. Something is amiss.

Interesting when using TAB, the debug.print fires for the top 3 but not for the 4th. No event.
The Mouse selection, works great.

Function FourComboHaveValues() As Boolean
' 4 Combo boxes are required
On Error GoTo err_trap
FourComboHaveValues = True ' False until one is missing data
    If Trim(Me.cboArea & "") = vbNullString Then
        FourComboHaveValues = False
        Exit Function
    End If
        If Trim(Me.cboClassification & "") = vbNullString Then
        FourComboHaveValues = False
        Exit Function
    End If
    If Trim(Me.cboType & "") = vbNullString Then
        FourComboHaveValues = False
        Exit Function
    End If
    If Trim(Me.cboFormation & "") = vbNullString Then
        FourComboHaveValues = False
        Exit Function
    End If
Exit Function
err_trap:
Debug.Print "Function FourComboHavValues " & Err.Description
End Function
Sub EnableDisable5TextBoxes(ValidationValue As Boolean)
    'Code to enable or disable 5 text box and the Save Record button
    Debug.Print "5 text box enable is " & ValidationValue
    Me.txtAPI.Enabled = ValidationValue
    Me.txtAPI.Locked = Not ValidationValue
    Me.txtPermitFile.Enabled = ValidationValue
    Me.txtPermitFile.Locked = Not ValidationValue
    Me.txtEPAUIC.Enabled = ValidationValue
    Me.txtEPAUIC.Locked = Not ValidationValue
    Me.txtStateUIC.Enabled = ValidationValue
    Me.txtStateUIC.Locked = Not ValidationValue
    Me.txtFacility.Enabled = ValidationValue
    Me.txtFacility.Locked = Not ValidationValue
End Sub
Private Sub cboArea_Change()
    EnableDisable5TextBoxes FourComboHaveValues()
End Sub
Private Sub cboClassification_Change()
    EnableDisable5TextBoxes FourComboHaveValues()
End Sub
Private Sub cboFormation_Change()
        EnableDisable5TextBoxes FourComboHaveValues()
End Sub
Private Sub cboType_Change()
    EnableDisable5TextBoxes FourComboHaveValues()
End Sub

 

[KenHigg]

Play with the other events one at a time calling the same prc

 

[Rx_]

I have commented it out for now, trying to get a working proof of concept for early AM tomorrow.
I will put the code in the Save Record button and use the function to validate that all 4 are entered.

2nd part of question: if they are entered, the Save Record button will validate what is on the attachment. For example: API No. if entered has 9 digits.
The right-hand side is a SUB Form.
The * indicate Required. Also, the Section can only be a value of 1 to 36.
B.T.W. the Right Hand Side is data from a remote Oracle DB. They don't check for validation in the fields.

Question: What is the VBA format to call from my form to cross into the Subform to reach a text box value in the subform?

 

[The_Doc_Man]

I always use the LostFocus event rather than the CHANGE event for my transitions, and I store the current value in the GotFocus event for later comparison during LostFocus in order to detect changes. That way, TAB and Click (another box) make no difference.

If the content changed between GotFocus and LostFocus, a change occurred, so your code that was in the Change event could just be moved. But then, a question comes to mind: Are you using the Control's CHANGE event or the Form's CHANGE event? The form event will fire a lot more often the control's CHANGE event.

This approach also gives you the chance to do something simpleminded to decide when you are ready to do something else. I always use a "GotX" or "HaveX" flag for each unbound combo to let my code remember whether I have selected something. When "HaveX AND HaveY AND HaveZ AND HaveW" comes out true, I can diddle with other things that can't happen until my selections are made. I've never had a speed issue using LostFocus.

Where I have had some trouble is when my code to enable the OTHER things on the form is clunky, it doesn't matter how fast the LostFocus code runs. But you said it is OK for Mouse-driven, just not for TAB driven. That is odd because I would think that ALL events fire at the same rate (i.e. it's just a dispatched jump regardless of the event), it is what ELSE is happening at the time that slows me down.

 

[Rx_]

Access 2010 with SP.
Addes extra DoEvents around all of the events and changes to no avail.

I will probably look further into that. For tomorrow's proof of concept, there are a couple of hours for me to build the validation code in the Save button. The code above for validating the 4 combo worked great. The customer will probably ask for a half dozen new things tomorrow. LOL

You brought up some very good points. It will probably take a while just to test it out. Thanks!