How to make one combo box required based on selection in another combo box

**Etoimos** · 04-13-2020, 12:22 PM

Originally Posted by Gicu

If you don't care which one they choose you could check for the Len(NZ(SBIRSWorkstation1,"") & Nz(SBIRSWorkstation2,"") & ......NZ(SBIRSWorkstation5,"")>0 'at least one was entered.

Cheers,
Vlad

Adding that code after the AND throws a syntax error. I think it is missing a close parentheses. I added one right after the last parentheses and that got rid of the syntax error. I also added in workstations 3 and 4 using the format laid out for the other ones, but the validation did not work. I'm able to submit the form without any of the workstation names being selected.

**Gicu** · 04-13-2020, 03:57 PM

Can you please show the row source for the workstation combos?

**Etoimos** · 04-14-2020, 06:51 AM

Originally Posted by Gicu

Can you please show the row source for the workstation combos?

Here you go Vlad:

Code:

SELECT Operators.Operator_Name, Operators.Operator_Name FROM Operators WHERE ((Not (Operators.[SBIRS_Mission_Cert]) Is Null)) ORDER BY Operators.Operator_Name;

**Etoimos** · 04-14-2020, 07:14 AM

Also, is it considered bad form/coding to have a lot of code in the Before Update? Ultimately I'd like to have all of the check boxes and fields under both the BOA and SBIRS sections of my RunSheet form to become required if either of them had Nominal or Off Nominal selected for their Run Result, but that would be over 25 things that would have to accounted for in code.

**Gicu** · 04-14-2020, 11:34 AM

Can you try this:

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)


If Me.BOARunResults = "Nominal" Or Me.BOARunResults = "Not Nominal" Then
    If Nz(Me.BOAOperator, "") = "" Then
        MsgBox "Please enter data in 'BOAOperator'.", vbInformation, "Atention!"
        Cancel = True
        Me.BOAOperator.SetFocus
        Exit Sub
    End If
    If Len(Nz(Me.SBIRSWorkstation1, "") & Nz(Me.SBIRSWorkstation2, "") & Nz(Me.SBIRSWorkstation3, "") & _
        Nz(Me.SBIRSWorkstation4, "") & Nz(Me.SBIRSWorkstation5, "")) = 0 Then
        
        MsgBox "Please enter at least one workstation data in 'Workstation'.", vbInformation, "Atention!"
        Cancel = True
        Me.SBIRSWorkstation1.SetFocus
        Exit Sub
    End If
    
    'keep adding validation rules in here
    
End If


End Sub

Your own needs will dictate how many controls you need in the validation code, just try to keep it simple and not confusing for the user, if a control fails the validation mention it in the message box then set the focus on it and exit the sub.

Cheers,

**Etoimos** · 04-14-2020, 12:26 PM

Thanks Vlad, that appears to work pefectly. The tip about setting the focus is nice as well. These is a second popup after the Attention! message that the code is giving. This second popup is labeled as "Microsoft Access" "You can't go to the specified record." Is that last part coming up because of the focus part or something else? I'd like to get rid of it since it is just another think they have to click on and get confused by.

**Gicu** · 04-14-2020, 01:27 PM

That is caused by the macro you use to "submit" the record. I replaced the embedded macro with this code:

Code:

Private Sub SubmitRSButton_Click()
On Error GoTo Err_Handler


Me.Dirty = False


If MsgBox("Record has been submitted! Do you want to go to a new record?", vbYesNo, "New record?") = vbYes Then
DoCmd.GoToRecord , , acNewRec
End If
Exit_Here:
    Exit Sub


Err_Handler:
    If Err.Number = 3021 Then
    'ignore or message
Else
    MsgBox Err.Number & " " & Err.Description
End If


Resume Exit_Here
End Sub

Please note that you have a whole bunch of unbound controls that will still be populated when you go to a new record, you will need to empty them.

Cheers,

**Etoimos** · 04-15-2020, 07:02 AM

Thanks Vlad, that worked a treat. I had already taken care of those unbound control on my local copy. I can't thank you enough for all the help with this form.

**Etoimos** · 04-15-2020, 07:47 AM

Actually, I just noticed an issue with the SBIRS Workstation check. It is not checking to see if SBIRSRunResults equals Nominal or Off Nominal. I tried using the that portion of the Operator validation code and putting it in front of the Len code (with an And between them), but that did not work.

**Gicu** · 04-15-2020, 09:27 AM

Can you please post the code you have in the BeforeUpdate?
Cheers,

**Etoimos** · 04-15-2020, 09:56 AM

