Page 1 of 12 1234567891011 ... LastLast
Results 1 to 15 of 167
  1. #1
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    179

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


    I'm trying to fine tune a form and would like to have combo box BOAOperator become a required field if the selection in combo box BOARunResults equals either 'Nominal' or 'Off Nominal'. If it matters, I could make BOAOperator required and un-require it if BOARunResults equaled 'Did Not Participate'.

    I think this would be some type of validation rule, but I'm not sure how to do it.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,285
    I don't think a rule will catch it. I'd use the before update event:

    http://www.baldyweb.com/BeforeUpdate.htm

    with a test appropriate to your situation.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Bob Fitz is online now Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,444

    Post

    I would use validation code in the form's BeforeUpdate event. Something like:
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)    
        If (Me.BOARunResults = "Nominal" OR Me.BOARunResults = "Not Nominal" AND Isnull(Me.BOAOperator)  Then
            MsgBox "Please enter data in 'BOAOperator'.", vbInformation, "Atention!"
            Cancel = True
            Exit Sub
        End If 
    End Sub
    If this helped, please click the star at the bottom left of this posting and add to my reputation. Many thanks.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



  4. #4
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    179
    Quote Originally Posted by pbaldy View Post
    I don't think a rule will catch it. I'd use the before update event:

    http://www.baldyweb.com/BeforeUpdate.htm

    with a test appropriate to your situation.
    Thanks Paul, that is a good break down of it.

    Quote Originally Posted by Bob Fitz View Post
    I would use validation code in the form's BeforeUpdate event. Something like:
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)    
        If (Me.BOARunResults = "Nominal" OR Me.BOARunResults = "Not Nominal" AND Isnull(Me.BOAOperator)  Then
            MsgBox "Please enter data in 'BOAOperator'.", vbInformation, "Atention!"
            Cancel = True
            Exit Sub
        End If 
    End Sub
    Hi again Bob. It looks like you left off a ) before the Then. You were just testing me right

    I put that code in the form's BeforeUpdate, but it did not catch that BOAOperator was left blank. It just saved the data to the table anyway.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,285
    What was the code you tried? I'd also use the test from my link instead of IsNull(). That test will catch both a zero length string and Null.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    179
    Quote Originally Posted by pbaldy View Post
    What was the code you tried? I'd also use the test from my link instead of IsNull(). That test will catch both a zero length string and Null.
    I just cut and pasted Bob's code above.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,285
    Well, as you pointed out, it was missing a parentheses. It wouldn't go at the end, it would go here:

    If (Me.BOARunResults = "Nominal" OR Me.BOARunResults = "Not Nominal") AND Isnull(Me.BOAOperator) Then
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    179
    Thanks for pointing out I had the parentheses in the wrong place. However, after correcting that it is still not working for me. I can set BOARunResults to Nominal or Not Nominal and leave BOAOperator blank and I do not get the Message box saying to select an operator and the form data just gets saved to the underlying table.

    I have that code in the Before Update property for the form RunSheet. I do have a button (made by the Command Button Wizard) at the bottom of that form that I am using to submit the form to the database. Should I be putting this code in the buttons Before Update instead?

  9. #9
    Bob Fitz is online now Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,444
    Perhaps:

    If ((Me.BOARunResults = "Nominal" OR Me.BOARunResults = "Not Nominal") AND Nz(Me.BOAOperator,"")) = 0 Then
    If this helped, please click the star at the bottom left of this posting and add to my reputation. Many thanks.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



  10. #10
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    179
    Quote Originally Posted by Bob Fitz View Post
    Perhaps:

    If ((Me.BOARunResults = "Nominal" OR Me.BOARunResults = "Not Nominal") AND Nz(Me.BOAOperator,"")) = 0 Then
    Nope, that does not work either

  11. #11
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    179
    Okay, I might have found something that is related to my company laptop's security settings. I'll report back soon.

  12. #12
    Bob Fitz is online now Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,444
    Quote Originally Posted by Etoimos View Post
    Nope, that does not work either
    Sorry, I type that wrong too . Meant to type:

    f ((Me.BOARunResults = "Nominal" OR Me.BOARunResults = "Not Nominal") AND Nz(Me.BOAOperator,"")) = "" Then
    If this helped, please click the star at the bottom left of this posting and add to my reputation. Many thanks.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



  13. #13
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    179
    Alright, the security setting appears to be a red hearing. Turning it on or off does not matter.

    With this last line of code:

    Code:
    If ((Me.BOARunResults = "Nominal" OR Me.BOARunResults = "Not Nominal") AND Nz(Me.BOAOperator,"")) = 0 Then
    I get a Run-time error "13": Type Mismatch popup.

  14. #14
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    179
    Well, your updated version of your latest code still gives me a Run-time error '13' Type Mismatch popup.

  15. #15
    Bob Fitz is online now Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,444
    Quote Originally Posted by Etoimos View Post
    Alright, the security setting appears to be a red hearing. Turning it on or off does not matter.

    With this last line of code:

    Code:
    If ((Me.BOARunResults = "Nominal" OR Me.BOARunResults = "Not Nominal") AND Nz(Me.BOAOperator,"")) = 0 Then

    I get a Run-time error "13": Type Mismatch popup.
    Please see my post #12
    If this helped, please click the star at the bottom left of this posting and add to my reputation. Many thanks.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



Page 1 of 12 1234567891011 ... LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 10-13-2014, 09:20 AM
  2. Replies: 6
    Last Post: 02-19-2014, 11:11 AM
  3. Replies: 3
    Last Post: 07-03-2013, 10:38 AM
  4. Replies: 1
    Last Post: 10-30-2012, 10:29 AM
  5. Replies: 1
    Last Post: 07-11-2012, 08:36 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums