Keeps creating new records!

A|ex

Registered User.
Local time
Today, 19:53
Joined
Jul 11, 2004
Messages
90
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?
 
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.
 
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?
 
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.
 
if i removed the acSaveRecord would it work then? maybe add a save button on the appointments form?
 
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.
 
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
 
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.
 
A|ex said:
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
 
yhgtbfk is on the right track with the "where" argument. That will open the form to the correct customer but you need to add back the save record command so that it is prior to the OpenForm. You absolutely want to save the current record prior to opening the form. If you don't, the process will not work for new main form records.

BTW - the correct method for saving the current record is:
DoCmd.RunCommand acCmdSaveRecord
Don't use those wizard-generated menu commands.

The final thing you need to do in the popup form is to add a line of code to the BeforeInsert event to populate the customerID field so that the new record will be related to the mainform record.

Me.CustomerID = Forms!YourMainForm!CustomerID

I use the BeforeInsert event for this code because I don't want to run it unless the user actually attempts to add a new appointment. I don't want my code to dirty the record. That just confuses the user because he doesn't know why he gets requests to save when he didn't even change a record. The BeforeInsert event runs as soon as the first character is typed in the form.
 

Users who are viewing this thread

Back
Top Bottom