It is long now, so I have color code it here on the forums to make it easier to group. The blue code has to do with my BOA stuff and it is all working fine. The black is related to SBIRS and is working fine, the red is also related to SBIRS and it is the portion that I need to be triggered by the SBIRS Nominal or Off Nominal combo box. The black after the red text is the submit button you made and it is working fine as well.

Code:

Option Compare Database
Private Sub EventCombo_AfterUpdate()
ScenarioCombo.Requery
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If (Me.BOARunResults = "Nominal" Or Me.BOARunResults = "Off Nominal") And IsNull(Me.BOAOperator) Then
MsgBox "Please select a BOA Operator", vbInformation, "Atention!"
        Cancel = True
        Exit Sub
    End If
    
    If (Me.BOARunResults = "Nominal" Or Me.BOARunResults = "Off Nominal") And IsNull(Me.BOATracksExpected) Then
MsgBox "Please enter the number of Tracks Expected for BOA", vbInformation, "Atention!"
        Cancel = True
        Exit Sub
    End If

If (Me.BOARunResults = "Nominal" Or Me.BOARunResults = "Off Nominal") And IsNull(Me.BOATracksReceived) Then
MsgBox "Please enter the number of Tracks Received for BOA", vbInformation, "Atention!"
        Cancel = True
        Exit Sub
    End If

If (Me.BOARunResults = "Nominal" Or Me.BOARunResults = "Off Nominal") And IsNull(Me.BOATracksProcessed) Then
MsgBox "Please enter the number of Tracks Processed by BOA", vbInformation, "Atention!"
        Cancel = True
        Exit Sub
    End If

If (Me.BOARunResults = "Nominal" Or Me.BOARunResults = "Off Nominal") And IsNull(Me.BOATracksReleased) Then
MsgBox "Please enter the number of Tracks Released by BOA", vbInformation, "Atention!"
        Cancel = True
        Exit Sub
    End If

If (Me.BOARunResults = "Nominal" Or Me.BOARunResults = "Off Nominal") And IsNull(Me.BOAComms) Then
MsgBox "Please select the comms type that was used for BOA", vbInformation, "Atention!"
        Cancel = True
        Exit Sub
    End If
        
    If (Me.SBIRSRunResults = "Nominal" Or Me.SBIRSRunResults = "Off Nominal") And IsNull(Me.SBIRSSIMOperator) Then
MsgBox "Please select a SBIRS SIM Controller", vbInformation, "Atention!"
        Cancel = True
        Exit Sub
    End If
        
        If (Me.SBIRSRunResults = "Nominal" Or Me.SBIRSRunResults = "Off Nominal") And IsNull(Me.SBIRSTracksExpected) Then
MsgBox "Please enter the number of Tracks Expected for SBIRS", vbInformation, "Atention!"
        Cancel = True
        Exit Sub
    End If

        If (Me.SBIRSRunResults = "Nominal" Or Me.SBIRSRunResults = "Off Nominal") And IsNull(Me.SBIRSTracksReceived) Then
MsgBox "Please enter the number of Tracks Received for SBIRS", vbInformation, "Atention!"
        Cancel = True
        Exit Sub
    End If

        If (Me.SBIRSRunResults = "Nominal" Or Me.SBIRSRunResults = "Off Nominal") And IsNull(Me.SBIRSTracksProcessed) Then
MsgBox "Please enter the number of Tracks Processed for SBIRS", vbInformation, "Atention!"
        Cancel = True
        Exit Sub
    End If

        If (Me.SBIRSRunResults = "Nominal" Or Me.SBIRSRunResults = "Off Nominal") And IsNull(Me.SBIRSTracksReleased) Then
MsgBox "Please enter the number of Tracks Released for SBIRS", vbInformation, "Atention!"
        Cancel = True
        Exit Sub
    End If
    
     If (Me.SBIRSRunResults = "Nominal" Or Me.SBIRSRunResults = "Off Nominal") And IsNull(Me.SBIRSUnscripted) Then
MsgBox "Please enter the number of Unscripted Boosters for SBIRS", vbInformation, "Atention!"
        Cancel = True
        Exit Sub
    End If

        If (Me.SBIRSRunResults = "Nominal" Or Me.SBIRSRunResults = "Off Nominal") And IsNull(Me.SBIRSComms) Then
