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

[Gicu]

No what I am saying is to start with the first condition then you add each individual check inside that first If statement:

If Me.BoaResults="Nominal" or Me.BoaResults="Off Nominal" Then
     'check for BOA Operator
     If IsNull(Me.BOAOperator) Then           MsgBox "Please select a BOA Operator", vbInformation, "Atention!"
           Cancel = True
     End If
     'check for BOATracksExpected
      If IsNull(Me.BOATracksExpected) Then
.........
.........

'add individual If statements for all the controls you want to validate if Nominal and Off Nominal


End If

Basically remove the AND part of your original expressions and bring those inside the bigger If statement.


Cheers,

[Etoimos]

So I tried this and it is complaining about a missing End If... which I think comes from the double If statements at the start of each section (BOA and SBIRS).

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


    If (Me.SBIRSRunResults = "Nominal" Or Me.SBIRSRunResults = "Off Nominal") Then
    If IsNull(Me.SBIRSSIMOperator) Then
MsgBox "Please select a SBIRS SIM Controller", vbInformation, "Atention!"
        Cancel = True
        Me.SBIRSSIMOperator.SetFocus
    End If
        
        If IsNull(Me.SBIRSTracksExpected) Then
MsgBox "Please enter the number of Tracks Expected for SBIRS", vbInformation, "Atention!"
        Cancel = True
        Me.SBIRSTracksExpected.SetFocus
    End If
        If IsNull(Me.SBIRSTracksReceived) Then
MsgBox "Please enter the number of Tracks Received for SBIRS", vbInformation, "Atention!"
        Cancel = True
        Me.SBIRSTracksReceived.SetFocus
    End If
        If IsNull(Me.SBIRSTracksProcessed) Then
MsgBox "Please enter the number of Tracks Processed for SBIRS", vbInformation, "Atention!"
        Cancel = True
        Me.SBIRSTracksProcessed.SetFocus
    End If
        If IsNull(Me.SBIRSTracksReleased) Then
MsgBox "Please enter the number of Tracks Released for SBIRS", vbInformation, "Atention!"
        Cancel = True
        Me.SBIRSTracksReleased.SetFocus
    End If
    
     If IsNull(Me.SBIRSUnscripted) Then
MsgBox "Please enter the number of Unscripted Boosters for SBIRS", vbInformation, "Atention!"
        Cancel = True
        Me.SBIRSUnscripted.SetFocus
    End If
        If IsNull(Me.SBIRSComms) Then
MsgBox "Please select the comms type that was used for SBIRS", vbInformation, "Atention!"
        Cancel = True
        Me.SBIRSComms.SetFocus
    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
    End If
    
End Sub

[Gicu]

Have a look at this, I have indented the statements to make it easier to read:

Private Sub Form_BeforeUpdate(Cancel As Integer)
'BOA nominal or off nominal
If (Me.BOARunResults = "Nominal" Or Me.BOARunResults = "Off Nominal") Then   'start BOA checks


    'BOA Operator    
    If IsNull(Me.BOAOperator) Then
    MsgBox "Please select a BOA Operator", vbInformation, "Atention!"
        Cancel = True
        Me.BOAOperator.SetFocus
    Exit Sub
    End If


    'BOATracksExpected
    If IsNull(Me.BOATracksExpected) Then
    MsgBox "Please enter the number of Tracks Expected for BOA", vbInformation, "Atention!"
        Cancel = True
        Me.BOATracksExpected.SetFocus
    Exit Sub
    End If


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


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


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


    'BOAComms
    If IsNull(Me.BOAComms) Then
    MsgBox "Please select the comms type that was used for BOA", vbInformation, "Atention!"
        Cancel = True
        Me.BOAComms.SetFocus
    Exit Sub
    End If
        
End IF ' end of BOA Checks






If (Me.SBIRSRunResults = "Nominal" Or Me.SBIRSRunResults = "Off Nominal") Then 'start SBIRS checks


    'SBIRSSIMOperator
    If IsNull(Me.SBIRSSIMOperator) Then
    MsgBox "Please select a SBIRS SIM Controller", vbInformation, "Atention!"
        Cancel = True
        Me.SBIRSSIMOperator.SetFocus
    Exit Sub
    End If


    'SBIRSTracksExpected   
    If IsNull(Me.SBIRSTracksExpected) Then
    MsgBox "Please enter the number of Tracks Expected for SBIRS", vbInformation, "Atention!"
        Cancel = True
        Me.SBIRSTracksExpected.SetFocus
    Exit Sub
    End If


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


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


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


    'SBIRSComms
    If IsNull(Me.SBIRSComms) Then
    MsgBox "Please select the comms type that was used for SBIRS", vbInformation, "Atention!"
        Cancel = True
        Me.SBIRSComms.SetFocus
    Exit Sub
    End If
     
    'SBIRSWorkstation   
    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 IF 'end of SBIRS checks
    
End Sub

