Add new record to 2 related tables at the same time

[Sammie]

I have 2 tables: tblVendors and tblVendorContacts.
I want to add a new vendor via frmVendors, and at the same time, create a
new record in tblVendorContacts with both the VendorID from the new record I
am creating, and the new contact information I am typing in.

Objects & fields:
tblVendors has fields: vendorID, contactname, email

tblVendorContacts has fields: contactID, vendorID, namecontactV, and
EmailContactV

(these tables are joined on vendorID - what kind of relationship should
they have?)

frmVendors is based on tblVendors. I want to add a new vendor here, and
when I get to the contactname field, I want to add a new record to the
tblVendorContacts with the new vendorID, and also the contact information.

Thank you!
Sammie

 

[Allen Browne]

The simplest way to do this would be to use a form with a subform.

The main form is bound to tblVendors, so you enter all the vendor
information there. The subform is bound to tblVendorContacts. It is in
continuous view (one line per person.) So, as soon as you have filled out
the main vendor info. in the main form, you tab into the subform and fill in
the first contact name and email address. Then if there is another contact,
you just fill that on on the next row of the subform.

 

[Sammie]

Will this work if the main form is bound to a query based on tblVendors?

I need to designate one of the vendor's contacts as the active contact for
the vendor on the query. Should I create fields in the vendor record to hold
the active contact details? (The contents of these fields would change when
I change the designated active contact.) Or should I create the fields in
the query? What's the best way to select one of the VendorContact records to
be the active contact?

Thanks.
Sammie Access 2003

 

[Allen Browne]

You can use a query as the source for the main form, but you are likely to
run into trouble if you try to create new records in both the vendors and
vendor-contact tables by using the one form. I suggest you bind each form to
just one table, at least until you have experience of getting that working.

Rather than using a yes/no field in tblVendorContacts to indicated the prime
contact person for the vendor, would you consder using a numeric field named
(say) Priority? This way you can specify the #1 contact for the vendor, and
the #2 (to use if the prime contact person is unavailable), and so on. You
don't have to make this unique: they can have two contacts who are both
priority 1 if nobody cares which one you contact.

 

[Sammie]

I do not want to run into trouble. Would it be safe to bind frmVendors to
the tblVendors, get it working, and then create the query in the data source
of frmVendors? I need the query to format some fields and to create some new
fields from expressions, and I don't want to have to use 2 forms (1 to add a
record, and the other for on-going use).