Results 1 to 2 of 2
2004-02-17, 20:08 #1
- 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.
2004-02-17, 21:55 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 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.