Results 1 to 11 of 11
  1. #1
    jondavidf is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jun 2012
    Posts
    57

    Combo box - Forms vs. Tables

    Hey all,

    I'm trying to get a combo box to display one value (Race) on a form but display another value (RaceID) in the underlying table, with both Race and RaceID tied to the combo box. This is the setup for the part of the database I'm working on:



    Table 1 = tblPtMain - This table has general demographic info about patients, including the field Race. I used the LookUp Wizard (Combo Box) to select the RaceID and Race from tblLstRace.
    Table 2 = tblLstRace - This table has two fields - RaceID and Race.
    Form 1 = frmPtMain - This is a data entry form where users will enter the demographic info including Race, via a Combo Box.

    I would like the FORM to display the Race field as the actual race, but the TABLE to display the race field as the ID. I've tried changing the "Row Source" order and the "Bound Column" in both properties but I end up saving the actual race in both the form and the table. See images below.

    Attached images are:

    1 - Race Field properties from the table tblPtMain.
    2 - Partial form view with combo box from the form frmPtMain.
    3 - Race field properties from the form frmPtMain

    I have several other fields that I'm trying to do the same thing with. I've gotten most of them to work the way I want, but after much trial and error and no straight-forward method to the madness. It seems like it shouldn't be too hard, and I was hoping you may have a simple solution that I could do most every time that would work.

    Thanks!

    Jon

    Click image for larger version. 

Name:	CaptureTblPtMain - Race Field Properties.PNG 
Views:	25 
Size:	10.0 KB 
ID:	36803Click image for larger version. 

Name:	CaptureFrmPtMain - Race Field.PNG 
Views:	25 
Size:	13.0 KB 
ID:	36804Click image for larger version. 

Name:	CaptureFrmPtMain - Race Field Properties.PNG 
Views:	25 
Size:	11.3 KB 
ID:	36805

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The bound column should be RaceID and should be the first column and normally hidden.
    So you see the description field but store the ID value.
    Normally you would use an autonumber for the ID field. Values 01, 02 etc indicate the use of a text field.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    RowSource: SELECT RaceID, Race FROM tblLstRace ORDER BY RaceID;
    ColumnWidths: 0";2"

    I NEVER build lookups in tables. http://access.mvps.org/Access/lookupfields.htm
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    jondavidf is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jun 2012
    Posts
    57
    Thanks! I prefer to hide it as well, but I think my boss has trust issues when she can’t see the ID field 🤪. I may just try to readdress with her. I think it would make things simpler.

    One question, though, if I extract the table data to an excel sheet will it extract RaceID or Race if I hide the ID as you suggest? The end game is that all data will be extracted from Access and imported into SPSS for analysis. My boss just wants the codes (like RaceID) for import into SPSS. Thanks again!

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    It will export the alias Race value if lookup is built in table.

    These RaceID values have meaning to users? Are these RaceID values required for some sort of reporting requirements? Should they really be considered a code value (such as ZipCode, SSN, etc) and not just simply a PK/FK record link (which could easily be accomplished with autonumber type)?

    Can have a textbox that is also bound to tblPtMain field to display the RaceID. Whether or not textbox is locked or allows edit is your decision.

    Recommend avoid using exact same field names in multiple tables. A common convention is to use a suffix of PK and FK: RaceID_PK, RaceID_FK
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    jondavidf is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jun 2012
    Posts
    57
    The ID codes are what is desired for export, and there are several other fields in the database that will also have ID codes. They do not represent anything other than the associated field (e.g. 0 = “No”; 1 = “Yes”; 99 = “No Data”; or 0 = “Male”; 1 = “Female”; etc.). They are used primarily to shorten the amount of text space used and increase the ability to manipulate data when exporting from Access to Excel and importing into SPSS (statistical software). The final spreadsheet imported will probably have about 300 columns and 1000 rows for SPSS to analyze so any reduction in size and ease of data manipulation will be beneficial.

    Thanks again for the help!

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by June7 View Post
    RowSource: SELECT RaceID, Race FROM tblLstRace ORDER BY RaceID;
    ColumnWidths: 0";2"
    I NEVER build lookups in tables. http://access.mvps.org/Access/lookupfields.htm
    Me neither because of all the confusion these create

    It will export the alias Race value if lookup is built in table.
    Sorry but that's not true.
    The table DISPLAYS the Race field but STORES the RaceID field
    If exported, it exports the field that is saved - the RaceID field.

    I've just rechecked as its several years since I last used this idea.
    So whilst I wouldn't use lookup fields at table level, that will give what the OP wants here!
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Before I made that statement, I tested exporting a table with lookups (not the Lookup data type, just number field formatting for combobox) with the export wizard to Excel. The alias values were exported, not the ID. However, just realized I checked "export with formatting and layout". So I tested again and did not check that and this time the ID's exported, not the alias values.

    So we are both right

    And OP wants the RaceID, not the Race descriptor - so not using lookups in table will serve just fine.

    If you really want to reduce bits output, then make RaceID a number type field instead of text. You can always format for display with leading zero.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    jondavidf is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jun 2012
    Posts
    57
    Could you expand on what you do instead of lookup tables? Thanks!

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The lookup tables are not eliminated, lookup fields are. Still build lookup tables, just don't build lookup fields in other tables that reference the lookup tables.

    Build comboboxes (or listboxes) on forms, instead of setting combobox/listbox properties for field in table.
    Last edited by June7; 01-06-2019 at 05:57 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by June7 View Post
    Before I made that statement, I tested exporting a table with lookups (not the Lookup data type, just number field formatting for combobox) with the export wizard to Excel. The alias values were exported, not the ID. However, just realized I checked "export with formatting and layout". So I tested again and did not check that and this time the ID's exported, not the alias values.

    So we are both right
    I see what you mean. Well I never knew that!
    However the fact that ticking a box on the export wizard gives different results just adds to the confusion table level lookups cause in my view
    One more reason to add to the evils of table lookup fields
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 17
    Last Post: 04-09-2018, 04:39 PM
  2. Replies: 15
    Last Post: 12-08-2017, 05:32 PM
  3. Replies: 7
    Last Post: 11-06-2017, 01:23 PM
  4. Forms and Tables
    By Tiffy in forum Access
    Replies: 4
    Last Post: 02-11-2012, 03:19 PM
  5. Replies: 10
    Last Post: 07-12-2011, 11:09 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