Input Validation in QuerySave event

hcl-bot · January 24, 2007, 2:06pm

This is an addition of a previous post that I submitted. In the earlier post, I was receiving errors when I tried to save the form. I moved the input validation formula from a field on the form into the QuerySave event of the form (I added the code below). Now, I do not receive any errors but the validation formula is not validating which means that it allows me to save duplicate locations. Any anyone shed any light on this? Thanks!

Sub Querysave(Source As Notesuidocument, Continue As Variant)

Dim session As New NotesSession

Dim workspace As New NotesUIWorkspace

Dim db As NotesDatabase

Dim docs As NotesDocumentCollection

Dim curDoc As NotesDocument

Dim doc As NotesDocument

Dim searchFormula As String

Set db = session.CurrentDatabase

Set source = workspace.CurrentDocument

Set curDoc = source.Document

searchFormula = { Form = “Location Lookup” & Location = “} + curDoc.Location(0) + {” & Region = “} + curDoc.Region(0) + {” & Division = “} + curDoc.Division(0) + {”}

Set docs = db.Search(searchFormula, Nothing, 0)

Set doc = docs.GetFirstDocument

If docs.Count > 1Then

If (doc.UniversalID = source.Document.UniversalID) Then

Msgbox "A saved location document already exists with this name " + doc.Location(0), 0 + 48, “Duplicate Location”

Continue = False

End If

source.document.Date_Edited = Now

End Sub

hcl-bot · January 24, 2007, 2:19pm

Subject: Input Validation in QuerySave event

If you’re creating a new document this would fail because of the line ‘If docs.Count > 1’… which would mean 2 or more.

Maybe you should have ‘If docs.Count > 0’

Alex

hcl-bot · January 24, 2007, 2:33pm

Subject: RE: Input Validation in QuerySave event

Alex, thanks for your response. I modified the code as you suggested by replacing If docs.Count > 1 with If docs.Count > 0. The code did not stop there as it should and allowed me to save the duplicate document. Basically, what I hope to do is to see if the data in those fields match the data in other documents and if it does, then stop the save and give an error saying that there is already another document. Any ideas will be appreciated.

hcl-bot · January 24, 2007, 3:39pm

Subject: Input Validation in QuerySave event

Part of the reason is that you are using a full text search, and the dB may not have been indexed, therefore you will not find anything, even if if does exits.

I would suggest using a view instead, and do a getalldocumentsBykey,

if the count is > 0, then loop thru the documents int he collection and if any universal id’s are different than what you have, then you have a duplicate.

John

hcl-bot · January 24, 2007, 4:12pm

Subject: Input Validation in QuerySave event

I think your problem is:

If (doc.UniversalID = source.Document.UniversalID) Then

What you want to check is whether a different document has the same key, so you should be checking for NOT equals.

Simon

hcl-bot · January 25, 2007, 12:43pm

Subject: RE: Input Validation in QuerySave event

Simon, thank you for your response. I realized the problem you mentioned right after I posted my last post. However, after modifying the code and testing it, I am still able to save a duplicate document when composing a new document and entering the same data as another document. I am wondering if I should go a different direction than what I’m doing. I added the code below and maybe someone can catch my mistake (if there is any).

Dim session As New NotesSession

Dim workspace As New NotesUIWorkspace

Dim db As NotesDatabase

Dim view As NotesView

Dim docs As NotesDocumentCollection

Dim curDoc As NotesDocument

Dim doc As NotesDocument

Dim searchFormula As String

Dim items As String

On Error Goto errorHandler

Set db = session.CurrentDatabase

Set view = db.GetView(“Location Lookup”)

Set source = workspace.CurrentDocument

Set curDoc = source.Document

searchFormula = { Form = “Location Lookup” & Location=“} & curDoc.Location(0) & {” & Region=“} & curDoc.Region(0) & {” & Division=“} & curDoc.Division(0) & {”}

Set docs = db.Search(searchFormula, Nothing, 0)

If docs.Count > 0 Then

Set doc = docs.GetFirstDocument

If Not (doc.UniversalID = source.Document.UniversalID) Then

Msgbox "A saved location document already exists with this name " + doc.Location(0), 0 + 48, “Duplicate Location”

Continue = False

Exit Sub

End If

items = curDoc.Location(0) + curDoc.Region(0) + CurDoc.Division(0)

Set doc = view.GetDocumentByKey(items)

If Not (doc Is Nothing) Then

source.Document.Date_Edited = Now

Call curDoc.Save(True, True)

End If

errorHandler:

Msgbox "There is an error - " & Error & " - in Location Lookup Form:QuerySave Event on the line: " & Erl

hcl-bot · January 25, 2007, 1:18pm

Subject: RE: Input Validation in QuerySave event

Have you tried creating a new document, exiting the database, re-opening it and then creating another document with the same details? What happens then?

It’s possible that using the db.Search method the results are being cached.

Also in the selection formula the Form is set to ‘Location Lookup’. Do you have an alias name for the Form in the form Properties? If so make sure the alias name is put into the formula instead.

Alex

hcl-bot · January 25, 2007, 1:38pm

Subject: RE: Input Validation in QuerySave event

Alex, I did what you suggested and here’s what happened.

I created a new document and entered data into the 3 fields. After saving the document, I received an error message saying that there’s an error on line 0 but the message did not tell me what the exact error is.
I closed the database and reopened it again.
I created a new document and entered the same data as the previous document. After saving the document, I received the error where it says that there’s another document with the same location (This is the error I’m expecting to get). After I clicked, “OK”, I received a message saying if I want to save the document and I clicked “No”. After that, the document closed. (When I click “Yes”, the correct error message reappears)
I opened the first document I created, and tried to edit the Region field, I received an error message saying that there’s an error on line 0 but did not say what the error is.

Additionally, there is a input validation formula in the Location and Region fields where they are required (EX: @If(Location = “”; @Failure(“Location is required”); @Success). I’m not sure if this is related to the problem but thought I should mention that. Any suggestions will be appreciated!

hcl-bot · January 25, 2007, 8:44pm

Subject: RE: Input Validation in QuerySave event

Sounds like you’re getting there…

I would probably put the following bit of code in your QuerySave code after the line “Set source = workspace.CurrentDocument” so that if a validation formula returns an error then the rest of your script doesn’t get execute:

If Not Source.Refresh Then

Exit Sub

End If

Now your Save button on your form should have a formula similar to:

@If(

@Command([FileSave]);

    @Command([FileCloseWindow]);

""

)

So the document will only save & close if all validations succeed.

To get better error handling in your QuerySave code put this line of code after your declarations (after the line Dim searchFormula As String)

On Error Goto Error_Handler

Then at the end of the script (before End Sub) put:

Exit Sub

Error_Handler:

Dim NewLine As String

NewLine = Chr(10) & Chr(13)

Messagebox "The following error occurred: " & NewLine & "Error number: " & Cstr(Err) & NewLine & "Line number: " & Cstr(Erl) & NewLine & "Description: " Error$, 0+16, "An error has occurred"

Exit Sub

Hope this helps a little more.

Alex