VBA code - loop for inputbox with query string (records found vs. no records found)
Posted on 2004-09-09
I need to know how to keep prompting the user for input until there is a record match found. Also, it would be a nice feature to also give the user the option of totally exiting out of the document (especially if they know that there will never be a match). What is the best way to do this? Please provide the necessary code. Thanks.
My code so far is:
Private Sub Document_Open()
Dim CreationDate As Variant, Initials As Variant
CreationDate = InputBox("Please enter the letter creation date below:" & vbCrLf & _
vbCrLf & "Date Format: mm/dd/yy", "Und15 Letter - Input Required")
If IsNull(CreationDate) Then Exit Sub
Initials = InputBox("Please enter your initials below:", "Und15 Letter - Input Required")
If IsNull(Initials) Then Exit Sub
On Error Resume Next
ActiveDocument.MailMerge.DataSource.QueryString = _
"SELECT * FROM [Und15] WHERE (([CreateDate] = #" & CreationDate & "#) AND ([Typist] = " & Chr(34) & Initials & Chr(34) & "))"
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = False
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
If Err.Number <> 0 Then
MsgBox "There were no records that matched your search criteria. Please try again.", vbExclamation, "No Records Found!"
Else: ThisDocument.Close SaveChanges:=wdDoNotSaveChanges
On Error GoTo 0