Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2012
    Posts
    79

    Unanswered: Issue selecting value in 2 column combobox

    I have one combobox with two columns that lists counties in one column, and the state of the county in the 2nd column. The county column (0) is the bound column. Rowsourcetype is "Table/Query"

    There can be the same county name for two or three states. For instance:

    Code:
    County           State
    Wilson            Indiana
    Wilson            Kentucky
    Clark              Ohio
    Vigo               Illinois
    I am using the following SQL statement as a rowsource to retrieve the values:

    Code:
    .RowSource = "SELECT DISTINCT County, State FROM Contacts WHERE Category <> ""Personnel"" ORDER BY County"
    The table "Contacts" is a contact list of businesses, people etc. I'm mainly concerned about businesses thus "Personnel" is not included. All businesses in the contact list have a county listed.
    I use DISTINCT because I don't care what the business name is in this instance, I am merely looking for the county. Without it it gives me an entry for every business in the county/state.

    The issue is, when I select the values "Wilson/Kentucky" it will not allow me to select it, and it seems to default to "Wilson/Indiana". I don't get any errors on debug, or when moving through the code. For values that don't have duplicate counties with different states, for instance, Clark/Ohio, it works fine.

    Any ideas?
    Version: Access 2010

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Have tried making the State the bound column ?

    MTB

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    How is the selection being used? This kind of behavior is seen, for instance, when using a Combobox to retrieve a Record, because the code Access uses involves the FindFirst command, and that's just what it does, finds the first instance of the given selection. In this case it would retrieve

    Wilson/Indiana

    even though

    Wilson/Kentucky

    was selected, because Wilson/Indiana comes first, and Access is only looking at the 'Wilson' part. The solution is to use a Query/SQL Statement, creating a calculated field by concatenating the two Fields (CountyField & " " & StateField) then using this calculated field to retrieve the Record.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Nov 2012
    Posts
    79
    It is indeed going for the first instance. Alphabetically. It's function isn't to retrieve a record. The values are used to update a query which is the record source for a report. Really the state field isn't used at all. It's only function is to differentiate between Wilson/Indiana and Wilson/Kentucky. Otherwise in a single column list it would look like

    Wilson
    Wilson
    Clark
    Clark

    and the user would get confused as to which county they need to choose to see the records for say, Wilson County Indiana.

    I contemplated using a listbox next to the combo box that will list associated states with the selected county but I was hoping for a solution to this problem since it fits better within the form.

    Mike- thanks for the reply. Unfortunately that won't work for this problem since the state is just an identifier.
    Last edited by Pis7ftw; 04-25-14 at 12:12. Reason: Answer mikes post
    Version: Access 2010

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Pis7ftw View Post

    ...the state field isn't used at all...the user would get confused as to which county they need to choose to see the records for...
    Sorry, but that makes no sense! If the state field isn't used at all there would be no need to differentiate between the two Wilsons! And the solution remains the same; concatenate the county and state fields and the correct one will be selected.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Nov 2012
    Posts
    79
    And you sir, are right. In the scheme of things, I misidentified what is used and why. Anyway, I'm familiar with concatenating two or more cbobox values or textbox values into another control, but have no experience doing so in an SQL statement. Could you provide an example.
    Version: Access 2010

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    there's several ways of getting what you want.
    one would be to have 3 columns in your combo, two of which are hidden, and then pull the right values from the combo's selected collection.

    another approach would be to populate the combo with a single column including the county and state, in that order eg:-
    Wilson, Indiana
    Wilson, Kentucky
    Clark, Ohio
    Vigo, Illinois

    then when you build your SQL you need to explode the selected value to build that fragment of your where clause. I'd probably stuff that into a function that returns the state and county SQL EG:-

    Code:
    public function SplitLocation(Location as string, optional Separator as string = ",") as string
    'function that splits a county / state string into parts of a where clause and if required use an optional separator (default is a comma)
    dim words() as string
    'need to put in some validations
    if isnull(location) or len(location)<5 then ';the user is playing silly buggers
      SplitLocation = "" 'test for "" in the calling module, and if sop dont' apply the where clause
    else
      words = split(Location,",")
      SplitLocation = "State = '" & words(1) & " AND County = '" & words(0) & "'")
    endif
    exit function

    then your where clause becomes something like
    Code:
    dim wherefragment as string
    strSQL = "SELECT DISTINCT County, State FROM Contacts WHERE Category <> 'Personnel'" 
    wherefragment = splitlocation (mycombobox,",") 'assume you do separate the county and state by comma's
    if len(wherefragment) >5 then
    strSQL =  strSQL & " AND " & wherefragment & " ORDER BY County"
    .rowsource = strsql
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Nov 2012
    Posts
    79
    Much obliged. I'll tinker with it when I get to work on tuesday.
    Version: Access 2010

  9. #9
    Join Date
    Nov 2012
    Posts
    79
    Makes sense and works great! Thanks for the help!
    Version: Access 2010

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •