Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2004-09-09
12
642 Views
Last Modified: 2012-05-05
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
Comment
Question by:amrnorman
  • 5
  • 4
  • 3
12 Comments
 
LVL 6

Expert Comment

by:mcp111
ID: 12019393
do while isnull(creationdate)

inputbox....
inputbox...
loop
0
 

Author Comment

by:amrnorman
ID: 12019539
Unfortunately, the suggestion does not work.  I added the code and now the input boxes do not appear at all.
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12019753
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 6

Expert Comment

by:mcp111
ID: 12020054
Add

Set creationdate = Null

before the do loop and it will work.
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12020115
No it will not, since the loop condition is whether records were found, not whether anything was entered.
0
 
LVL 6

Expert Comment

by:mcp111
ID: 12020259
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
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12020295
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
 

Author Comment

by:amrnorman
ID: 12021221
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
 
LVL 18

Accepted Solution

by:
SjoerdVerweij earned 500 total points
ID: 12021263
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
 

Author Comment

by:amrnorman
ID: 12021304
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
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12021544
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
 

Author Comment

by:amrnorman
ID: 12022381
SjoerdVerweij,

All is well.  Thank you!
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes some very basic things about SQL Server filegroups.
Read about achieving the basic levels of HRIS security in the workplace.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question