Problem adding entry for existing customer

[Guest]

I have a vacation home bookings database that uses a form to enter bookings,
using a bookings query. The query is derived from two tables that can be
updated, along with a couple of other reference tables (property details, tax
rates etc.)
The customer and booking tables are related by client key. The form allows
me to enter new customer data and all the booking information, and then
invokes Word and Excel to write confirmations and update the booking
calendars. So far, so good.
The problem arises when I try to add a booking for an existing client. I
enter the client key, and the booking information, but then get a duplicate
key error when I exit the form. If I go into the query view (sheet view)
directly, there is no problem. As most bookings are new business, I've
learned to live with this, but I'd really like to know how to fix the form
(or maybe the query) so that I can enter new bookings for existing customers.

 

[Guest]

In the Before Update event of the control where you enter the client key, do
a Dlookup to see if the client already exists. If it does not, go on about
your business. If it does, present a message box to the user telling them
the client already exists and allow them to either use the existing client or
re enter the client key

 

[John Vinson]

I have a vacation home bookings database that uses a form to enter bookings,
using a bookings query. The query is derived from two tables that can be
updated, along with a couple of other reference tables (property details, tax
rates etc.)
The customer and booking tables are related by client key. The form allows
me to enter new customer data and all the booking information, and then
invokes Word and Excel to write confirmations and update the booking
calendars. So far, so good.
The problem arises when I try to add a booking for an existing client. I
enter the client key, and the booking information, but then get a duplicate
key error when I exit the form. If I go into the query view (sheet view)
directly, there is no problem. As most bookings are new business, I've
learned to live with this, but I'd really like to know how to fix the form
(or maybe the query) so that I can enter new bookings for existing customers.

Rather than basing your Form on a Query joining the two tables, I'd
suggest recognizing the real-life fact that you have (albeit rarely) a
one (customer) to many (bookings) relationship, and use a Form based
on the customer table with a Subform based on Bookings. You could have
a combo box on the Customer form to look up an existing customer, with
its NotInList event programmed to go to the new record if the customer
is not found.

John W. Vinson[MVP]

 

[Guest]

What action should I take if the client exists, and is accepted? I have
name, address, phone etc. fields from the customer table. Is there some way
of dynamically creating a new row in the query at this point? Currently, I
just enter the client key in a new row in sheet view and all the client
information is automatically populated.