FormBased On A Query

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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200911/1

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/...UBED1=ACCESS-L


"injanib via AccessMonster.com" u35551@uwe wrote in message
news:9eb8a5ab56078@uwe...
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200911/1

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.

"Roger Carlson" wrote in message
...
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/...UBED1=ACCESS-L


"injanib via AccessMonster.com" u35551@uwe wrote in message
news:9eb8a5ab56078@uwe...
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200911/1