Results 1 to 5 of 5
  1. #1
    Roncc is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Dec 2017
    Posts
    85

    Combo Box "no selection"vs "item not on list" issue

    I would like some help with a Combo Box issue. I have two forms, each with a Combo Box, that allows the user to select a client, either by AlienNumber (form1 or by LastName (form 2). I have a “Continue” button on each form that results in the opening a form that is data sourced to a select query where the criteria for the selection is based on the selection made in the Combo Box opn the previous form. If I simply code the Continue button to open the proper form (DoCmd.OpenForm "SpecificClientSelectedByNameFrm", acNormal, "", "", , acNormal) and I have not made a selection in the Combo Box the second form (SpecificClientSelectedByNameFrm) opens but it is empty. If I type in a name that is not on the Combo Box list and select the “Continue” button I get an error message that reads “The text you entered isn’t an item on the list”.
    On the other hand, if I attach code to the “Continue” button to advise the user if they have not made a selection from the Combo Box (in other words the Combo Box is left blank) I can successfully place an error message to the user asking them to make a selection from the list. However, if they have typed in something into the Combo Box that is not on the list the proper form opens but it is empty.
    The code I use to inform the client that they have not made a selection is:

    If Combo1.Value & "" = "" Then
    MsgBox "You have not selected a Client. Use the DropDown list to the right of the Alien Number box to select the Client you wish see.", vbOKOnly, ""
    Else


    DoCmd.OpenForm "SpecificClientSeletedFrm", acNormal, "", "", , acNormal
    End If

    I want the code to check for no selection on the Combo Box but I also want to inform the user if they have entered data that is not on the list but what I have so far will only do one or the other but not both. The OpenForm statement is the same on each:

    DoCmd.OpenForm "SpecificClientSelectedByNameFrm", acNormal, "", "", , acNormal

    DoCmd.OpenForm "SpecificClientSeletedFrm", acNormal, "", "", , acNormal

    The only difference is that one has an if than else clause before the OpenForm code and the other doesn’t.
    Any ideas?
    Thanks in advance for the help.
    Ron

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    have both combos run RunValidCbo event to check what to do.
    both combos will have NOT IN LIST event to remove the bad entry.

    Code:
    sub 
    cboByName_AfterUpdate()
    RunValidCbo
    end sub
    sub cboAlienID_AfterUpdate()
    RunValidCbo
    end sub
    Public Sub RunValidCbo() Select Case True Case Not IsNull(cboByName) DoCmd.OpenForm "SpecificClientSelectedByNameFrm" Case Not IsNull(cboAlienID) DoCmd.OpenForm "SpecificClientSeletedFrm" Case Else MsgBox "You have not selected a Client. " & vbCrLf & "Use the DropDown list to the right of the Alien Number box to select the Client you wish see.", vbOKOnly, "Required" End Select End Sub Private Sub cboByName_NotInList(NewData As String, Response As Integer) Response = acDataErrDisplay
    cboByName= Null
    End Sub

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Searching by just Last name is not a good thing to do. How many "Smiths" could there be. Yes, I know it is your dB and you control what/who gets entered, but many people could have the same last name. Only one person should have an "AlienNumber".

    You can set the combo box "Limit to List" property to YES. It will not allow someone to type in a name that is not in the row source list.
    Or you can write a lot of code the check to see if the value entered in the combo box is in the table.

  4. #4
    Roncc is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Dec 2017
    Posts
    85
    I tried this but couldn't get it to work. The other solution offered by the second responder did fix the problem. Thank you for your the information you provided and for taking the time to respond.

    Ron C

  5. #5
    Roncc is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Dec 2017
    Posts
    85
    Your suggestion did the trick. Thank you. Re: last name searches; You are certainly correct, there can be many "Smiths" but often there are situations where the user (or the client) does not know the AlienNumber. For those instances I have provided a method for the user to find the client by looking up the last name but the combo box also includes other identifying information, first name, address, telephone number, etc.

    Thanks again.

    Ron C

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 02-28-2017, 12:29 PM
  2. Replies: 2
    Last Post: 04-12-2016, 12:58 PM
  3. Replies: 4
    Last Post: 09-10-2015, 08:22 AM
  4. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  5. Replies: 11
    Last Post: 03-29-2012, 02:32 PM

Tags for this Thread

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