Inserting Into Multiple Tables - (Unbound?)

[skidawgs]

Hi,



Is using an unbound form the only way to insert into 2 tables at the "same" time?

I am creating a "Add Candidate" form that my user can add a candidate to a position. The table design includes a CANDIDATE, CANDIDATE_POSITION, and POSITION (with a m-m relationship). The main form is based on the POSITION table, so when the user selects a 'Add Candidate' Button, the pop-up form would allow them to insert necessary data. Except, I would like them to insert this data into both tables without needing to add a candidate first then associating that candidate with the position. Instead, I would like to (if possible) populate the CANDIDATE_POSITION.Position_ID with an argument passed from the mainform, then insert into both other tables via a button click.

Is the best way to do this via an unbound form? Or are there any better alternatives?
P.S. - I'm familiar with SQL so would insert into CANDIDATE before inserting into CANDIDATE_POSITION.

Thanks!
Skid

[June7]

Use bound forms.

Main form bound to Position, subform bound to Candidate_Position with a combobox to select candidate. Master/Child Links properties of subform container will automatically save PositionID into Candidate_Position.

Options to add new candidate 'on-the-fly' during data entry:

1. with combobox NotInList event

2. open candidate input form with combobox ListItemsEditForm property (I've never tested this)

Review

http://allenbrowne.com/ser-27-01.html
https://blueclawdatabase.com/notinlist-event-code/

[ssanfu]

Be aware that "POSITION" is a reserved word and shouldn't be used for object names....

POSITION: JET reserved (kb248738);ODBC (kb125948);ANSI-92 Reserved (kb287417) (http://allenbrowne.com/AppIssueBadWord.html#P)

[skidawgs]

Thank you for the prompt replies! I will go with your method, June7. I suppose there will just be a couple more clicks which really isn't the end of the world and it is much better than the alternative of an unbound form.

Also, thanks for the insight about using the reserved word POSITION. I hadn't realized it was reserved until now.

Thanks,
Skid