Case Method Issue: Whats Wrong Here

A

Adam McReynolds

Registered User.
Local time
Today, 15:21
Joined
Aug 6, 2012
Messages
129
My code won't recognize my cases using "Left" but it fires on the "Case Else" options. What am I missing? Second, any suggestions to make this code shorter?

This code is firing on a Serial Number Field in a datasheet form.
Code: 
Private Sub Incoming_Module_Sn_AfterUpdate()
Dim warrantyCheck As Date

Select Case Me.customer
''''''CASE ALPHA CUSTOMER
    Case "Alpha"
    
        Select Case Me.end_user
            Case Left(Me.end_user, 2) = "TW"
                'BEG 6 MONTHS ''''''''''''''''''''''''''''''''''''
                If Me.Incoming_Disposition = "Estimate" Then
                If Not IsNull(DLookup("Incoming_Module_Sn", "tbl_Module_Repairs", "Incoming_Module_Sn = '" & Me.Incoming_Module_Sn.Text & "'")) Then
                 
                  If IsNull(DMax("[complete_date]", "tbl_Module_Repairs", "Incoming_Module_Sn = '" & Me.Incoming_Module_Sn.Text & "'")) Then
                     MsgBox "No Complete date found", vbOKOnly + vbCritical
                     Exit Sub
                     Else
                          warrantyCheck = DMax("[complete_date]", "tbl_Module_Repairs", "Incoming_Module_Sn = '" & Me.Incoming_Module_Sn.Text & "' And [Incoming_Disposition] =  'Completed'")
                        If Date - warrantyCheck < 190 Then ' approx 183 + 7 (6 months + a week)
                          If MsgBox("This unit is still under warranty and was last completed on " & warrantyCheck & ". Would you like to mark this as a warranty?", vbYesNo, "Duplicate Warning") = vbYes Then
                             Me.Incoming_Disposition = "Warranty"
                          End If
                        End If
                    End If
                End If
                End If
                'END 6 MONTHS'''''''''''''''''''''''''''''''''''''''''''''
            Case Left(Me.end_user, 4) = "Char"
                ' BEG 1 YEAR''''''''''''''''''''''''''''''''''''
                If Me.Incoming_Disposition = "Estimate" Then
                If Not IsNull(DLookup("Incoming_Module_Sn", "tbl_Module_Repairs", "Incoming_Module_Sn = '" & Me.Incoming_Module_Sn.Text & "'")) Then
                 
                  If IsNull(DMax("[complete_date]", "tbl_Module_Repairs", "Incoming_Module_Sn = '" & Me.Incoming_Module_Sn.Text & "'")) Then
                     MsgBox "No Complete date found", vbOKOnly + vbCritical
                     Exit Sub
                     Else
                          warrantyCheck = DMax("[complete_date]", "tbl_Module_Repairs", "Incoming_Module_Sn = '" & Me.Incoming_Module_Sn.Text & "' And [Incoming_Disposition] =  'Completed'")
                        If Date - warrantyCheck < 372 Then ' approx 365 + 7 (1 Year + a week)
                          If MsgBox("This unit is still under warranty and was last completed on " & warrantyCheck & ". Would you like to mark this as a warranty?", vbYesNo, "Duplicate Warning") = vbYes Then
                             Me.Incoming_Disposition = "Warranty"
                          End If
                        End If
                    End If
                End If
                End If
                'END 1 YEAR'''''''''''''''''''''''''''''''''''''''''''''
            Case Else
                'BEG 6 MONTHS''''''''''''''''''''''''''''''''''''
                If Me.Incoming_Disposition = "Estimate" Then
                If Not IsNull(DLookup("Incoming_Module_Sn", "tbl_Module_Repairs", "Incoming_Module_Sn = '" & Me.Incoming_Module_Sn.Text & "'")) Then
                 
                  If IsNull(DMax("[complete_date]", "tbl_Module_Repairs", "Incoming_Module_Sn = '" & Me.Incoming_Module_Sn.Text & "'")) Then
                     MsgBox "No Complete date found", vbOKOnly + vbCritical
                     Exit Sub
                     Else
                          warrantyCheck = DMax("[complete_date]", "tbl_Module_Repairs", "Incoming_Module_Sn = '" & Me.Incoming_Module_Sn.Text & "' And [Incoming_Disposition] =  'Completed'")
                        If Date - warrantyCheck < 190 Then ' approx 183 + 7 (6 months + a week)
                          If MsgBox("This unit is still under warranty and was last completed on " & warrantyCheck & ". Would you like to mark this as a warranty?", vbYesNo, "Duplicate Warning") = vbYes Then
                             Me.Incoming_Disposition = "Warranty"
                          End If
                        End If
                    End If
                End If
                End If
                'END 6 MONTHS'''''''''''''''''''''''''''''''''''''''''''''
           End Select
