Solved

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

Posted on 2004-09-09
12
624 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
 
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need way to extend view in MS Access 47 73
Linking dictionary and columns together 2 61
Data quality checks 2 49
How to SQL Trace a SPECIFIC query 24 57
In today’s complex data management environments, it is not unusual for UNIX servers to be dedicated to a particular department, purpose, or database.  As a result, a SAS® data analyst often works with multiple servers, each with its own data storage…
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
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…

912 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now