Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2008
    Posts
    2

    Red face Unanswered: Help with showing data

    Hi people,

    I'm new to this forum and was wondering if someone could offer me a little bit of help please. I'm fairly new to Access and someone gave me a copy of NorthWind db to have a look at. So far I have created my own db but i'm a little bit stuck, the part that I wanted to take from the northwind db it was how in the Order tables you can view customers details (it takes the CustomerID and all the info from the customer table), I didnt know how the creator of the Northwind db did this so I looked at the table in design view and found they used a lookup method (which I have tried to use in my db but I dont know how to, I have checked all the filed properties but its doesnlt let me look up).

    What I am trying to do is simple I have a contacts table which has the following fields:

    Contact ID PK
    Company ID FK (Connected to the Company Table)
    Title
    First Name
    Last Name
    etc

    I have created a form and in that form I have changed the Company ID from a text box to a combo box, what I am trying to do is basically show all the Company names in the Company table in this combobox (I have CompanyID in both tables and in the Company table it is set as auto number and in the contact table it is set as number) . However when i want to save data in to the contacts table I want to save it as the Company ID and not as the company name - is this possible?

    Thank you very much.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes it's possible.

    You would have a combo box with a query that returns the records from your company table. Let's say... 2 fields. The CompanyID and the CompanyName.

    You then have the combo box Column Count set to 2, allowing you to access both fields.

    You have the Field Widths set to 0cm;7cm, which makes the combo box work with and show the CompanyName by hiding the first field.

    You also have the combo box Bound Column set to 1 so that it stores just the first field, CompanyID.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Nov 2008
    Posts
    2
    Hi StarTrekker

    Thanks for your help.

    What I have done so far is as follows:

    So far what I have done is the following:

    I clicked on query, I changed it to SQL view and inserted the following:

    Code:
    SELECT CompanyID, CompanyName 
    FROM Company 
    ORDER BY CompanyName;
    I have then clicked on the design view of the Contact table and selected CompanyID, in the field property I have selected Lookup.
    Then I changed the column count to 2, and changed the column width to 0;7 and the bound column to 1.

    But when I click on my form in the combobox it says '0'

    Am I on the right path? Thanks

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You don't need a query.
    You don't need to, and never should, mess with the design of your tables.
    This is all done in the form.

    Changed the following slightly.

    You would have a combo box with a Row Source that returns the records from your company table. Let's say... 2 fields. The CompanyID and the CompanyName.

    You then have the combo box Column Count set to 2, allowing you to access both fields.

    You have the Field Widths set to 0cm;7cm, which makes the combo box work with and show the CompanyName by hiding the first field.

    You also have the combo box Bound Column set to 1 so that it stores just the first field, CompanyID.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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