"Add Client" Button Creates Two Identical Records

[STEVENCV]

Hi,

I have a database that was created by a colleague. They have created a form for adding client records onto the database.

This is done via a button that, when pressed, checks mandatory fields for correct entries and then saves the record.

However, when we do this, it is creating two identical records on the database.

She cannot see what she has done wrong in the code, and I know next to nothing about coding, so could someone please see the following code and let me know if anything obvious is in there that might be causing this problem?

Thank you.

Private Sub CmdAdd_Click()

Dim dbAddClient As DAO.Database
Dim rstAddClient As DAO.Recordset


Set dbAddClient = CurrentDb
Set rstAddClient = dbAddClient.OpenRecordset("ClientInfo")

If IsNull(TxtForename.Value) Then
   MsgBox "Enter a valid Forename", vbExclamation, "Cannot Save"
ElseIf IsNull(TxtSurname.Value) Then
    MsgBox "Enter a valid Surname", vbExclamation, "Cannot Save"
ElseIf IsNull(TxtDB.Value) Then
    MsgBox "Enter a valid Birth date", vbExclamation, "Cannot Save"
ElseIf IsNull(TxtProperty.Value) Then
    MsgBox "Enter a valid Property Number/Name", vbExclamation, "Cannot Save"
ElseIf IsNull(TxtStreet.Value) Then
    MsgBox "Enter a valid Street", vbExclamation, "Cannot Save"
ElseIf IsNull(TxtPostcode.Value) Then
    MsgBox "Enter a valid Postcode", vbExclamation, "Cannot Save"
ElseIf IsNull(TxtTelNo.Value) Then
    MsgBox "Enter a valid Contact Telephone number", vbExclamation, "Cannot Save"
ElseIf IsNull(CboArea.Value) Then
    MsgBox "Enter a valid Area", vbExclamation, "Cannot Save"
ElseIf IsNull(TxtNINO.Value) Then
    MsgBox "Enter a valid National Insurance Number", vbExclamation, "Cannot Save"
ElseIf IsNull(CboTenure.Value) Then
    MsgBox "Enter whether client has their own house or rents", vbExclamation, "Cannot Save"
ElseIf IsNull(CboHowFunded.Value) Then
    MsgBox "Enter how the clients service is funded", vbExclamation, "Cannot Save"
ElseIf IsNull(CboLevel.Value) Then
    MsgBox "Enter a valid ID number", vbExclamation, "Cannot Save"
ElseIf IsNull(TxtAlertID.Value) Then
    MsgBox "Enter a valid ID number", vbExclamation, "Cannot Save"
ElseIf IsNull(CboStatus.Value) Then
    MsgBox "Enter clients status", vbExclamation, "Cannot Save"
ElseIf IsNull(TxtReviewDate.Value) Then
    MsgBox "Enter date the client was reviewed", vbExclamation, "Cannot Save"
ElseIf IsNull(CboReferral.Value) Then
    MsgBox "Enter a valid Referral Group", vbExclamation, "Cannot Save"
ElseIf IsNull(CboUrgent.Value) Then
    MsgBox "Enter if client was an urgent install", vbExclamation, "Cannot Save"
ElseIf IsNull(CbocostAvoidance.Value) Then
    MsgBox "Please fill cost avoidance field", vbExclamation, "Cannot Save"
    
ElseIf MsgBox("Save Member Details ?", vbOKCancel, "Confirmation") = vbOK Then

rstAddClient.AddNew
rstAddClient("Title").Value = CboTitle.Value
rstAddClient("Forename").Value = TxtForename.Value
rstAddClient("Surname").Value = TxtSurname.Value
rstAddClient("Date_of_Birth").Value = TxtDB.Value
rstAddClient("Property_Name").Value = TxtProperty.Value
rstAddClient("Street").Value = TxtStreet.Value
rstAddClient("Village").Value = TxtVillage.Value
rstAddClient("Town").Value = TxtTown.Value
rstAddClient("City").Value = TxtCity.Value
rstAddClient("Postcode").Value = TxtPostcode.Value
rstAddClient("TelephoneNo").Value = TxtTelNo.Value
rstAddClient("National_Insurance_No").Value = TxtNINO.Value
rstAddClient("AlertID").Value = TxtAlertID.Value
rstAddClient("SwiftNo").Value = TxtSwiftNo.Value
rstAddClient("OldID").Value = TxtOldID.Value
rstAddClient("Review_Date").Value = TxtReviewDate.Value
rstAddClient("Review_due_date").Value = TxtDueDate.Value
rstAddClient("Reviewed_by").Value = TxtReviewedBy.Value
rstAddClient("NOK_Name").Value = TxtNOKname1.Value
rstAddClient("NOK_telephone_no1").Value = TxtNOKTelNo1.Value
rstAddClient("NOK_telephone_no2").Value = TxtNOKTelNo2.Value
rstAddClient("Date_of_referral").Value = TxtDateRef.Value
rstAddClient("Time_of_Referral").Value = TxtTimeRef.Value
rstAddClient("Start_date_of_service").Value = TxtStart.Value
rstAddClient("GSM_Unit_Requested").Value = TxtGSMRequest.Value
rstAddClient("Install_completion_date").Value = TxtInstall.Value
rstAddClient("Installed_by").Value = TxtInstalledby.Value
rstAddClient("Reason_not_achieved").Value = TxtReason.Value
rstAddClient("End_date").Value = TxtEnd.Value
rstAddClient("DeInstall_date").Value = TxtDeInstall.Value
rstAddClient("NOK_Name").Value = TxtNOK2Name.Value
rstAddClient("NOK2_Telephone_No1").Value = TxtNOK2TelNo1.Value
rstAddClient("NOK2_Telephone_No2").Value = TxtNOK2TelNo2.Value
rstAddClient("sensor_requested").Value = TxtSensorRequest.Value
rstAddClient("Serial_number").Value = TxtSerial.Value
rstAddClient("Keysafe_requested").Value = TxtKeyRequest.Value
rstAddClient("KeysafeNo").Value = TxtKeyNo.Value
rstAddClient("Keysafe_position").Value = TxtPosition.Value
rstAddClient("Origin_of_data").Value = TxtOrigin.Value
rstAddClient("Checked_against_file").Value = TxtChecked.Value
rstAddClient("Moving_Levels").Value = TxtMoving.Value
rstAddClient("Fault_report_date").Value = TxtFaultReport.Value
rstAddClient("Fault_reason").Value = TxtFaultR.Value
rstAddClient("DS_plan_reviewed").Value = TxtDSPlan.Value
rstAddClient("Day_Support").Value = TxtDaySuppSent.Value
rstAddClient("Notes").Value = TxtNotes.Value
rstAddClient("Area").Value = CboArea.Value
rstAddClient("Alert_Level").Value = CboLevel.Value
rstAddClient("Area").Value = CboArea.Value
rstAddClient("Client_Status").Value = CboStatus.Value
rstAddClient("Referral_Group").Value = CboReferral.Value
rstAddClient("Urgent").Value = CboUrgent.Value
rstAddClient("Type_of_unit_installed").Value = CboUnitType.Value
rstAddClient("Level_Change").Value = CboChange.Value
rstAddClient("Sent_to").Value = CboSent.Value
rstAddClient("Day_support").Value = DaySupport.Value
rstAddClient("GSM_unit_present").Value = GSM.Value
rstAddClient("Install_target_achieved").Value = InstallTarget.Value
rstAddClient("Record_verified").Value = RecordVerified.Value
rstAddClient("Tenure").Value = CboTenure.Value
rstAddClient("HowFunded").Value = CboHowFunded.Value
rstAddClient("costAvoidance").Value = CbocostAvoidance.Value
rstAddClient("Smoke").Value = Smoke.Value
rstAddClient("InfaRed").Value = InfaRed.Value
rstAddClient("FallsIVI").Value = FallsIVI.Value
rstAddClient("Carbon").Value = Carbon.Value
rstAddClient("BogusCaller").Value = BogusCaller.Value
rstAddClient("BedChair").Value = BedChair.Value
rstAddClient("Exit").Value = PropExit.Value
rstAddClient("Temperature").Value = Temperature.Value
rstAddClient("Flood").Value = Flood.Value
rstAddClient("Gas").Value = Gas.Value
rstAddClient("Medication").Value = Medication.Value
rstAddClient("Epilepsy").Value = Epilepsy.Value
rstAddClient("Enuresis").Value = Enuresis.Value
rstAddClient("DDA").Value = DDA.Value
rstAddClient("CareAssist").Value = CareAssist.Value
rstAddClient("NoSensors").Value = NoSensors.Value
rstAddClient("Important").Value = TxtImportant.Value

rstAddClient.Update

'DoCmd.GoToRecord , , acNewRec

End If


End Sub

 

[DavidAtWork]

I can't see anything obvious although I would question how many nested If's & ElseIf's you can use.
Personally I would move the line of code:
Set rstAddClient = dbAddClient.OpenRecordset("ClientInfo")
down to just above the:
rstAddClient.AddNew

