Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2008
    Location
    Cardiff
    Posts
    47

    Unanswered: Table Columns Relationships

    Hi Guys,

    Please would you be able to advise me on the following:

    I got a table within my database that I use for contact details. The table consists of three columns:

    Name
    Telephone Number
    Email Address

    The table is linked to a form where the contents are ready available to choose from within each column so that who ever populates the form doesn't need to type in the data. i.e the data is already available within the drop-down list.
    What I'm now wanting to do is link the columns together so when 'Name' is selected the other two columns populate automatically...

    For Example:

    From within my form, I choose 'Joe Bloggs' in the first column, his telephone number and email address will display automatically within the next two columns.

    Sorry again if this isn't clear...

    Cheers...

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Why not just use a multiple column listbox that loads all of the data at once instead of making the user select a contact to load the details? Not sure what level of expertise you have, but if you need help on List Boxes, check out Martin Green's site for a good intro. Also look at the properties to a listbox and check out the help for the column count property.
    Me.Geek = True

  3. #3
    Join Date
    Nov 2008
    Location
    Cardiff
    Posts
    47
    Hi Nick,

    Thanks for the advice....

    I just think it looks a bit more tidy doing it this way. There is only 3 of us that will be updating the database anyway...

    Cheers

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by boams
    What I'm now wanting to do is link the columns together so when 'Name' is selected the other two columns populate automatically...
    So if you're not already loading the data into "columns" then where is the data being loaded into? If you mean that the users select a name from a combo-box, and you'd like the number and email to be loaded into textboxes, then I would recommend using a dlookup on the OnClick event of the combobox, or something along that lines. Let us know if you need anymore help or wish to continue.
    Me.Geek = True

  5. #5
    Join Date
    Nov 2008
    Location
    Cardiff
    Posts
    47
    Hi Nick,

    Thanks again for your reply...

    The data is being loaded into a table, then being used as look-up information on the form... I think that is it, when a Name is selected from the drop down on the first column, I need the other two columns (still the same table) to populate based on the 1st Columns Name...
    Could you please advise what dlookup is and how I set this up...

    Thanks again.... :-)

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by boams First Post
    I got a table within my database that I use for contact details. The table consists of three columns:

    Name
    Telephone Number
    Email Address

    The table is linked to a form where the contents are ready available to choose from within each column so that who ever populates the form doesn't need to type in the data. i.e the data is already available within the drop-down list.
    Quote Originally Posted by boams Last Post
    The data is being loaded into a table, then being used as look-up information on the form...
    I guess I'm a little confused as to how exactly you're trying to use the data: Perhaps if you could upload a stripped-down version of your database that you'd like me to look at, I can give it a look and see if I can better understand what you're after.
    Me.Geek = True

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    How many tables are there?? Where are the primary keys? Have you set relationships up?

    There should be no reason to do what you are asking. Once the name Fred Bloggs is added, there is no reason to add it again, form or not.

    This whole thing smells of bad table design. Can you please elaborate on your table setup? Perhaps post your ERD?
    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

  8. #8
    Join Date
    Nov 2008
    Location
    Cardiff
    Posts
    47
    Hi Guys,

    Sorry for the confusion....

    I have one table with 3 Columns:

    Column 1 is called Name
    Column 2 is called Telephone Number
    Column 3 is called Email Address

    The table is linked to 3 Combo Boxes on a form. Each Combo Box retrieves data from each column of the table.

    What I'm wanting to do on the form is to be able to select a Name from the first combo box and the other two combo boxes are then automatically filled with information thats specific to the name.

    I can upload the database if thats easier, which part do you need? I've split the database into Front & Back-end and created the MDE file...

    Thanks again for your support....

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    nckdryr has already suggested the two options
    if you con't have too many contacts stuff all the values you require into list/combo box then retireve the values using the item selected collection and get the requisite columns, bearing in mind access uses base 0 for columns.
    or do 3 separate dlookups
    or do a single sql recordset fetch

    in all 3 cases you need to put some code in the list/combo boxes event. which event you choose depends on you, personally I tend to use combo boxes with a limit to list, and put code in the on click event.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Nov 2008
    Location
    Cardiff
    Posts
    47
    Cool, thanks for the feedback...
    Can you please advise what code I need to put into the Combo Box and where do I place it?

    Cheers...

  11. #11
    Join Date
    May 2005
    Posts
    1,191
    As for the code, Martin Green has a great tutorial on Cascading Lists for Access that could easily be adapted to load a single value instead of another list, same basic idea at least. As for where to put it:
    Quote Originally Posted by healdem
    in all 3 cases you need to put some code in the list/combo boxes event. which event you choose depends on you, personally I tend to use combo boxes with a limit to list, and put code in the on click event.
    Me.Geek = True

  12. #12
    Join Date
    Nov 2008
    Location
    Cardiff
    Posts
    47
    Thanks Nick

Posting Permissions

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