Results 1 to 10 of 10
  1. #1
    justin.cherveny is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Oct 2016
    Posts
    4

    Question Combo box: Non-Numeric values from another table

    Hi, All!



    I have a combo-box related question.

    I am trying to pull in generic client data from a table into a form using a combo box, but I only get the option to pull in numeric data.

    Here are the details:

    First, I have 2 tables: ClientData and IncomeStatementData

    The ClientData table is a static table lists all of our clients. It only changes if we get a new client or if a client updates their address/phone number etc. Each client has a Client_ID field (Primary Key) and each client is only listed once with information that will not change (Company_Name, Tax_ID, etc)

    The IncomeStatementData table has income statement data for each company. We create a new entry for each company on a quarterly basis. This table also has Client_ID, Company_Name, Tax_ID, etc. The IncomeStatementTable has ID (autonumber) as its primary key.

    There is a one-to-many relationship between the Client_ID in the ClientData table and Client_ID in the IncomeStatementData table.

    I made form to input data into the IncomeStatementData table. In design view, I click Design >> Combo Box and then click where I want the combo box on the form.

    I select "I want the combo box to get the values from another table or query" and click "Next"

    Under "Which table or query should provide the values for your combo box?" I select ClientData (Because this is where the company name, tax ID etc is stored)

    Here is where I am stuck. Where it says "Which fields of ClientData contain the values you want included in your combo box?" The only options are the numeric ones. Company_Name, for example, isn't available and neither are any of the other non-numeric fields (e.g. customer name, comments, preferred language, etc)

    Thanks so much!


    Justin
    Attached Thumbnails Attached Thumbnails Capture.JPG  

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    doesn't sound right - can you post a screenshot of your relationships.

    Your screenshot is showing Company_ID, but you said

    Each client has a Client_ID field (Primary Key) and each client is only listed once with information that will not change (Company_Name, Tax_ID, etc)

  3. #3
    justin.cherveny is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Oct 2016
    Posts
    4

    Updated Screenshots

    Here are the updated screenshots. You will see that I get Company_ID, Tax_ID, and Contact_Number as options, but none of the qualitative fields (Address, Company Name)Click image for larger version. 

Name:	Capture.JPG 
Views:	10 
Size:	34.9 KB 
ID:	26033Click image for larger version. 

Name:	Capture 2.JPG 
Views:	10 
Size:	38.0 KB 
ID:	26034

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you are not by any chance using lookup or multivalue fields in your Clientdata table?

  5. #5
    justin.cherveny is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Oct 2016
    Posts
    4
    No, just Number and Long text. I'm trying to keep it real simple for the ClientData table.
    Click image for larger version. 

Name:	Capture3.JPG 
Views:	9 
Size:	49.4 KB 
ID:	26035

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Also, just to cover all the bases, what is the sql to the query you are using

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    interesting that contact number now appears when it didn't before.

    can you upload a copy of the db? remove any sensitive data, compact and then zip.

  8. #8
    justin.cherveny is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Oct 2016
    Posts
    4
    Actually, I just figured it out. Access can't do lookups for fields with the "long text" data type. I tried doing a simple lookup by changing a field in the income statement data table and I got an error. Then I changed the data type to "Short text" and it works great. Didn't realize there was a difference between "long text" and "Short text" when it came to doing lookups (and combo boxes)

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Data types in Access......

    Access 2000/2002/2003/2007/2010
    Field Type
    What It Holds
    Text Text up to 255 characters long (including spaces and
    punctuation). Use a Text field, not a Number field, for codes
    — such as phone numbers, ZIP codes, and other postcodes
    — even if they look like numbers.
    Memo Like a Text field, but more of them — up to 65,536
    characters. A memo field can contain rich (formatted) text, and you can set it to Append Only, so that it can accumulate text notes, without allowing the user to delete what’s already there.


    Access 2013/2016
    Field Type
    What It Holds
    Short Text Text up to 255 characters long (including spaces and punctuation).
    Use a Text field, not a Number field, for codes even if they look like numbers, such as phone numbers, zip codes, and other postal codes.
    Long Text Text up to 65,536 characters. A Long Text field can contain
    Rich Text (formatted text), and you can set it to Append Only so
    that it can accumulate text notes without allowing the user to
    delete what’s already there.


    So it would make sense (to me) that combo boxes cannot do "lookups" on a memo/long text field.....

  10. #10
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    yeah - didn't pick up on long text, I'm still working with memo's

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

Similar Threads

  1. ComboBox Rounding Numeric Values
    By acannon in forum Forms
    Replies: 3
    Last Post: 07-03-2014, 04:08 PM
  2. Ordering Numeric Values
    By kazaccess in forum Access
    Replies: 7
    Last Post: 12-31-2013, 02:00 PM
  3. Only show numeric values
    By dr4ke in forum Queries
    Replies: 2
    Last Post: 07-22-2012, 05:09 AM
  4. Setting Up DB with numeric values
    By dashingirish in forum Access
    Replies: 4
    Last Post: 02-17-2012, 03:41 PM
  5. Updating Numeric Values
    By dssrun in forum Queries
    Replies: 9
    Last Post: 11-24-2010, 11:20 AM

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