Can only suggest stepping through the code and checking the table to see at what point the record is added and then check any subsequent events in the code that follows once the record has been added.

One further point on the nested If's, if there is a limit then you could try using a boolean variable which you set initially to True and then test each textbox or combo separately like this:
If IsNull(TxtForename.Value) Then
MsgBox "Enter a valid Forename", vbExclamation, "Cannot Save"
BooleanVar = False
End If

After checking all textboxes you only need to check the value of the boolean variable and if True, you can:
Set rstAddClient = dbAddClient.OpenRecordset("ClientInfo")
rstAddClient.AddNew
rstAddClient("Title").Value = CboTitle.Value
rstAddClient("Forename").Value = TxtForename.Value
etc

David

 

[jdraw]

In addition to David's comments, you may want to review the
ClientInfo table (or if that is a query; then the table containing Client info) and see what the PK is. You may also want to place a unique compound index on the fields you feel make the Client unique (eg forename,surname,...nationalInsuranceNumber). The unique index would prevent saving duplicates, but you really should solve the underlying problem as is being suggested.

 

[Mihail]

Have you some code under OnCurrent event of the form ?
Or in BeforeUpdate or in AfterUpdate.
Something call twice the CmdAdd_Click procedure.
Do a search in the code (CTRL+F) and find if CmdAdd_Click appear in other places.

 

[AdamCLloyd]

I cant see anything with the code either, but I had a thought, I know it may sound silly, but have we checked that the entry form capturing this data that is validated before added isn't indeed itself bound to the table the data is going into? - That might explain why its getting added twice, once as the user fills out the form because its bound, and then again by the save button?

 

[Galaxiom]

Use a With construct. Value is the default property and can be omitted. Include the Current Class Designator (Me)

With rstAddClient
   .AddNew
   !Title = Me.CboTitle
   !Forename = Me.TxtForename
   etc
   .Update
End With

 

[Galaxiom]

The Else If is not really required. That would allow single line IFs

If IsNull(TxtForename) Then MsgBox "Enter a valid Forename", vbExclamation, "Cannot Save" : Exit Sub
If IsNull(TxtSurname) Then MsgBox "Enter a valid Surname", vbExclamation, "Cannot Save" : Exit Sub

and so on.

No End If at the bottom as they are independent lines.

 

[Galaxiom]

Another way to make the validation code more concise is to write the message into the Tag property of the control and loop through the controls collection.

Dim ctrl As Control

For Each ctrl in Me.Controls
   If Len(ctrl.Tag) > 0 Then
      If IsNull(ctrl) Then MsgBox ctrl.Tag, vbExclamation, "Cannot Save" : Exit Sub
   End If
Next

 

[Galaxiom]

I cant see anything with the code either, but I had a thought, I know it may sound silly, but have we checked that the entry form capturing this data that is validated before added isn't indeed itself bound to the table the data is going into? - That might explain why its getting added twice, once as the user fills out the form because its bound, and then again by the save button?

That is what I expect but I got distracted by the sheer verbosity of the code.

Most repetitive code can be made a lot more concise.

 

[STEVENCV]

Thank you all for your replies.

I am waiting for my colleague to look into your suggestions on Monday and then I'll let you know how we got on.

Thanks again.

 

[STEVENCV]

I just had a thought, after your comments about bound forms saving it.

I took out all the:

rstAddClient.AddNew rstAddClient("Title").Value = CboTitle.Value rstAddClient("Forename").Value = TxtForename.Value rstAddClient("Surname").Value = TxtSurname.Value rstAddClient("Date_of_Birth").Value = TxtDB.Value rstAddClient("Property_Name").Value = TxtProperty.Value rstAddClient("Street").Value = TxtStreet.Value rstAddClient("Village").Value = TxtVillage.Value rstAddClient("Town").Value = TxtTown.Value rstAddClient("City").Value = TxtCity.Value rstAddClient("Postcode").Value = TxtPostcode.Value rstAddClient("TelephoneNo").Value = TxtTelNo.Value rstAddClient("National_Insurance_No").Value = TxtNINO.Value rstAddClient("AlertID").Value = TxtAlertID.Value rstAddClient("SwiftNo").Value = TxtSwiftNo.Value rstAddClient("OldID").Value = TxtOldID.Value rstAddClient("Review_Date").Value = TxtReviewDate.Value rstAddClient("Review_due_date").Value = TxtDueDate.Value rstAddClient("Reviewed_by").Value = TxtReviewedBy.Value rstAddClient("NOK_Name").Value = TxtNOKname1.Value rstAddClient("NOK_telephone_no1").Value = TxtNOKTelNo1.Value rstAddClient("NOK_telephone_no2").Value = TxtNOKTelNo2.Value rstAddClient("Date_of_referral").Value = TxtDateRef.Value rstAddClient("Time_of_Referral").Value = TxtTimeRef.Value rstAddClient("Start_date_of_service").Value = TxtStart.Value rstAddClient("GSM_Unit_Requested").Value = TxtGSMRequest.Value rstAddClient("Install_completion_date").Value = TxtInstall.Value rstAddClient("Installed_by").Value = TxtInstalledby.Value rstAddClient("Reason_not_achieved").Value = TxtReason.Value rstAddClient("End_date").Value = TxtEnd.Value rstAddClient("DeInstall_date").Value = TxtDeInstall.Value rstAddClient("NOK_Name").Value = TxtNOK2Name.Value rstAddClient("NOK2_Telephone_No1").Value = TxtNOK2TelNo1.Value rstAddClient("NOK2_Telephone_No2").Value = TxtNOK2TelNo2.Value rstAddClient("sensor_requested").Value = TxtSensorRequest.Value rstAddClient("Serial_number").Value = TxtSerial.Value rstAddClient("Keysafe_requested").Value = TxtKeyRequest.Value rstAddClient("KeysafeNo").Value = TxtKeyNo.Value rstAddClient("Keysafe_position").Value = TxtPosition.Value rstAddClient("Origin_of_data").Value = TxtOrigin.Value rstAddClient("Checked_against_file").Value = TxtChecked.Value rstAddClient("Moving_Levels").Value = TxtMoving.Value rstAddClient("Fault_report_date").Value = TxtFaultReport.Value rstAddClient("Fault_reason").Value = TxtFaultR.Value rstAddClient("DS_plan_reviewed").Value = TxtDSPlan.Value rstAddClient("Day_Support").Value = TxtDaySuppSent.Value rstAddClient("Notes").Value = TxtNotes.Value rstAddClient("Area").Value = CboArea.Value rstAddClient("Alert_Level").Value = CboLevel.Value rstAddClient("Area").Value = CboArea.Value rstAddClient("Client_Status").Value = CboStatus.Value rstAddClient("Referral_Group").Value = CboReferral.Value rstAddClient("Urgent").Value = CboUrgent.Value rstAddClient("Type_of_unit_installed").Value = CboUnitType.Value rstAddClient("Level_Change").Value = CboChange.Value rstAddClient("Sent_to").Value = CboSent.Value rstAddClient("Day_support").Value = DaySupport.Value rstAddClient("GSM_unit_present").Value = GSM.Value rstAddClient("Install_target_achieved").Value = InstallTarget.Value rstAddClient("Record_verified").Value = RecordVerified.Value rstAddClient("Tenure").Value = CboTenure.Value rstAddClient("HowFunded").Value = CboHowFunded.Value rstAddClient("costAvoidance").Value = CbocostAvoidance.Value rstAddClient("Smoke").Value = Smoke.Value rstAddClient("InfaRed").Value = InfaRed.Value rstAddClient("FallsIVI").Value = FallsIVI.Value rstAddClient("Carbon").Value = Carbon.Value rstAddClient("BogusCaller").Value = BogusCaller.Value rstAddClient("BedChair").Value = BedChair.Value rstAddClient("Exit").Value = PropExit.Value rstAddClient("Temperature").Value = Temperature.Value rstAddClient("Flood").Value = Flood.Value rstAddClient("Gas").Value = Gas.Value rstAddClient("Medication").Value = Medication.Value rstAddClient("Epilepsy").Value = Epilepsy.Value rstAddClient("Enuresis").Value = Enuresis.Value rstAddClient("DDA").Value = DDA.Value rstAddClient("CareAssist").Value = CareAssist.Value rstAddClient("NoSensors").Value = NoSensors.Value rstAddClient("Important").Value = TxtImportant.Value  rstAddClient.Update  'DoCmd.GoToRecord , , acNewRec

And replaced it with:

RunCommand acCmdSaveRecord

This seems to work. With my extremely limited knowledge of code though, I'd be grateful if anyone can see any obvious problems I may have created by doing that?

 

[Mihail]

So... the form with this CmdAdd_Click button is a bounded form ?
If YES I think that we have an explanation for your issue:
When you press this button the code will add the data (by creating a new record). Then, when you move to the new record, Access will save all the data again.
If I am right, then the job is doing even if you have, under this button, a single line:

CmdAdd_Click
   DoCmd.GoToRecord , , acNewRec
End Sub

Test this !