database design - One Form with Fields from two Tables -

i new access 2007 , trying create 1 form has multiple tabs on it. 4 of tabs represent information 1 table , 2 of tabs represent information second table.

there relationship between 2 tables: enrollment table!id has lookup relationship medical info!id lookup.

if there new record created using form, automatically assignes , id client record in enrollment table, corresponding record automaticaly created in medical info table.

the problem having if create new client form, , move medical info table on form, can display id lookup results, however, no record created in medical info table unless enter data 1 field first.

if makes sense, there way write record medical info table @ point client id created, establish relationship between "client id" , "id lookup" without having enter field first?

without getting deep db or form, i'd venture guess @ doing need so:

in form, @ point of entry of client id, use (after_update) trigger check see if new record or not, , run insert sql medical info table , requery form or requery form's fields use data first table. use entry such "new enrollment" in field has information on medical activity done in typical entry, , today's date activity date.

or , create button instead of after_update.

either case, you'll need data validating make sure not cause errors in insert query.

post more specifics if isn't enough help.