Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Jun 2003
    Utah, USA
    Thanked 5 Times in 5 Posts

    Junction Tables and Data Entry Forms (XP Pro)

    I can design a form that can add a record to a junction table, provided that I select the data for the new record from the tables that underly the junction table.

    But, is it possible to construct that form so that it can add a record to the junction table, even if the record in the underlying table doesn't exist - and if that is the case open up a subform that can add new records to the underlying tables? The Northwind sample database does not do this, which makes me think it isn't possible.

    Here's a simple example. Could I have tblFirst containing first names and tblLast containing last names and tblComplete putting the two together. Then I want frmComplete to be able to add new records to tblComplete with (say) drop down lists for names that are already records in tblFirst and tblLast, and if I enter (say) a first name in frmComplete that is not in tblFirst then I a subform will open in frmComplete allowing me to update tblFirst.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Junction Tables and Data Entry Forms (XP Pro)

    You can do this if you display the values in a combo box (dropdown list) with the underlying table as Row Source.
    - Set the Limit to List property of the combo box to Yes. If the user enters a value that is not in the underlying table, the On Not In List event occurs.
    - Write code in the On Not In List event of the combo box to add the value to the table. If you search this forum for NotInList, you will find several examples, for instance <post#=146637>post 146637</post#>.
    Post back if you need more assistance.

Posting Permissions

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