Uneffective Data Inserting

[acoon]

I have a form where I insert data into two tables:
reservations
and clients (which its primary key is clientid )
(each reservation has one client .the linking field is clientid)

In order to insert new reservations I built a form where the user inserts the reservation data (including clientid )
and a sub form where the user inserts the client data.

When the user inserts the clients data he inserts it in the subform unless the client already exists, in that case he types the clientid in a textbox in the main form and the details of the client in the sub form automatically appear.
If the user tries to insert an existing client into the sub form an error message appears:
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. (Error 3022)"

I would like to avoid the duplicate work the user has to do and enable him to insert the client data through the sub form solely (new and existing clients as well). How is it possible?

 

[TylerTand]

Sorry you explanation is a little difficult to follow. (probably why nobody has replied!)

If I understand your dilemma you have a form with a sub form. The main form has the Reservation information and the subform has the client's info. Where I got lost was when you said:

"When the user inserts the clients data he inserts it in the subform unless the client already exists, in that case he types the clientid in a textbox in the main form and the details of the client in the sub form automatically appear."

What I don't get is how does the employee know what the clientid is for any customer? Where does he have this information stored? Does he only find this out when he tries to readd the client and he gets the error message about a duplicate record?

The next part that was confusing was this: quote: " I would like to avoid the duplicate work the user has to do and enable him to insert the client data through the sub form solely (new and existing clients as well). "

First off, if the client already has a record, you shouldn't create a new record for him. Just append the value in the reservation field to correspond with the new reservation. (I am assuming that they won't have more than one reservation going at a time?) If so then you want to update the reservation id with the one from the main form. You can do this by first finding the client's info in the sub form. The subform should directly be sourced by the client table. Without a filter by default. This will allow you to use a filter to find the client's information. I would suggest having a phone number field that is a lookup combo box. Set the after update properties to open the subform with the clients info. ex. docmd.openform "Client Info Sub form name here",,,"[phonenumber]=" & "[phonenumber]" & "

This code will reference an unbound text field named phonenumber on your sub form. So the procedure would be when a customer comes to the desk you ask them for their phone number. Put it in the unbound text box and if the client has a record it will open the subform with his info. If it does not then you can have this code below the after update code:

if me![phonenumber] <> client.phonenumber then (create a new record command).

This way you first look up to see if the client exists and if he does then you can run an update query to change the reservationid number to the main forms number.

docmd.runsql "UPDATE [Client Table Name] SET [Client Table Name].ReservationID = [forms]![Main Form Name]![ReservationID];"

This way you just adjust the clients reservation to match his current reservation.

This will fulfill your requirements to have the client's info inputted solely on the subform and also skip the useless entering of data for a customer that already has a record. Hopefully this helps you. If you have any further questions about either my explanation or my code please post again and I will try and help further
Tyler