Results 1 to 4 of 4
  1. #1
    Clarionchanger is offline Novice
    Windows Vista Access 2007
    Join Date
    Oct 2012
    Posts
    2

    How to store ID on list box but show related data


    I have 2 tables one, Contacts, primary key ContactID this table contains names, addresses etc and one group called Form primary key Form ID, foreign Key Contact ID. I want to store the related contact ID in my form but display firstname ad surname from Contaacts list. This works fine when I am selecting name. List box shows 2 columns with correct data but when I recall the form after making other entries it only displays the firstname. The correct ContactID is stored. Why does this happen.
    If I can get this working I would like to concatenate the Firstname and surname to make it more readable but don't know where to begin.
    This is my first attempt at an Access database and am finding it very confusing after my other pure database (Clarion). Your help is appreciated.
    Thanks Pete.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,602
    You have a table called 'Form'? Form is a reserved word. Should not use reserved words as names.

    List or combo box RowSource:

    SELECT ID, Surname & ", " & Firstname FROM Contacts ORDER BY LastName, Firstname;

    ControlSource: the field in destination table
    ColumnCount: 2
    BoundColumn: 1
    ColumnWidths: 0";2"
    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.

  3. #3
    Clarionchanger is offline Novice
    Windows Vista Access 2007
    Join Date
    Oct 2012
    Posts
    2
    Thank you June7. Very helpful. BTW I dont have a table called form and the database is much larger than indicated I thought this was the simplest way to describe the problem. Although I did not know Form was a reserved term in Access so I have 2 problems answered.
    I'm sure i will be back
    Pete

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,602

    Access Reserved Words

    It's a fairly long list, review http://support.microsoft.com/kb/286335
    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.

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

Similar Threads

  1. Replies: 1
    Last Post: 04-25-2012, 12:36 PM
  2. list files recursively and store results in array
    By maxbre in forum Programming
    Replies: 2
    Last Post: 11-10-2011, 01:49 AM
  3. Lookup to show related values
    By mjhopler in forum Access
    Replies: 1
    Last Post: 08-19-2011, 01:35 PM
  4. Show related data on a form
    By Accessgrasshopper in forum Forms
    Replies: 4
    Last Post: 03-17-2011, 07:53 PM
  5. Replies: 1
    Last Post: 01-21-2010, 02:36 AM

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