MsgBox "Please select the comms type that was used for SBIRS", vbInformation, "Atention!"
        Cancel = True
        Exit Sub
    End If    
        
        If Len(Nz(Me.SBIRSWorkstation1, "") & Nz(Me.SBIRSWorkstation2, "") & Nz(Me.SBIRSWorkstation3, "") & _
        Nz(Me.SBIRSWorkstation4, "") & Nz(Me.SBIRSWorkstation5, "")) = 0 Then
        
        MsgBox "Please select an Operator for the SBIRS Workstation that was used", vbInformation, "Atention!"
        Cancel = True
        Me.SBIRSWorkstation1.SetFocus
        Exit Sub
    End If
    
End Sub
Private Sub SubmitRSButton_Click()
On Error GoTo Err_Handler

Me.Dirty = False

If MsgBox("Record has been submitted! Do you want to go to a new record?", vbYesNo, "New record?") = vbYes Then
DoCmd.GoToRecord , , acNewRec
End If
Exit_Here:
    Exit Sub

Err_Handler:
    If Err.Number = 3021 Then
    'ignore or message
Else
    MsgBox Err.Number & " " & Err.Description
End If

Resume Exit_Here
End Sub

**pbaldy** · 04-15-2020, 10:46 AM

You should be able to combine the two tests:

If (Me.SBIRSRunResults = "Nominal" Or Me.SBIRSRunResults = "Off Nominal") And Len(...) = 0 Then

**Gicu** · 04-15-2020, 11:08 AM

Why don't you follow the pattern we created for BOA... and test for the Results and inside of that If nest your other tests:
If If (Me.SBIRSRunResults = "Nominal" Or Me.SBIRSRunResults = "Off Nominal") then

End if
Makes it easier to follow in my opinion.

Cheers,

**Etoimos** · 04-15-2020, 11:37 AM

Originally Posted by pbaldy

You should be able to combine the two tests:

If (Me.SBIRSRunResults = "Nominal" Or Me.SBIRSRunResults = "Off Nominal") And Len(...) = 0 Then

That is what I tried to do before posting up here (see my post #54 above), but for some reason it did not work. I just used your code and added in all the Len stuff and it is working fine now. My not sure what the difference was between the two.

Originally Posted by Gicu

Why don't you follow the pattern we created for BOA... and test for the Results and inside of that If nest your other tests:
If If (Me.SBIRSRunResults = "Nominal" Or Me.SBIRSRunResults = "Off Nominal") then

End if
Makes it easier to follow in my opinion.

Cheers,

I'm not 100% sure what you are saying here. Do you mean I should have put all those extra If checks inside the initial one like this:

Code:

If If (Me.BOARunResults = "Nominal" Or Me.BOARunResults = "Off Nominal") And IsNull(Me.BOAOperator) Then
MsgBox "Please select a BOA Operator", vbInformation, "Atention!"
        Cancel = True
           
    If (Me.BOARunResults = "Nominal" Or Me.BOARunResults = "Off Nominal") And IsNull(Me.BOATracksExpected) Then
MsgBox "Please enter the number of Tracks Expected for BOA", vbInformation, "Atention!"
        Cancel = True
       
If (Me.BOARunResults = "Nominal" Or Me.BOARunResults = "Off Nominal") And IsNull(Me.BOATracksReceived) Then
MsgBox "Please enter the number of Tracks Received for BOA", vbInformation, "Atention!"
        Cancel = True
        
If (Me.BOARunResults = "Nominal" Or Me.BOARunResults = "Off Nominal") And IsNull(Me.BOATracksProcessed) Then
MsgBox "Please enter the number of Tracks Processed by BOA", vbInformation, "Atention!"
        Cancel = True
        
If (Me.BOARunResults = "Nominal" Or Me.BOARunResults = "Off Nominal") And IsNull(Me.BOATracksReleased) Then
MsgBox "Please enter the number of Tracks Released by BOA", vbInformation, "Atention!"
        Cancel = True
        
If (Me.BOARunResults = "Nominal" Or Me.BOARunResults = "Off Nominal") And IsNull(Me.BOAComms) Then
MsgBox "Please select the comms type that was used for BOA", vbInformation, "Atention!"
        Cancel = True
        Exit Sub
    End If

I'm not sure about the double If If at the start of that?

**pbaldy** · 04-15-2020, 11:46 AM

Post 54 didn't show the actual code so hard to say what might have been wrong. FYI I agree with where Vlad is headed but I'll let him elaborate.

How to make one combo box required based on selection in another combo box

Thread Tools

Similar Threads

Fill one combo box with multiple lines based on the selection of another combo box

Combo boxes and saving information based off the combo box selection

Choose values from combo box based from selection made on previous combo box

Access 2010 Form Combo Box results based on selection in another Combo Box

Populate a combo box based on selection in another combo box (Access 2010)

Posting Permissions