Change Code from IF statement to CASE statement

[papic1972]

Hi All,

Is it possible to change the code below from IF statements to CASE statements? I'm seriously stuck with how to go about this!!!

In summary, the code is triggered from the after update event of my form's checkbox 'Check25970'. I have a field called 'OrderID' on my form & if the number in the field 'OrderID' is between a particular number (say for example between 45001 AND 47500), then a PDF version of report 'rptInvoice' is produced & automatically stored in a specific folder on my C: drive (i.e C:\45000-47501).



If Me.Check25970 = True AND (Me.OrderID >=45001 AND Me.OrderID <=47500) Then
Me.Check25976 = False
blRet = ConvertReportToPDF("rptInvoice", vbNullString, "C:\45001-47500\" & Me.OrderID & "CI" & ".pdf", False, False, 0, "", "", 0, 0)
DoCmd.OpenReport stDocName, acPreview

Else

If Me.Check25970 = True AND (Me.OrderID >=47501 AND Me.OrderID <=50000) Then
Me.Check25976 = False
blRet = ConvertReportToPDF("rptInvoice", vbNullString, "C:\47500-50000\" & Me.OrderID & "CI" & ".pdf", False, False, 0, "", "", 0, 0)
DoCmd.OpenReport stDocName, acPreview

Else

If Me.Check25970 = True AND (Me.OrderID >=50001 AND Me.OrderID <=52500) Then
Me.Check25976 = False
blRet = ConvertReportToPDF("rptInvoice", vbNullString, "C:\50001-52500\" & Me.OrderID & "CI" & ".pdf", False, False, 0, "", "", 0, 0)
DoCmd.OpenReport stDocName, acPreview

Else

If Me.Check25970 = True AND (Me.OrderID >=52501 AND Me.OrderID <=55000) Then
Me.Check25976 = False
blRet = ConvertReportToPDF("rptInvoice", vbNullString, "C:\52501-55000\" & Me.OrderID & "CI" & ".pdf", False, False, 0, "", "", 0, 0)
DoCmd.OpenReport stDocName, acPreview

End If
End If
End If
End If

 

[boblarson]

If Me.Check25970 = True Then
   Select Case Me.OrderID
      Case Is >=45001 AND <=47500
         blRet = ConvertReportToPDF("rptInvoice", vbNullString, "C:\45001-47500\" & Me.OrderID & "CI" & ".pdf", False, False, 0, "", "", 0, 0)
      Case Is >=47501 AND <=50000
         blRet = ConvertReportToPDF("rptInvoice", vbNullString, "C:\47500-50000\" & Me.OrderID & "CI" & ".pdf", False, False, 0, "", "", 0, 0)
      Case Is >=50001 AND <=52500
         blRet = ConvertReportToPDF("rptInvoice", vbNullString, "C:\50001-52500\" & Me.OrderID & "CI" & ".pdf", False, False, 0, "", "", 0, 0)
      Case Is >=52501 AND <=55000
         blRet = ConvertReportToPDF("rptInvoice", vbNullString, "C:\52501-55000\" & Me.OrderID & "CI" & ".pdf", False, False, 0, "", "", 0, 0)
      Case Else
         Msgbox "Not valid"
   End Select      
      Me.Check25976 = False
      DoCmd.OpenReport stDocName, acPreview
End If

I don't see where you assign stDocName, but you can assign it wherever and use it once.

 

[RuralGuy]

Try something like this:

Private Sub Check25970_AfterUpdate()

   If Me.Check25970 = True Then

      Select Case Me.OrderID

         Case 45001 To 47500
            blRet = ConvertReportToPDF("rptInvoice", vbNullString, _
                                       "C:\45001-47500\" & Me.OrderID & "CI" & ".pdf", _
                                       False, False, 0, "", "", 0, 0)
         Case 47501 To 50000
            blRet = ConvertReportToPDF("rptInvoice", vbNullString, _
                                       "C:\47500-50000\" & Me.OrderID & "CI" & ".pdf", _
                                       False, False, 0, "", "", 0, 0)
         Case 50001 To 52500
            blRet = ConvertReportToPDF("rptInvoice", vbNullString, _
                                       "C:\50001-52500\" & Me.OrderID & "CI" & ".pdf", _
                                       False, False, 0, "", "", 0, 0)
         Case 52501 To 55000
            blRet = ConvertReportToPDF("rptInvoice", vbNullString, _
                                       "C:\52501-55000\" & Me.OrderID & "CI" & ".pdf", _
                                       False, False, 0, "", "", 0, 0)
         Case Else
            '-- Do whatever you need to do if the value is out of range
      End Select

      Me.Check25976 = False      
      DoCmd.OpenReport stDocName, acPreview

   Else
      '-- Checkbox is not True
   End If

End Sub

 

[boblarson]

Yeah, the Case XXX to XXX is good. Although with that you would still want to remove the redundant Me.Check25976 and DoCmd.OpenReport code. One time is good for those two as it is exact duplicates for every case.

 

[RuralGuy]

Yeah, the Case XXX to XXX is good. Although with that you would still want to remove the redundant Me.Check25976 and DoCmd.OpenReport code. One time is good for those two as it is exact duplicates for every case.

Agreed and done.

 

[papic1972]

Hi Guys,

Thank you for your efforts, i've tried your suggestion but the code does not fire at all, it does nothing. Is there something missing from your code?

 

[papic1972]

Please disregard the previous post, i've got it working!!!!!!! (It was my typing error!)

Thank you!!!