Results 1 to 9 of 9
  1. #1
    sdyk76 is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    13

    Record won't change/navigate when making a ComboBox selection

    I'm having a problem with a form I am creating. I want to have the form select a record from a combo box and display all the fields for one record from one table and some related information from two other tables.



    It works just fine when I am only working with fields from one table but when I try to add fields from another table the records stop changing when I make a new selection in the Combo Box.

    I have 3 tables:

    tblProject(ProjectName, ProjectNumber, ProjectDescription, tblClient.ClientName, tblClientContact.ContactName)
    tblClient(ClientName, StreetAddress, City, State, Zip Code)
    tblClientContact(ContactName, PhoneNumber1, PhoneNumber2, EmailAddress, tbl.Client.ClientName)

    When I create a form using just the tblProject and use a combobox to select a Project name and Number to navigate through records everything works just fine. If I add the tblClientContact.PhoneNumber1 field to the form (as related to the appropriate client contact for that project) or the Address field from the client table, everything breaks. Selecting a project name or number from the combobox does nothing except change the field in the combo box.

    I am very new to access (less than 20 hours) so let me know if I should be supplying more/different information.

    Thanks!

  2. #2
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Do some research on normalization.
    Your tables should probably look more like these below. Use primary Keys and Foreign keys.

    tblProject(ProjectID,ProjectName, ProjectNumber, ProjectDescription, ClientID,ContactID)
    tblClient(ClientID, ClientName, StreetAddress, City, State, Zip Code)
    tblClientContact(ContactID, ClientID, ContactName, PhoneNumber1, PhoneNumber2, EmailAddress)

  3. #3
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    heres an example of the joins and combobox
    ProjectClients.zip

  4. #4
    sdyk76 is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    13
    Thanks Moke, that helps alot. So when I use the Lookup wizard in my project table for say, the client should I only use the clientID as a field instead of clientID and clientName? Or is it okay to use both fields so its easier to identify the right record?

    I think somewhere along the way I didn't include the primary key in one of my lookup fields.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    If you have created Look up FIELDs in you r table(s), read this:
    The EVILS of Lookup Fields in tables

  6. #6
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    You would normally have a Form based on One Table and then Subforms to display the related data from other tables.

  7. #7
    sdyk76 is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    13
    Thank you for the help. I've basically resorted to starting over and recreating my tables adhering to proper normalization.

    My next question is this: If I don't use a lookup field, how does the end user (in a form) input the correct foreign key without directly knowing which record that key corresponds to? Is it okay to utilize the lookup wizard as long as the data displayed in the field is the foreign key?

  8. #8
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    If you have setup your table relationships correctly then the Foreign Key will auto-populate when adding a new record in the subform.

    If you can upload a zipped copy of the database we can give you an example.

  9. #9
    sdyk76 is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    13
    Thank you for the help everyone. I've been spending some time normalizing my tables and making better relationships. I had some pretty bad preconceptions about how Access was supposed to work so I appreciate everyone's help.

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

Similar Threads

  1. Replies: 8
    Last Post: 04-30-2020, 07:59 AM
  2. How to update record with combobox selection
    By vector39 in forum Programming
    Replies: 3
    Last Post: 09-25-2017, 09:19 AM
  3. Replies: 4
    Last Post: 09-13-2017, 10:35 AM
  4. Replies: 1
    Last Post: 04-13-2016, 09:02 PM
  5. Combobox selection updates Table record
    By lucky in forum Access
    Replies: 4
    Last Post: 10-30-2011, 10:44 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