InputBox with Loop issue

lryckman · April 7th, 2009, 04:50 PM

Hello!
I have a slight issue...I have the code below, but if the user clicks on the cancel button, it will not cancel out of the loop...I can't remember how to fix this...any ideas?

Code:

 
    Dim ReportSize As String
 
    DoCmd.RunCommand acCmdSaveRecord
    ReportSize = InputBox("Please enter the number cooresponding to the paper size " _
    & "you wish to print on." & _
    vbCrLf & "1 - Letter" & _
    vbCrLf & "2 - A4", "Please choose your paper size")
 
    Do While ReportSize <> "1" And ReportSize <> "2"
 
        ReportSize = InputBox("Please enter the number cooresponding to the paper size " _
        & "you wish to print on." & _
        vbCrLf & "1 - Letter" & _
        vbCrLf & "2 - A4", "Please choose your paper size")
 
    Loop
 
    If ReportSize = "1" Then
 
        OpenReport_FX "rptECNForm", acViewNormal, "", _
        "[CCNNumber]=[Forms]![frm2009_11]![CCNNumber]"
 
    End If
 
    If ReportSize = "2" Then
 
        OpenReport_FX "rptECNForm_A4", acViewNormal, "", _
        "[CCNNumber]=[Forms]![frm2009_11]![CCNNumber]"
 
    End If
 
    Exit Sub

Thanks in advance for any help.

Laura

mmcdonal · April 8th, 2009, 08:00 AM

I am not sure why you want to force the user to input a string when they click cancel by keeping them in a loop. But you could do this:

Code:

If ReportSize <> "" Then
   Do While ReportSize <> "1" And ReportSize <> "2"
 
        ReportSize = InputBox("Please enter the number cooresponding to the paper size " _
        & "you wish to print on." & _
        vbCrLf & "1 - Letter" & _
        vbCrLf & "2 - A4", "Please choose your paper size")
 
    Loop
End If

This would force them into a loop if they entered anything in the input box, but I am not sure why a user would enter anything other than 1 or 2. Can you give them a pop-up modal form with radio buttons and cancel instead?

Did that help?

mmcdonal · April 8th, 2009, 08:06 AM

Also, I would do this:

Code:

Dim sMessage As String
Dim sTitle As String
 
sMessage = "Please enter the number cooresponding to the paper size " _
& "you wish to print on." & _
vbCrLf & "1 - Letter" & _
vbCrLf & "2 - A4"
 
sTitle = "Please choose your paper size"
 
DoCmd.RunCommand acCmdSaveRecord
ReportSize = InputBox(sMessage, sTitle)

That way if you have to change the message, you don't have to go hunting for it in the code. You have a misspelling here, so when you have to change it, you will have to change it twice.

HTH

SerranoG · April 8th, 2009, 05:16 PM

How about?

Code:

    Dim strReportSize As String
    Dim strTitle As String, strMessage as String
 
    DoCmd.RunCommand acCmdSaveRecord
 
    strReportSize = "Unknown"
 
    Do While strReportSize <> "1" And strReportSize <> "2" And _
        Len(strReportSize) > 0
 
        strMessage = "Please enter the number corresponding " & _
            " to the paper size you wish to print on." & _
            vbCrLf & vbCrLf & "1 - Letter" & vbCrLf & "2 - A4"
 
        strTitle = "Please choose your paper size."
 
        strReportSize = Trim(InputBox(strMessage, strTitle, 1))
    Loop
 
    Select Case strReportSize
        Case "1"
            OpenReport_FX "rptECNForm", acViewNormal, "", _
                "[CCNNumber] = [Forms]![frm2009_11]![CCNNumber]"
        Case "2"
            OpenReport_FX "rptECNForm_A4", acViewNormal, "", _
                "[CCNNumber]=[Forms]![frm2009_11]![CCNNumber]"
        Case Else
            MsgBox "Operation Canceled.", vbInformation, "Open Report"
    End If

Note: I've not tested this. The indenting will look funny in your e-mail. Look at this on the web to view it in the code box.

boblarson · April 8th, 2009, 07:31 PM

If I read this correctly, I am seeing it differently than anyone else. I thought you wanted to let them cancel out if they choose cancel. The simplest way is just to change this part:

Code:

 ReportSize = InputBox("Please enter the number cooresponding to the paper size " _
    & "you wish to print on." & _
    vbCrLf & "1 - Letter" & _
    vbCrLf & "2 - A4", "Please choose your paper size")

to this:

Code:

 ReportSize = InputBox("Please enter the number cooresponding to the paper size " _
    & "you wish to print on." & _
    vbCrLf & "1 - Letter" & _
    vbCrLf & "2 - A4", "Please choose your paper size")
If ReportSize = "" Then
  MsgBox "Selection Canceled", vbInformation, "Cancel"
  Exit Sub
End If

mmcdonal · April 9th, 2009, 07:19 AM

Yes, that is what I obtained with

If ReportSize <> "" Then
...
End If

The post was to prevent the users from getting into an infinite loop (allowing them to cancel.) There should have been an Exit Sub there.

SerranoG · April 9th, 2009, 07:44 AM

The code I posted should do the same except I try to avoid Exit Subs as much as possible. I don't like code that just bails out midway. I prefer "if the condition doesn't apply to you, then skip it and continue to the next code, even if that's an End Sub."

Just a different mindset.

lryckman · April 9th, 2009, 08:12 AM

Everyone's help is much appreciated. This is working correctly now allowing the user to cancel out of the loop.

Thank you!
Laura