I am also attaching the code as a text file (zip).

Cheers,

[Etoimos]

Thanks Vlad. Do the ' act as comment indicators? So everything on the same line and after the ' ​is ignored in the code execution?

[Gicu]

Yes, you got it, makes the code easier to read and follow.
Cheers,

[Gicu]

One thing I forgot to mention was that you were missing the Exit Sub in most individual checks.

 'BOATracksExpected
    If IsNull(Me.BOATracksExpected) Then
    MsgBox "Please enter the number of Tracks Expected for BOA", vbInformation, "Atention!"
        Cancel = True
        Me.BOATracksExpected.SetFocus
    Exit Sub
    End If

Unless you gather all the missing fields and build one long message at the end listing them all (which is not always very helpful and can be confusing to the user) you want to exit the validation after each SetFocus to allow the user to correct the issue.

Cheers,
Vlad

[Etoimos]

That is more good info Vlad, thanks.

[Etoimos]

Okay, another round of questions regarding this form. I would like to have one version of this form that is for data entry only (how we have designed it up to this point) and not allow the users to go to previously entered records. However, I would like to give them the ability to search for an old data set that they entered and I would like for it to be presented in the exact format of this current form.

I'm assuming that I need to create a query to pull up the old data, but do I need to recreate this form from scratch to display what the query outputs or is there a better/easier way to do it?

[Gicu]

I don't think you need a new query, you could add a Search button or combobox in the form's header and dynamically change its DataAdd property from True (like you have it now) to False which will make the form display the records (you will probably need to also on turn on the record selector and navigation buttons)
In the combos afterupdate event (the combo will display your event they want to select) use something like this

 ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ID] = " & Str(Nz(Me![cboBox], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Cheers,

[Etoimos]

I don't think you need a new query, you could add a Search button or combobox in the form's header and dynamically change its DataAdd property from True (like you have it now) to False which will make the form display the records (you will probably need to also on turn on the record selector and navigation buttons)
In the combos afterupdate event (the combo will display your event they want to select) use something like this

 ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ID] = " & Str(Nz(Me![cboBox], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Cheers,

Hi Vlad, they had me working on other things last week, so I'm just now getting around to playing with this. For my search function I need them to be able to look things up by combination of Event, Date, and Run Number. All three of those will have to be used to find the record they are looking for. I think I would like to have a button on a switchboard that opens a new form that would allow them to select those parameters and click a "Find Run Sheet" button. That button would in turn open the Run Sheet form we've been working on with the correct data displayed.

How hard would it be to do it that way?

[Gicu]

Not that hard, but remember that the search combo I mentioned in my previous post could have all those columns (turn the Column Heads property to Yes so they show in the list) or you could have a series of them "linked" together (cascading combo boxes). Further more you could hide the search control while in data add mode and only show them when they click a button that also turns off data add.

Cheers,

[Etoimos]

Not that hard, but remember that the search combo I mentioned in my previous post could have all those columns (turn the Column Heads property to Yes so they show in the list) or you could have a series of them "linked" together (cascading combo boxes). Further more you could hide the search control while in data add mode and only show them when they click a button that also turns off data add.

Cheers,

For your combined search combo, would the combo box display every Event/Date/Run record in the data base? Meaning they would have to scroll thru them all to find the one they were looking for? If that is the case, I think I have to go with three separate cascading combo boxes to allow them the to more easily select what they are looking for.

I do like the idea of having an add data mode and a search mode. Perhaps each one tied to a different button on my switchboard. The reason I really want to have the two appear separately is that I'll have both operators and managers wanting to search for past Run Sheets. Keeping the managers as far away from the data entry stuff as possible is very important. lol

[Gicu]

Yes it will (unless you edit its record source to only show a subset such as last month, last year,....) but if sorted properly should be easy to find. The three cascading combos work too as the y will indeed progressively reduce the records shown. Very easy to implement the two buttons using the DataMode argument of the OpenForm method (https://docs.microsoft.com/en-us/off...rmopendatamode).

Cheers,

[Etoimos]

Yes it will (unless you edit its record source to only show a subset such as last month, last year,....) but if sorted properly should be easy to find. The three cascading combos work too as the y will indeed progressively reduce the records shown. Very easy to implement the two buttons using the DataMode argument of the OpenForm method (https://docs.microsoft.com/en-us/off...rmopendatamode).

Cheers,

Thanks. I'll get my three combo boxes working and then mess around with the DataMode argument to get the form to open in the right mode for each switchboard button.

[Etoimos]

Alright, I've got my three combo boxes working (EventLookupCombo , DateLookupCombo , and RunLookupCombo ) and I've added a button to the new form ( SearchRunSheets ). I tried playing around with this code:

 DoCmd.OpenForm "Run Sheet", acNormal, , , acFormReadOnly, "Event=" & Me.EventLookupCombo 

I know it is missing the last two combo box checks, but that gives me a "Type Mismatch" error.