Results 1 to 11 of 11
  1. #1
    hfreedman1957 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    35

    Combo Box Confusion

    Hi,


    In a City table, I have a City_ID field (Autonumber) and a City_Name field (text). In an Address table, I have City_ID (long) field where I'm storing the ID of the respective City from the City table.

    In an Address form with the Address table as the record source, I want a combo box (I think) that will look up the city from the City table and display the City_Name selected from the combo box drop down, but store the City_ID in the Address table.

    I can't figure out how to do that. After using the combo box wizard, I end up displaying the City_ID in the combo box field. Any help would be appreciated.

    Thanks!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The combo is based on a query or sql statement that returns the id and name fields (have 2 columns in the combo).
    You hide the id field by setting its width to 0. You bind column 1 to wherever you're writing the data.
    Maybe all you have to do at this point is set the first column width to 0" assuming the wizard created a 2 column combo for you and you made the right selection when choosing the column to bind to the table.
    Last edited by Micron; 05-19-2017 at 07:05 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    hfreedman1957 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    35
    I wasn't concerned about displaying the ID field in the combo box drop down, but your point about binding to the proper column helped a ton. Expanding on the row source to show the little query, I moved the name field to the 1st position and the ID to the 2nd position. I then set the bound column to 0 and it worked perfectly. I'm not 100% sure what that means. The field is actually storing the ID, but it's bound to the column with the Name. Very confusing!!! Anyway, it works, so thanks so very much!

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The combo column order reflects the query or table fields from left to right. If you bind combo column 0, the value from the combo row in the first column becomes the data value for the left-most table field or updatable query field. Sounds like you solved your problem by reversing the query field order. While not a bad thing to do, know that if you ever build recordsets and refer to the data by its index position (such as rs.Fields(2) ) and start moving things around in tables or queries, you're going to experience some issues.

    Typically, ID's (especially when they are autonumbers) are hidden from the user because they can be confusing, and they are supposed to be meaningless to the user. That's why I mentioned making the width 0". IMHO, you shouldn't be showing them. Sometimes, the less a user sees behind the scenes, the better.
    Glad you got it solved.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    hfreedman1957 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    35
    It's not working (Yikes!!!). The data's all over the place. In many records, when I look at the table, it has the ID of record 7, yet in the form it's displaying the name of record 18. Some are showing blank names even though the ID has a legitimate value. OMG!! I'm tired. I'll look at this again tomorrow.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you have to post back after looking into it, provide more info about the form and what you're doing with it.

    Is it single record form with navigation controls? A continuous or data sheet view? Split? Form/subform?
    What are you doing when selecting combo values? Viewing/editing old records only? Creating new records only (or both)?
    Note that if, for example, this is a single record form and you choose a different combo value from a bound combo, you will alter that value in the existing record. Sometimes the combo should NOT be bound (i.e. it does have a row source property setting but does not have a control source property) so that this can't happen.
    Last edited by Micron; 05-19-2017 at 08:53 PM. Reason: correction

  7. #7
    hfreedman1957 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    35
    Sorry for the delayed response. This was becoming too difficult so I've decided to ignore attempting good db / programming practices, and store the data from the "lookup" tables into my main table rather than storing just the Record ID. The database is small so storage is not an issue.

    No more examples, here is exactly what I have. I have three "lookup" tables, STOs, Projects, and Environments. For STOs and Projects, I've deleted the ID (auto number) fields as the STOName field and ProjectName field are unique in their respective tables and are therefore the primary keys. For the Environments table, I have an EnvironmentsName field (also unique) but I kept Environments_ID as an autonumber only for sorting purposes. I never want the records displayed in alphabetic order of EnvironmentName so I've loaded the table such that the Environment_ID is always the sorted order I want displayed. The EnvironmentName field is my primary key and there are only 10 records in total.

    My main table is called Actions where I have STOName, ProjectName, EnvironmentName, and Environment_ID fields. On the Actions form, I use comboboxes to lookup the values in the respective "lookup" tables. So far so good.

    On the Actions form, in the EnvironmentName combobox, I have a Change Event procedure to grab the Environment_ID field as well from the Environments table anytime the EnvironmentName field changes on the Actions form. This procedure is failing with an error. Also, the Actions form will no lonnger allow me to add a new record. In the Actions query which is the record source for the form, I have set up relationships to the three lookup tables (don't know why I would really need that) but am not including any fields from those lookup tables in the query.

    Here is my change event code...

    Dim rs As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    If EnvironmentName <> "" Then
    strSQL = "SELECT EnvironmentName, Environment_ID"
    strSQL = strSQL & " FROM tblEnvironments"
    strSQL = strSQL & " WHERE EnvironmentName = '" & Me.EnvironmentName & "';"
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    Environment_ID = rs!Environment_ID
    End If

    I can't provide the error I was receiving until I resolve the adding record problem as that is the error I'm now receiving. Any advice would be appreciated. Thanks so much in advance.

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    People on this list don't want to help those who "do their own thing". Follow the advice given, 99% of solutions provided here is the right thing to do and is based on hundreds of years of Access experience.

    Do not use text name fields as primary keys - they can change too easily, spelling mistakes, name changes, etc. and your entire database gets out of sync. Always use autonumbers as the pk for lookup tables.
    Comboboxes for lookup tables will always have two columns, ID then name. The bound column is 1, the widths are 0 and 1/2/etc. Bind them to the record source which will store the ID, not the name.

    If you are unable to add a record in your form then it is usually the fact that the record source does not allow additions, the query may be too complex. Open the query from the nav pane and see if it allows you to add a new record, to test if it is the query at fault or not.

    When you have done this, start a new thread with your new problems.

  9. #9
    hfreedman1957 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    35
    No, I cannot add records with the query. I knew that already but neglected to mention it. In my prior revision, I created the db such that in the lookup tables, I had autonumber ID fields that were the primary key. In the Actions table, I only stored the ID's from the three lookup tables. Therefore in the Action query, I included the lookup tables with relations and I had to add the name fields from the lookup tables into the query so the sorting would be by the names rather than by the ID numbers. When I was updating Action records in the Actions form based on the Actions query, it was making changes to the lookup tables. That's when I threw in the towel and decided I would work only with the name fields and store the data so I didn't need to include any lookup table fields in the query which would prevent any possibility of the lookup tables being changed from the Actions form. I will revert back again as you recommend but I'm going to need help here. Thanks so much!

  10. #10
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The names will come from the comboboxes, not from the record source. The record source will contain the ID, the combobox has its bound column as the ID, so will show the name based on the combobox's row source. Hope that explains it!

  11. #11
    hfreedman1957 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    35
    Yep it does. It's all working as it should. Thanks for putting me back on the proper track. Greatly appreciated!

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

Similar Threads

  1. Combo box confusion
    By Lou_Reed in forum Access
    Replies: 1
    Last Post: 03-24-2017, 12:19 PM
  2. Cascading Combo Box on Form Confusion
    By synses in forum Forms
    Replies: 9
    Last Post: 03-19-2017, 07:24 PM
  3. DLL Confusion
    By frankvfox in forum Access
    Replies: 6
    Last Post: 08-11-2013, 07:22 AM
  4. VB and SQL confusion
    By Wayne311 in forum Programming
    Replies: 29
    Last Post: 01-31-2011, 10:27 PM
  5. Combo box lookup confusion
    By redpenner in forum Forms
    Replies: 5
    Last Post: 08-19-2010, 08:45 PM

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