Change Code from IF statement to CASE statement (1 Viewer)

P

papic1972

Registered User.
Local time
Today, 09:23
Joined
Apr 14, 2004
Messages
122
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

boblarson

Smeghead
Local time
Yesterday, 16:23
Joined
Jan 12, 2001
Messages
32,059
Code: 
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

RuralGuy

AWF VIP
Local time
Yesterday, 17:23
Joined
Jul 2, 2005
Messages
13,826
Try something like this:
Code: 
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
 
Last edited:
boblarson

boblarson

Smeghead
Local time
Yesterday, 16:23
Joined
Jan 12, 2001
Messages
32,059
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

RuralGuy

AWF VIP
Local time
Yesterday, 17:23
Joined
Jul 2, 2005
Messages
13,826
boblarson said:
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.
Click to expand...
Agreed and done.
 
P

papic1972

Registered User.
Local time
Today, 09:23
Joined
Apr 14, 2004
Messages
122
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?
 
P

papic1972

Registered User.
Local time
Today, 09:23
Joined
Apr 14, 2004
Messages
122
Please disregard the previous post, i've got it working!!!!!!! (It was my typing error!)

Thank you!!!
 
You must log in or register to reply here.

Similar threads

D
Reducing Same Code in If and ElseIf Statement
Replies
4
Views
933
Pat Hartman
P
M
Solved Not able to delete files in folder
Replies
2
Views
540
The_Doc_Man
The_Doc_Man
swell
Cross tab query not recognising field name.
2
Replies
27
Views
2,574
swell
swell
D
Solved Unable to save form record
2
Replies
27
Views
2,620
Pat Hartman
P
L
alter a boolean field from no to yes with VBA
Replies
1
Views
1,554
pbaldy
pbaldy

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom