Get next available key field

[dsmaj]

Here's my most recent problem...

I have a data entry form to add a new record to a table. This form has several buttons on it to call up addition forms which deal with tables that support the main data entry table in a one-to-many relationship. In order for these supporting forms to be able to store data in their associated tables that relates to the main-data-entry-form's record, I need to have the main-data-entry-form records key field, which of course doesn't exist yet because the user is only at the data-entry stage, and hasn't clicked "save" yet.

To further illustrate, here's an example with one of the supporting tables and the main table

tbl_MainData (one)======>(many) tbl_SupportData

So when the frmMain form calls frmSupportData, it wants frmSupportData to add records to tbl_SupportData that relates to the new MainData record that we are in the midst of creating--however since the new tbl_MainData record hasn't actually been created yet, frmSupportData has no tbl_MainData key to work with! Does this make sense?

My question then, is how can I retreive the next available tbl_MainData key field value in order to add relevant records to tbl_SupportData, and even if I can do this, I just realized that I bet it wont work, since referential integrity gods will yell at me. Hrmff.... Okay, is there a decently reasonable solution to this problem? The only thing I can think of would be to automatically add a new tbl_MainData record as soon as frmMainData loads so that I can use that key in subsequent operations, and then if the user clicks "Cancel" on frmMainData, I would just use a delete query to remove the record from the table. Is this a viable solution?

Thanks if you made it this far through my rambling...
Sam.

 

[dsmaj]

Hey Pat,
Yeah, I was careful not to use the word "subform" because of the obvious confusion it might cause--my pop-forms are in fact just regular forms. The way my forms are laid out, they don't actually specify a recordsource in design view--rather I import my required data in code (I know, bad bad!). Anyhow, you've answered my question regardless...thanks!

Sam.