Customers- Contracts form problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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.
 
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
 
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.
 
I greatly appreciate your help. Your comment were very helpful.
So at first I have tried to enter data directly on the query that ties the
customers and contract table and it worked perfectly either for old or new
customers.
Then I have tried to enter data on the Customers-contracts subform.
I have made a combobox with a query as row source that bounds it to the
fkCustomerID and second column tied to the customers name from the customers
table. When I select the name of an old customer everything is OK, the form
auto populates customer data and I can enter contract data. But, here is a
problem again, when I type the name of a new customer I get the message “The
text you entered isn’t an item in the listâ€.
I tried to change the “Limit to List†property of the combobox to No, but I
can’t do it and I get the error message “The first visible column, which is
determined by the columnwidths property, isn’t equal to the bound columnâ€

GL
 
GL,

Good. It sounds like your queries and the subforms are setup
properly so we are getting pretty close to having it working
the way that you want it to.

If you really focus on and think about what I said about the
critical issue being the need to get the CustomerID into the
CustomerID field from the contract table into the
contract/customer query that fuels your subform, you should
understand why you are having the current problem with the
combo box and the new customer not being in the list.

The combobox is looking up the existing customers and
putting them in the list. The combobox is displaying the
customer name and is allowing you to type that in to select
the customer, but it is actually being driven by the
CustomerID field which is hidden. No problem there when you
enter an existing one, just when you enter a new one.

If you enter a name not in the customer table, your
NotInList event will trigger which gives you the message
that you are getting now. You are right. Setting the
LimitToList property to No would normally allow you to type
in something that isn't in the list and save that value in
the table. This presents a couple of problems for you. 1) If
you just typed in a new customers name in and saved that in
the contract record there would be no corresponding record
entered into the customer table to reference later or to use
to add a second contract to that customer. There would also
be no opportunity for you to add any other specific customer
info such as address, phone, etc... 2) Now back to the
critical item. Although the combobox is allowing you to type
in a customer name, it is really dealing with the CustomerID
field exclusively, not the name fields. If you are trying to
enter a new customer there is no CustomerID yet for the
non-existant customer so there is no ID for the combobox to
save as that ID is only assigned when the customer is
entered into the Customer table! The error that you are now
receiving is saying in effect: "I can't enter the name you
typed into the ID field as it is the wrong data type".
Actually the error is truly being raised because you are
typing something into the 2nd or 3rd column of the combobox
and it is really only concerned with the first hidden column
which is the only one that it binds it to the table. To make
a long story short, turning the LimitToList off is only
really useful for a single column combobox where the source
field is not an Access autonumber ID field.

So... To add a new customer's contract the bottom line is
that you need to enter the new customer first so that they
are in the combobox and already have an ID when you need to
assign them to the contract. There are a couple of ways to
approach this.

1) Enter the Customer first before going to the Contracts
form. Easy to design (do nothing) but not totally user
friendly as they need to switch back and forth between
forms.

2) Put a button on the subform that would allow the user to
open the Customer form form entry if the user can't find
someone in the list. Better, but you still have to make sure
that the Contract form gets refreshed to show the new
customer after they have been entered in the other form.

3) Use the NotInList event that is raised when you enter a
name that is not there ( as long as LimitToList is set back
to Yes) to open up a form to open a form to enter the new
customer info. This can be done with a dialog style form
that can then signal the calling form that the new customer
info is now available for the list.

Here is some sample VBA code that assumes that you have a
customer entry form named frmAddCustomer to use to enter the
customer info and your combobox is named cboCustomerID. This
code would be entered into the NotInList event of your
combobox:

Private Sub cboCustomerID_NotInList(NewData As String,
Response As Integer)
Dim strMsg as String
' Verify that the user wants to enter a new customer
strMsg = "No matching entry in the Customer List. Would you
like to enter a new Customer now?"
' They say Yes, open the customer form
If msgbox(strMsg, vbYesNo,"Add New Customer?") = vbYes Then
DoCmd.OpenForm "frmAddCustomer",,,,,acDialog
Response = acDataErrAdded
Else
' They say No, don't add one. Cancel combobox entry
Response = acDataErrContinue
Me!cboCustomerID.Undo
End if

The acDataErrAdded response will trigger after your customer
form is closed and will tell the combobox that the new entry
should now be available and to take a fresh look for it. If
everything was entered correctly it should now be in the
list. This is a much more elegant approach.
 
Dear Gary

Your third approach is really an elegant and technically correct but it is
not convenient for my application for the reasons:
- About 95% of the entries concern new customers.
- Customers with many contracts concern only contracts of different kinds.
Taking these into account I have decided to make a form to enter the
information for a limited number of contract kinds.
Also to put customer and contract data entry available on a form that is
actually a subform under a contract kind form.
The idea was to fill the textboxes regarding the customer and contract
fields at the same time on the same form (the Customers-Contracts form) with
a simple way. For the rare cases where a customer had another kind of
contract before, I thought it should be convenient and safe to use the same
form but populate customer field with the data available from a previous
contract under a “mechanism†that in this case does not add a new record in
the customers table.
This combination is the problem I can do the one or the other way but not
both under the same form.
I thought it should be convenient to do it but may be it is something
impossible for an Access application.

Rregards

GL
 
It would be certainly be bending the rules of normalized
relational database design, but, if it is rare to have more
than one contract per customer, you could make any pertinent
customer info actually part of the contract record itself.
In this case you would not use a combobox to lookup a
customer as you would just enter their info for every
contract. The handicap that you will have is when you want
to see the various contracts that everyone has as it will be
more problematic to group contracts by the customers.

If you entered the contracts from a subform on a Customer
form where you enter the Customer first and then their
contract in the subform I don't think that you would be
having the problem that you are now encountering by entering
the new contracts from a contract group form where you now
need to select a customer that hasn't yet been entered.

Good luck with the project!
 
I’ll join the customers and contract tables in one and I’ll work in one form.
I also think to add one more field and assign a unique number for each
customer as the customer code. If that code is placed in a combobox that
looks up the table and if the same customer is found then the new record is
automaticaly filled with the previously stored data
With a Select Distinct … query based on that field and grouped on the rest
fields involving customer data I can isolate and have a list of customers
only with no duplicates, either if they have one or more contracts.

Thanks for your help,

Regards

GL
 
Back
Top