Results 1 to 9 of 9
  1. #1
    tmptplayer is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    4

    Question Simple Sub-Form Lookup Table Updating Issue

    Greetings,

    I think I have a stupid simple question, but can't figure it out. I'm trying make a form where I associate two tables. My structure is:
    Table A:

    Tbl A ID Tbl A Name

    Table B:
    Tbl B ID Tbl B Name

    Table C:
    Tbl A ID Linked Tbl B ID Linked


    qryJoin
    Tbl A ID Linked Tbl A Name Tbl B ID Linked Tbl B Name


    I can create a form that shows the query, and that it groups by either A or B. I then created a lookup field for the form so that a combo list would list all of the Names (for one or the other) if I try to change the value in the subform. The problem is, it won't update Table C when I do so, it just gives me an error saying unable to update.



    When I hand jam the ID's into C, the fields all show properly in the form (to review data), but to create a new record, it fails. Is there a way to fix this without using VBA?

    Thanks!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,819
    Is this a many-to-many relationship? Is TblC the junction table?

    Options for data entry.

    1. form bound to TblC with comboboxes to select TblA and TblB items

    2. form bound to TblA and subform bound to TblC with combobox to select TblB item

    3. form bound to TblB and subform bound to TblC with combobox to select TblA item
    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
    tmptplayer is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    4
    It is a many to many relationship.

    TblC only references ID numbers - I use the query to bridge the Names to ID's. How do I use one of these options, but allow the user to select the options by name?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,819
    I don't know enough about your data and processes.

    Use real table names in your post.

    If you want to provide your db for analysis, follow instructions at bottom of my post.
    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.

  5. #5
    tmptplayer is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    4
    Here's the database

    TestTrainingDB - Copy.zip

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,819
    If I understand what you are trying to do:

    1. subform RecordSource should be RoleAssociations

    2. combobox on subform should be bound to TrainingID from RoleAssociations

    3. combobox RowSource should be: SELECT ID, TrainingName FROM tblTraining;

    4. combobox ColumnCount should be: 2

    5. combobox ColumnWidths should be: 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.

  7. #7
    tmptplayer is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    4
    Quote Originally Posted by June7 View Post
    If I understand what you are trying to do:

    1. subform RecordSource should be RoleAssociations

    2. combobox on subform should be bound to TrainingID from RoleAssociations

    3. combobox RowSource should be: SELECT ID, TrainingName FROM tblTraining;

    4. combobox ColumnCount should be: 2

    5. combobox ColumnWidths should be: 0";2"
    Great - this works! I'm a bit fuzzy as to how it works though - is it just looking at the first column in the SQL Select statement to choose the field to bind to? Why doesn't it get confused when the combo box has two pieces of data?

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,819
    The BoundColumn property sets the column with value to save. The ColumnWidths property is hiding the first column so users only see TrainingName but the ID is saved.
    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.

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Why is there no relationship for the Location table?
    Where does this fit in your "business"?

    Just a suggestion to sort this out (if it is needed) at the relationship/model stage before you get too deeply involved writing code etc.
    Good luck with your project

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

Similar Threads

  1. table lookup issue
    By Jen0dorf in forum Access
    Replies: 3
    Last Post: 06-12-2017, 03:12 PM
  2. Replies: 1
    Last Post: 07-17-2014, 05:51 PM
  3. Replies: 3
    Last Post: 08-22-2012, 03:51 PM
  4. Combo Box on Form Simple Issue
    By anoob in forum Access
    Replies: 4
    Last Post: 01-13-2011, 01:54 AM
  5. Lookup Form (should be simple)
    By joshlee in forum Forms
    Replies: 3
    Last Post: 05-06-2009, 12:04 PM

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