Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-09-09
12
Medium Priority
?
659 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
[X]
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
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 2000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

719 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