• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 669
  • Last Modified:

VBA code - loop for inputbox with query string (records found vs. no records found)

Hello,

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) & "))"
 
     With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .MailAsAttachment = False
        .MailAddressFieldName = ""
        .MailSubject = ""
        .SuppressBlankLines = False
    With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
           
        End With
        .Execute Pause:=True
                     
    End With
 
 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
 End If
 On Error GoTo 0

   
End Sub

0
amrnorman
Asked:
amrnorman
  • 5
  • 4
  • 3
1 Solution
 
Partha MandayamTechnical DirectorCommented:
do while isnull(creationdate)

inputbox....
inputbox...
loop
0
 
amrnormanAuthor Commented:
Unfortunately, the suggestion does not work.  I added the code and now the input boxes do not appear at all.
0
 
SjoerdVerweijCommented:
Private Sub Document_Open()

Dim CreationDate As Variant, Initials As Variant, ErrorNumber As Long

Do

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) & "))"
 
     With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .MailAsAttachment = False
        .MailAddressFieldName = ""
        .MailSubject = ""
        .SuppressBlankLines = False
    With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
           
        End With
        .Execute Pause:=True
                     
    End With

 ErrorNumber = Err.Number

 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
 End If
 On Error GoTo 0

Loop Until ErrorNumber = 0
   
End Sub
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Partha MandayamTechnical DirectorCommented:
Add

Set creationdate = Null

before the do loop and it will work.
0
 
SjoerdVerweijCommented:
No it will not, since the loop condition is whether records were found, not whether anything was entered.
0
 
Partha MandayamTechnical DirectorCommented:
You are in the wrong loop :-)

I was talking about my do loop

I meant

dim creationdate
Set creationdate = Null
do while isnull(creationdate)

inputbox....
inputbox...
loop

0
 
SjoerdVerweijCommented:
Again, please re-read the question:

"I need to know how to keep prompting the user for input until there is a record match found."

That is not what you are doing.
0
 
amrnormanAuthor Commented:
SjoerdVerweij,

Thank you. Your code works great.  Can you tell me how to put in an exit point for the user when he/she is presented with the message that no records were found?  This would apply for situations where the user would knowingly never get a return record.  I don't want the user to be in a endless loop in that situation.  Maybe a button could be added to the message box to exit?  I've read something about using Case.  Would this be appropriate? I do not know how to code that.  Could you provide the code that would allow me to finalize my request?  Please let me know.  Thanks.
0
 
SjoerdVerweijCommented:
Private Sub Document_Open()

Dim CreationDate As Variant, Initials As Variant, ErrorNumber As Long

Do

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) & "))"
 
     With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .MailAsAttachment = False
        .MailAddressFieldName = ""
        .MailSubject = ""
        .SuppressBlankLines = False
    With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
           
        End With
        .Execute Pause:=True
                     
    End With

 ErrorNumber = Err.Number

 If ErrorNumber <> 0 Then
   If MsgBox("There were no records that matched your search criteria.  Do you want to try again?", vbQuestion Or vbYesNo, "No records found") = vbNo
     ErrorNumber = 0
   End If
 Else
   ThisDocument.Close SaveChanges:=wdDoNotSaveChanges
 End If
 On Error GoTo 0

Loop Until ErrorNumber = 0
   
End Sub
0
 
amrnormanAuthor Commented:
SjoerdVerweij,

Syntax error on the following line:

  If MsgBox("There were no records that matched your search criteria.  Do you want to try again?", vbQuestion Or vbYesNo, "No records found") = vbNo
0
 
SjoerdVerweijCommented:
Oops.

If MsgBox("There were no records that matched your search criteria.  Do you want to try again?", vbQuestion Or vbYesNo, "No records found") = vbNo Then
0
 
amrnormanAuthor Commented:
SjoerdVerweij,

All is well.  Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now