Solved

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

Posted on 2004-09-09
12
613 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
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…

746 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

12 Experts available now in Live!

Get 1:1 Help Now