FormBased On A Query

[injanib via AccessMonster.com]

Hi all,

I have posted this question before, but I might not have posted in the right
group.

I hope I get an answer from this group.

I have two tables, tblCustomer and tbleContract

tblCustomer has the following fields:
CustomrID (Primary Key)
Customer
Organization
Contact

tblContract has the following fields
ContractID (Primary Key)
CustomerID
ContractName
ContractNumber
AwardDate

tblCustomer has a one-to-many relationship with tblContract using the
CustomerID field.

One Customer from tblCustomer may have multiple Contracts in tblContract, so
here is what I did.
I created a query based on both tables, with only one CustomerID field from
tblCustomer.

My form is based on the query.
The problem is that when I enter a record for a new contract and select a
customer name that already exists in the tblCustomer table, the new record
will write to the query and the query will then synch back with the
underlying tables and record a duplicate entry for the tblCustomer table.

I am not sure how to work this out. When I enter a new contract and select a
customer name, I want the contract to be just related to this customer and
not enter a duplicate entry in the tblCustomer everytime I enter a record in
the tblContract other wise it will defeat the purpose of having two tables.
As I mentioned One customer may have multiple contracts.I am not sure if this
is because of how the relationship is set up or what.

 

[Roger Carlson]

The usual way to do this is to have a Form/Subform where the mainform is
based on the table on the "one-side" of the relationship and the subform is
based on the table on the "many-side".

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Rockn]

You should not make the form based on a qury for this instance if you are
inputting data. Only use the customer table to build a dropdown list to
select the customer from. Your contracts table can have many contracts for
the same customer so do not use a query to create this form, leave it
unbound and use VBA code to populate the contracts table once the form is
fired off.

The usual way to do this is to have a Form/Subform where the mainform is
based on the table on the "one-side" of the relationship and the subform
is based on the table on the "many-side".

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L