I have a problem with LotusScript which is giving me a serious headache!
I want to stop users from creating duplicate records on my database. So I have written a small LotusScript chunk which runs on the documents QuerySave. Basically, on saving the document, it goes away and quickly searches the database for a matching document (basing the results on matching three fields).
If a duplicate document is found, it should close the one they have just created and opens the one found in the search instead.
The code looks like this:
Sub Querysave(Source As Notesuidocument, Continue As Variant)
'This script checks to see if a similar or identical event is already entered for the offender
'It uses the following fields to determine this:
'evtSentDate
'evtSentCourt
'offCRN
'If a duplicate event is found, the user is warned, and the new event is
'abandoned. If no match is found, the save is committed to the database.
Dim ws As New NotesUIWorkspace
Dim db As NotesDatabase
Dim session As New NotesSession
Dim searchresults As NotesDocumentCollection
Dim doc As NotesDocument
Dim existingdoc As NotesDocument
Dim newuidoc As NotesUIDocument
Dim formula As String
Dim dateTime As New NotesDateTime("")
Set db = session.currentdatabase
'Get handle on backend document
Set doc = Source.Document
'Create a db search formula to search for existing matching events
formula = "FORM=""frmEvent"" & offCRN = """ + doc.OffCRN(0) + """ & evtSentDate = @TextToTime(""" + Str(doc.evtSentDate(0)) + """) & evtSentCourt = """ + doc.evtSentCourt(0) +""""
Set searchresults = db.search(formula, dateTime, 0)
If searchresults.count>0 Then
'tell them to clear off!
response=Messagebox("A duplicate event has been found - this new event you have created will be deleted and the existing one used instead", 0, "Duplicate event")
doc.SaveOptions="0"
Continue=False
Set existingdoc = searchresults.GetFirstDocument
Set newuidoc = ws.EditDocument(False, existingdoc, False)
Exit Sub
Else
'Save and get out
Continue=True
End If
End Sub
Now, the really squirley part about this is that it runs and performs perfectly in Debug mode. However, if I run it live (with Debug turned off) it closes the WRONG document. It does appear to open the document it found during the search, but then closes it again straight away.
Anyone any ideas? I would even consider a completely different approach if anyone has got one!
Simon