Keeps creating new records!

[A|ex]

Hi i seem to be having a problem with my database. I have a customers, appointment and appointment details table and forms. Whenever i enter a customer and add appointment details, close. Reopen it and click the appoitment button to bring the appointment form up and it will create a new record. How do i bind an appointment to a customer, so when u open an appointment which is linked via customer id to load up the same details and not create a new record?

So only that ONE customer can make ONE appointment. they cant make more than one? hmmm come to think of it isnt that just a relationship thing?

 

[The_Doc_Man]

To answer your question, we would need to know what goes on behind the scenes when you "click the appointment button." If there is VBA code behind that to create a new appointment, then you have your answer. Look at the OnClick event code for the "appointment" button to get your answer.

 

[A|ex]

i never thought about that:

Private Sub btnOpenAppointment_Click()
On Error GoTo Err_btnOpenAppointment_Click
If IsNull(Me![CustomerID]) Then
MsgBox "Enter customer information before entering an appointment."
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenForm "frmAppointment", , , , acEdit
End If

Exit_btnOpenAppointment_Click:
Exit Sub

Err_btnOpenAppointment_Click:
MsgBox Err.Description
Resume Exit_btnOpenAppointment_Click
End Sub

here is the code isnt it just opening the form in edit mode?

 

[The_Doc_Man]

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

This saves a new record even if you didn't have a new record to save.

You need a separate key or button control that doesn't include the DoCmd to save a record.

 

[A|ex]

if i removed the acSaveRecord would it work then? maybe add a save button on the appointments form?

 

[The_Doc_Man]

What you need to do depends on what you WANT to do.

If you wanted to open the appointment for editing, you need to get rid of the DoCmd that does the acSaveRecord operation. Keep the one that does the acEdit operation.

If you want to create a new appointment, you want both the acSaveRecord and the acEdit operation. This jumps out to me as two buttons, one the clone of the other, with the acSaveRecord operation removed. But it is up to you as to how to get there from where you are now.

 

[A|ex]

what i want to do is when you fill in customer details you press the appointments button to open the appointments form. if there is no appointment for that customer i want it to create one. then if you ever go back to the same customer you can open up the record to view and edit without it making another one

 

[The_Doc_Man]

You might need to do a DCount for this customer to see if s/he has any appointments already. In the one case (0 appointments), do the save and then edit. In the other case, (at least 1 appointment exists), us a DLookup to find the first (or latest) appointment and do an Edit of that record.

So that means you have to put some code BEFORE the DoCmd action to decide whether you want the acSaveRecord or not. If you have 0 records, you cannot select one until you create one - so you need the acSaveRecord in the If/Then/Else/End If sequence on the "0" leg of the test. You won't need to find anything because that acSaveRecord does it for you. But on the other side, you are going to need a query to find your way around to the record you want in order to do the .Edit in the first place.

 

[yhgtbfk]

what i want to do is when you fill in customer details you press the appointments button to open the appointments form. if there is no appointment for that customer i want it to create one. then if you ever go back to the same customer you can open up the record to view and edit without it making another one

What about

DoCmd.OpenForm "Appointments", , , "[customer]='" & customer & "'"

This limits the form to display only those for the customer.

In the "All" properties for the form, dont allow additions or deletions.

Since each customer only has one appointment, the appointment data should be in the same table as the customer data and the customer should be the primary key.

When you enter the customer details, simply dont display the appointment data on the form.

When you go to the appointment's form, bind the report to the customer record source and just have the appointment data displayed.

I hope that makes sense