Re: Customers- Contracts form problem

GL,

The critical point here is that if the Customer already
exists, the only thing that needs to be added to the
Contract table is the CustomerID of that customer. This is
done automatically if the contract is a subform to a
customer form and the linking ID is the CustomerID. It can
also be done manually by using a combobox bound by the
CustomerID that displays the customer choices on a contract
form.

To illustrate this you can try it directly in your query
that ties the customer and contract tables together. If you
open the query in table view and then go to the bottom of
the query and add a new record you should be able to enter
the CustomerID into the CustomerID field that comes from the
Contract table (not the field that may be coming from the
Customer table as that would give you your duplicate key
error that you keep getting) any other fields that are
included in the query coming from the Customer table should
automatically populate into the record. If this does not
work or there is no line to add a new record, your query may
not have been constructed so that it is updateable which
could be another issue.

You stated earlier that you are trying to add your contracts
from a contract group form. If this is the way that you
continue to go and the above method works directly on the
query I would setup your contract subform to use a customer
combobox that is bound to the CustomerID field but displays
the Customer Name with the CustomerID field being the first
field in the combobox. If you set this field to a 0" length
it will effectively hide the ID and let you type in the name
to get the ID.

--
Gary Miller
Sisters, OR



"GL" <GL@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:87D6078C-90C5-4C98-AFB4-39716BB94B92@xxxxxxxxxxxxxxxx
Customers table has indeed the field CustomerID and the
contract table the
fkCustomerID. Form, subform and queries are tied together
probably well
because when displaying records of contract groups there
is no any problem.
The problems starts in the case when I try to add a
contract to an old
customer. In that case all the recalled customer data are
stored in a new
record and this gives the duplicate value for the
CustomerID. What I would
like to do on this case but I don't know how, is to add a
new contract record
and "stays" or "points" to the old customer record.

GL


"Gary Miller" wrote:

That approach works fine for displaying existing
contracts.
Where this creates complications is when you need to
assign
a new contract.

One approach is to put a button or other trigger on your
existing form setup that would bring up a new form that
is
dedicated to new contract entry where you then select the
appropriate contract type and customer from comboboxes.
You
can then refresh the contact group form to display the
new
entry.

A variation that has you start a new contract from the
contract group form should also be fine (depending how
the
query is constructed) if the form/subform are tied
together
by contract group and you have a combobox to pick your
customer for the new contract from a combobox bound to
the
CustomerID. Where you will have an issue is that your
query
that ties customers and contracts together may actually
contain the CustomerID's from both the Customer and
Contract
tables. Make sure that you are trying to update the ID
that
comes from the Contract table, not the Customer table
which
may be happening. That is why I prefix the foreign key
fields with an 'fk' so I can tell at a glance which one
comes from which table.

--
Gary Miller
Sisters, OR



"GL" <GL@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7D1C5DAB-B20E-4232-9B6E-3084B65D5D05@xxxxxxxxxxxxxxxx
I have made the structure of tables exactly as you
advise,
but due to the way
I like to have the data entry, I don't use the
form-subform configuration but
one form in continuous form view and as data source a
query that includes
both linked tables. So each line of the list includes
the
fields of both
tables. Actually that form is the subform of a form
that
is assigned to a
group of contracts as contracts are grouped according
to
their kind.
Do you think there is a solution on this?

GL



"Gary Miller" wrote:

The 2 table approach is correct. It would then
normally
translate into a main form for the Customers and a
subform
for their Contracts. It sounds like you are having
problems
with the primary and foreign key in the tables if you
are
getting a duplicate primary key message.

Should be...

tblCustomers:
CustomerID - Autonumber - Primary key with no
duplicates
etc....

tblContracts:
ContractID - Autonumber - Primary key with no
duplicates
fkCustomerID - Number - Long Integer - Indexed -
Allow
Duplicates
etc....

Create a main form for contacts and a subform for the
contract info. The key is to use the CustomerID field
and
the fkCustomerID fields as the Master/Child links on
the
data tab of the subform control when you put the
subform
on
the main form.

Gary Miller
Sisters, OR



"GL" <GL@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:53D2E4DB-687C-449E-864B-FB2BF9025E42@xxxxxxxxxxxxxxxx

Hello

I'd like to have a form to fill info for customers
and
their contract.
Customer's name is given in a combo box and I want
to
have
two options while
filling the form.
First case, if a new contract is made for an old
customer
when customer's
name is selected on the combo the rest customer info
is
automatically filled
(as they are already stored) and the contract
details
are
manually filled.
Second case, for a new customer data for him as well
as
for his contract are
manually filled.
I have tried two tables one for "customers" and one
for
"contracts" joined
by a one to many relation and some ways to do it but
I
am
stacked by the
"primary key duplicate values" problem.
Do you know a way to do it successfully?

Thanks in advance

GL









.