I am currently working on a database where the main form uses a tab control to switch between different information for each record. The primary table, tblFunction, will store a foreign key, MenuID, in each record. The MenuID is linked to tblMenu, where detailed information is stored. The "Menu" tab contains a combo box (ctlMenu) which is bound to the MenuID field and lists all the menus from tblMenu (while the value for this combo is the MenuID, the list is set to hide this column and only display the menu name). When the user selects a menu from the list, a subform below will display detailed information from tblMenu. I have managed to get the form to do this much so far, however there are a few considerations that I cannot seem to figure out:

1. There is button set to create a new menu, and when clicked should set the subform to data entry mode. A new tblMenu record is created, and when the user clicks a "save" button, I would like it to set the ctlMenu combo to the MenuID of the new record, lock the subform, and filter to display the details in the subform.

2. The form should automatically load the subform details if a menu has been selected (ie. if the user opens a saved function). If no menu has been selected, the user is presented only with a blank subform. So far what happens when I try this is that I get an empty grey box where the subform should be OR a new tblMenu record being created every time frmFunction is run. Essentially, there should not be a tblMenu record created unless the user has explicitly clicked the 'new' button to create one.

3. The user should have the option to create a new menu based on an existing one, in which case the menu record would be duplicated and given a new name.

4. If the user types a value into the combo box that is not in the list, the form should automatically create a new record with the typed value as the Menu Name.

Thanks in advance for any help you can possibly give me!