''''''CASE COX CUSTOMER
    Case Left(Me.customer, 3) = Cox
        ' BEG 1 YEAR''''''''''''''''''''''''''''''''''''
        If Me.Incoming_Disposition = "Estimate" Then
        If Not IsNull(DLookup("Incoming_Module_Sn", "tbl_Module_Repairs", "Incoming_Module_Sn = '" & Me.Incoming_Module_Sn.Text & "'")) Then
         
          If IsNull(DMax("[complete_date]", "tbl_Module_Repairs", "Incoming_Module_Sn = '" & Me.Incoming_Module_Sn.Text & "'")) Then
             MsgBox "No Complete date found", vbOKOnly + vbCritical
             Exit Sub
             Else
                  warrantyCheck = DMax("[complete_date]", "tbl_Module_Repairs", "Incoming_Module_Sn = '" & Me.Incoming_Module_Sn.Text & "' And [Incoming_Disposition] =  'Completed'")
                If Date - warrantyCheck < 372 Then ' approx 365 + 7 (6 months + a week)
                  If MsgBox("This unit is still under warranty and was last completed on " & warrantyCheck & ". Would you like to mark this as a warranty?", vbYesNo, "Duplicate Warning") = vbYes Then
                     Me.Incoming_Disposition = "Warranty"
                  End If
                End If
            End If
        End If
        End If
        'END 1 YEAR'''''''''''''''''''''''''''''''''''''''''''''
    Case Else
            If Nz(DCount("incoming_module_sn", "tbl_Module_Repairs", " incoming_module_sn = '" & Me.Incoming_Module_Sn.Text & "'"), 0) > 0 Then
        If MsgBox("The Serial Number already exists. Check Warranty! Verify the warranty period for this customer. Do you wish to continue?", vbOKCancel, "Duplicate Warning") = vbCancel Then
            Cancel = True
            Me.Undo
            Exit Sub
        End If
    End If
End Select

End Sub
 
It looks like Me.end_user should return as string
but
Left(Me.end_user, 2) = "TW" should return True or False

have you tried:-
Select Case True

Chris.
 
Isn't this one of those situations where it would be simpler to use If.. Then block

Should Cox be in quotes.

Brian
 
Too much code.

There is a need to isolate what we are going to do from when we are going to do it.
Try to look at the logical structure before even trying to fill in the blanks.

Simplify, test and expand:-

Code: 
Option Compare Database
Option Explicit


Private Sub Incoming_Module_Sn_AfterUpdate()

    Select Case Me.Customer
        Case "Alpha"
            Select Case True
                Case Left(Nz(Me.End_User), 2) = "TW"
                    MsgBox "End_User starts with 'TW'"
                
                Case Left(Nz(Me.End_User), 4) = "Char"
                    MsgBox "End_User starts with 'Char'"
                
                Case Else
                    MsgBox "End_User does not start with 'TW' or 'Char'"
               
           End Select
           
        Case Else
            MsgBox "Customer is not 'Alpha'"
        
    End Select

End Sub

Despite what even Microsoft says, there is very little difference between the ‘If Then Else’ and the ‘Select Case’ structures but the ‘Select Case’ may have the advantage in readability. Probably boils down to what you are used to.

Chris.
 
