Excel Template Problem

[EliseFreedman]

I have a form which I created some time ago in excel. It is used to record details of patient appointments. This data is then collect using the template wizard in a excel spreadsheet. The idea being that new forms can be created or alternatively old forms can be loaded and edited and the existing record over-written in the main spreadsheet. It has worked successfully for a few months.

Suddenly, the first time that an existing form is opened and edited , the user gets a message saying that the file already exists and do they want to replace it. I would expect this. However, they then get a message saying that the form does not exist in the database, do they want to create a new record. Im a bit confused as the record already exists. However, they are not given the chance to update the existing record .If I say create a new record therefore creating a duplicate record in the spreadsheet, I finally get the option to update the existing record. Of course, I dont want to end up with a large volume of duplicate records. Can anyone shed any light on this. My code used to save the record is below

Application.EnableEvents = False
Dim response As String
Dim Name As String
Dim TreatmentDate As Date
Dim TreatmentDate2 As String
Dim AppointmentType As String
TreatmentDate = Range("R18")
TreatmentDate2 = Format(TreatmentDate, "ddmmmmyyyy")
Name = Range("H12")
Name2 = Trim(Name)
AppointmentType = Range("R20")

If AppointmentType = "" Then
MsgBox "You must enter an appointment type before proceeding", vbCritical
Exit Sub
Else

ActiveWorkbook.SaveAs Filename:="M:\depts\HS&ES\Safety\Physio Stats\Form\TreatmentRecords\PhysiotherapyTreatmentRecord" & Name2 & TreatmentDate2 & ".xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=True
Application.Run "Commit"
Application.EnableEvents = True

End If 
End Sub

 

[SkipVought]

Elsie,

I am confused.

Your code has nothing to do with records. It has to do with saving a workbook with a particular name. If the Name & TreatementDate is identical to an existing workbook name, sure, you'll get a message.

But what's this about RECORDS? Totally different concept.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[strongm]

I suspect there's something in the 'Commit' macro that we need to see ...

 

[SkipVought]

Yes, post Commit.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}