Using data already entered from table for next record

[George]

I have a Vendor Table with 7 fields of company info:
Name, address, City, State, zip...etc Autonumber as PK

It matches up with a Contract Table with Autonumber as PK

I can have many contracts for one Vendor and many Vendors
for on Contract (Everything is working great!)

How can I allow them to select a Vendor that has already
been entered in and not type everything again?

Each Contract / Vendor entry is unique (no dups)

 

[John Vinson]

I have a Vendor Table with 7 fields of company info:
Name, address, City, State, zip...etc Autonumber as PK

It matches up with a Contract Table with Autonumber as PK

I can have many contracts for one Vendor and many Vendors
for on Contract (Everything is working great!)

How can I allow them to select a Vendor that has already
been entered in and not type everything again?

Each Contract / Vendor entry is unique (no dups)

Do you have two tables? or three? It appears that you have a many to
many relationship from Vendors to Contracts and should have THREE
tables - Contracts (with no Vendor link), Vendors (with no Contract
link), and a "resolver" table with the VendorID (long integer) linked
to the Vendors table PK, and a ContractID linked to the Contracts PK.
You could then use a Form based on Vendors with a Subform based on the
third table, with a combo box for the ContractID.

 

[George]

John: You are right - I do have a Junction Table for the
many to many relationship.... Will the combo box for the
ContractID then bring up all the Vendor info for that
ContractID? How will I than create a New ContractID for
this Vendor info? Can I just type in a new ContractID in
the combo box? Thanks again for your help!

 

[George]

John: You are right - I do have a Junction Table for the
many to many relationship.... Will the combo box for the
ContractID then bring up all the Vendor info for that
ContractID? How will I than create a New ContractID for
this Vendor info? Can I just type in a new ContractID in
the combo box? Thanks again for your help!

 

[George]

John: You are right - I do have a Junction Table for the
many to many relationship.... Will the combo box for the
ContractID then bring up all the Vendor info for that
ContractID? How will I than create a New ContractID for
this Vendor info? Can I just type in a new ContractID in
the combo box? Thanks again for your help!

 

[John Vinson]

John: You are right - I do have a Junction Table for the
many to many relationship.... Will the combo box for the
ContractID then bring up all the Vendor info for that
ContractID? How will I than create a New ContractID for
this Vendor info? Can I just type in a new ContractID in
the combo box? Thanks again for your help!

It might be better then to have the Contracts table as the main form's
recordsource; use whichever table will be updated more frequently. If
you have a more or less static set of vendors but will be creating new
contracts, use the Contract table on the mainform, and vice versa.

Note that sometimes you need to create *both* a new Contract and a new
Vendor - the simplest way to do this is to use the NotInList event of
the subform combo box to pop up a separate form for entering the
"other" side table's new value.