Ok, so I finally have had time to get back to this. Thank you to everyone for their help. Chris's method seemed to do the trick and here is what I got from that:
Code: 
Private Sub Incoming_Module_Sn_AfterUpdate()
Dim warrantyCheck As Date
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If Me.Incoming_Disposition = "Estimate" Then
    If Nz(DCount("incoming_module_sn", "tbl_Module_Repairs", " incoming_module_sn = '" & Me.Incoming_Module_Sn.Text & "'"), 0) > 0 Then
    
    Select Case True
        Case Me.customer = "Alpha"
            Select Case True
                Case Left(Nz(Me.end_user), 2) = "TW"
                'BEG 6 MONTHS ''''''''''''''''''''''''''''''''''''
                    If IsNull(DMax("[complete_date]", "tbl_Module_Repairs", "Incoming_Module_Sn = '" & Me.Incoming_Module_Sn.Text & "'")) Then
                     MsgBox "No Complete date found", vbOKOnly + vbCritical
                     Exit Sub
                     Else
                          warrantyCheck = DMax("[complete_date]", "tbl_Module_Repairs", "Incoming_Module_Sn = '" & Me.Incoming_Module_Sn.Text & "' And [Incoming_Disposition] =  'Completed'")
                        If Date - warrantyCheck < 190 Then ' approx 183 + 7 (6 months + a week)
                          If MsgBox("This unit is still under warranty and was last completed on " & warrantyCheck & ". Would you like to mark this as a warranty?", vbYesNo, "Duplicate Warning") = vbYes Then
                             Me.Incoming_Disposition = "Warranty"
                          End If
                        End If
                    End If
                'END 6 MONTHS''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                Case Left(Nz(Me.end_user), 4) = "Char"
                'BEG 1 YEAR''''''''''''''''''''''''''''''''''''
                    If IsNull(DMax("[complete_date]", "tbl_Module_Repairs", "Incoming_Module_Sn = '" & Me.Incoming_Module_Sn.Text & "'")) Then
                     MsgBox "No Complete date found", vbOKOnly + vbCritical
                     Exit Sub
                     Else
                          warrantyCheck = DMax("[complete_date]", "tbl_Module_Repairs", "Incoming_Module_Sn = '" & Me.Incoming_Module_Sn.Text & "' And [Incoming_Disposition] =  'Completed'")
                        If Date - warrantyCheck < 372 Then ' approx 365 + 7 (1 Year + a week)
                          If MsgBox("This unit is still under warranty and was last completed on " & warrantyCheck & ". Would you like to mark this as a warranty?", vbYesNo, "Duplicate Warning") = vbYes Then
                             Me.Incoming_Disposition = "Warranty"
                          End If
                        End If
                     End If
                'END 1 YEAR''''''''''''''''''''''''''''''''''''''''''''
                Case Else
                'BEG 6 MONTHS ''''''''''''''''''''''''''''''''''''
                    If IsNull(DMax("[complete_date]", "tbl_Module_Repairs", "Incoming_Module_Sn = '" & Me.Incoming_Module_Sn.Text & "'")) Then
                     MsgBox "No Complete date found", vbOKOnly + vbCritical
                     Exit Sub
                     Else
                          warrantyCheck = DMax("[complete_date]", "tbl_Module_Repairs", "Incoming_Module_Sn = '" & Me.Incoming_Module_Sn.Text & "' And [Incoming_Disposition] =  'Completed'")
                        If Date - warrantyCheck < 190 Then ' approx 183 + 7 (6 months + a week)
                          If MsgBox("This unit is still under warranty and was last completed on " & warrantyCheck & ". Would you like to mark this as a warranty?", vbYesNo, "Duplicate Warning") = vbYes Then
                             Me.Incoming_Disposition = "Warranty"
                          End If
                        End If
                    End If
                'END 6 MONTHS''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
               
           End Select
           
        Case Left(Nz(Me.customer), 3) = "Cox"
        'BEG 1 YEAR''''''''''''''''''''''''''''''''''''''''''''''''''''''
            If IsNull(DMax("[complete_date]", "tbl_Module_Repairs", "Incoming_Module_Sn = '" & Me.Incoming_Module_Sn.Text & "'")) Then
             MsgBox "No Complete date found", vbOKOnly + vbCritical
             Exit Sub
             Else
                  warrantyCheck = DMax("[complete_date]", "tbl_Module_Repairs", "Incoming_Module_Sn = '" & Me.Incoming_Module_Sn.Text & "' And [Incoming_Disposition] =  'Completed'")
                If Date - warrantyCheck < 372 Then ' approx 365 + 7 (6 months + a week)
                  If MsgBox("This unit is still under warranty and was last completed on " & warrantyCheck & ". Would you like to mark this as a warranty?", vbYesNo, "Duplicate Warning") = vbYes Then
                     Me.Incoming_Disposition = "Warranty"
                  End If
                End If
             End If
        'END 1 YEAR'''''''''''''''''''''''''''''''''''''''''''''
        
        Case Else
        
            If MsgBox("The Serial Number already exists. Check Warranty! Verify the warranty period for this customer. Do you wish to continue?", vbOKCancel, "Duplicate Warning") = vbCancel Then
            Cancel = True
            Me.Undo
            Exit Sub
            End If
        
    End Select
    End If
End If
End Sub

I think I didn't explain the situation well enough the first time as regards to the customers. The first customer we sub contract work from so they have end_user's, and the second customer is a direct customer. Anyways, thanks again!
 
You must log in or register to reply here.

Similar threads

A
Filter Records
Replies
1
Views
416
June7
June7
Garren.Shannon
10 key keypad
Replies
1
Views
585
June7
June7
H
Solved Problems with "renewal date" code
Replies
11
Views
1,032
HillTJ
H
V
Trying to have a button on a form open a report with info from a list box
Replies
18
Views
1,537
moke123
moke123
C
Surely there is an easier way, Lots of Check Boxes and with same code
Replies
11
Views
1,499
cheekybuddha
cheekybuddha

Users who are viewing this thread

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