[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 599
  • Last Modified:

Runtime Error 438 using ListView Control

I am using a Microsoft Access 2000 form with a list view control and a calendar control. A user selects a date, clicks a command button and the date is put into a textbox. User selects a total of two dates in order to establish a range of days to use for a service level calculation.

Error 438 occurs when the user provides two dates in the future. When the user provides two dates in the past there is no error all works as expected.

In the form load event, I am able to build columnheaders on the list view control and I say this because I am using the listview's members long before the listiview populates and complains that I can no longer use its members because the members are not supported. The code breaks when I attempt to evaluate if the listview listitems collection is populated or not. If I supress it then I get more errors such object not created and thats not good either.

I appreciate your insight and assistance in regards to this matter.
Private Sub PopConfirmationListView()
'On Error GoTo Error_Handler
    'code
    Dim d As Variant
    Dim i As ListItem
    Dim s As ListSubItem
    Dim slDate As Date
    Dim weekendString As String
    Dim nonWorkDayString As String
    Dim order As Integer
    Dim report As Integer
    
    ' >>> Code errors here <<<
    If Me.lvwConfirmation.ListItems.Count > 0 Then Me.lvwConfirmation.ListItems.Clear
    ' >>> end error <<<
    
    Set mSL = New ServiceLevel
    mSL.Init Me.RecordsetClone
   
    For Each d In mAllDays
        order = order + 1
        If IsWeekday(d) Then
            If IsWorkDay(d, mDaysNotWorked) Then
                ' order
                Set i = lvwConfirmation.ListItems.Add(, , order)
                ' date
                Set s = i.ListSubItems.Add(, , Format(d, "Short Date"))
                ' state
                Set s = i.ListSubItems.Add(, , "Work Day: Counted")
                ' service level
                slDate = GetServiceLevelDate(d, mDaysNotWorked)
                Set s = i.ListSubItems.Add(, , slDate)
                ' weekdays not counted
                nonWorkDayString = BuildNonWorkdayString(d)
                Set s = i.ListSubItems.Add(, , nonWorkDayString)
                ' weekends not counted
                weekendString = BuildWeekendString
                Set s = i.ListSubItems.Add(, , weekendString)
 
                report = CInt(fraReport.Value)
                mSL.Calculate d, slDate, report
            Else
                ' work day else
                ' order
                Set i = lvwConfirmation.ListItems.Add(, , order)
                ' date
                Set s = i.ListSubItems.Add(, , Format(d, "Short Date"))
                ' state
                Set s = i.ListSubItems.Add(, , "Non Work Day: Not Counted")
                ' service level
                Set s = i.ListSubItems.Add(, , "Non Work Day: No Service Level")
                ' weekdays not counted
                Set s = i.ListSubItems.Add(, , "Non Work Day: Not Applicable")
                ' weekends not counted
                Set s = i.ListSubItems.Add(, , "Non Work Day: Not Applicable")
 
            End If
        Else
            ' week day else
            ' order
            Set i = lvwConfirmation.ListItems.Add(, , order)
            ' date
            Set s = i.ListSubItems.Add(, , Format(d, "Short Date"))
            ' state
            Set s = i.ListSubItems.Add(, , "Weekend: Not Counted")
            ' service level
            Set s = i.ListSubItems.Add(, , "Weekend: No Service Level")
            ' weekdays not counted
            Set s = i.ListSubItems.Add(, , "Weekend: Not Applicable")
            ' weekends not counted
            Set s = i.ListSubItems.Add(, , "Weekend: Not Applicable")
        End If
    Next
Exit_Sub:
Exit Sub
 
'Error_Handler:
'    If Err.Number = 438 Then
'        Resume Next
'    Else
'        Stop
'        Resume
'        'MsgBox "Error: " & Err.Number & vbNewLine & "Description: " & Err.Description
'    End If
End Sub

Open in new window

0
jpmc_cmsops
Asked:
jpmc_cmsops
  • 4
  • 4
2 Solutions
 
BadotzCommented:
Can you show the code for "mSL.Calculate"?
0
 
jpmc_cmsopsAuthor Commented:
The calculate member is apart of an interface called ICalcuate. There is also ICalculate2 which has the same structure as ICalculate but there is one property name that is different. If it werent for that one property I would just use one interface.

The Class: 30DayServiceLevelCalculation implements ICalcualte
The Class: 2DayOpenServiceLevelCalculation and 5DayOpenServiceLevelCalculation implement ICalculate2

I will attach the code for all three implementations of calcualte.
>>IMPLEMENTATION FOR 30DAY<<
Private Sub ICalculate_Calculate(ByVal RecieveDate As Date, ByVal ServiceLevelDate As Date)
    mRecieveDate = Format(RecieveDate, "Short Date")
    mServiceLevelDate = Format(ServiceLevelDate, "Short Date")
   
    mRS.MoveFirst
    
    Do While Not mRS.EOF
        ' ** OVERALL **
        If mRS("fldReceiveDate").Value = mRecieveDate Then
            mIncoming = mIncoming + 1
            
            If IsNull(mRS("fldCloseDate").Value) Then
                mCompletes = mCompletes + 0
            ElseIf mRS("fldCloseDate").Value > mServiceLevelDate Then
                mCompletes = mCompletes + 0
            Else
                mCompletes = mCompletes + 1
            End If
    
        
            ' ** COLUMBUS **
            If mRS("fldSite").Value = "Columbus" Then
                mIncomingColumbus = mIncomingColumbus + 1
                
                If IsNull(mRS("fldCloseDate").Value) Then
                    mCompletesColumbus = mCompletesColumbus + 0
                ElseIf mRS("fldCloseDate").Value > mServiceLevelDate Then
                    mCompletesColumbus = mCompletesColumbus + 0
                Else
                    mCompletesColumbus = mCompletesColumbus + 1
                End If
                
            End If
            
            ' ** BANGALORE **
            If mRS("fldSite").Value = "Bangalore" Then
                mIncomingBangalore = mIncomingBangalore + 1
                
                If IsNull(mRS("fldCloseDate").Value) Then
                    mCompletesBangalore = mCompletesBangalore + 0
                ElseIf mRS("fldCloseDate").Value > mServiceLevelDate Then
                    mCompletesBangalore = mCompletesBangalore + 0
                Else
                    mCompletesBangalore = mCompletesBangalore + 1
                End If
                
            End If
    
            ' ** ELGIN **
            If mRS("fldSite").Value = "Elgin" Then
                mIncomingElgin = mIncomingElgin + 1
                
                If IsNull(mRS("fldCloseDate").Value) Then
                    mCompletesElgin = mCompletesElgin + 0
                ElseIf mRS("fldCloseDate").Value > mServiceLevelDate Then
                    mCompletesElgin = mCompletesElgin + 0
                Else
                    mCompletesElgin = mCompletesElgin + 1
                End If
                
            End If
        End If
        mRS.MoveNext
    Loop
    
    ' overall
    If mIncoming > 0 Then
        mServiceLevel = mCompletes / mIncoming
    Else
        mServiceLevel = 0
    End If
    
    ' Columbus
    If mIncomingColumbus > 0 Then
        mServiceLevelColumbus = mCompletesColumbus / mIncomingColumbus
    Else
        mServiceLevelColumbus = 0
    End If
 
    ' Bangalore
    If mIncomingBangalore > 0 Then
        mServiceLevelBangalore = mCompletesBangalore / mIncomingBangalore
    Else
        mServiceLevelBangalore = 0
    End If
 
    ' Elgin
    If mIncomingElgin > 0 Then
        mServiceLevelElgin = mCompletesElgin / mIncomingElgin
    Else
        mServiceLevelElgin = 0
    End If
End Sub
>> END 30DAY IMPELMENTATION <<
 
>> 5DAY OPEN IMPELMENTATION <<
Private Sub ICalculate2_Calculate(ByVal RecieveDate As Date, ByVal ServiceLevelDate As Date)
    mRecieveDate = Format(RecieveDate, "Short Date")
    mServiceLevelDate = Format(ServiceLevelDate, "Short Date")
   
    mRS.MoveFirst
    
    Do While Not mRS.EOF
        ' ** OVERALL **
        If mRS("fldReceiveDate").Value = mRecieveDate Then
            mIncoming = mIncoming + 1
            
            If IsNull(mRS("fldOpenDate").Value) Then
                mOpened = mOpened + 0
            ElseIf mRS("fldOpenDate").Value > mServiceLevelDate Then
                mOpened = mOpened + 0
            Else
                mOpened = mOpened + 1
            End If
    
        
            ' ** COLUMBUS **
            If mRS("fldSite").Value = "Columbus" Then
                mIncomingColumbus = mIncomingColumbus + 1
                
                If IsNull(mRS("fldOpenDate").Value) Then
                    mOpenedColumbus = mOpenedColumbus + 0
                ElseIf mRS("fldOpenDate").Value > mServiceLevelDate Then
                    mOpenedColumbus = mOpenedColumbus + 0
                Else
                    mOpenedColumbus = mOpenedColumbus + 1
                End If
                
            End If
            
            ' ** BANGALORE **
            If mRS("fldSite").Value = "Bangalore" Then
                mIncomingBangalore = mIncomingBangalore + 1
                
                If IsNull(mRS("fldOpenDate").Value) Then
                    mOpenedBangalore = mOpenedBangalore + 0
                ElseIf mRS("fldOpenDate").Value > mServiceLevelDate Then
                    mOpenedBangalore = mOpenedBangalore + 0
                Else
                    mOpenedBangalore = mOpenedBangalore + 1
                End If
                
            End If
    
            ' ** ELGIN **
            If mRS("fldSite").Value = "Elgin" Then
                mIncomingElgin = mIncomingElgin + 1
                
                If IsNull(mRS("fldOpenDate").Value) Then
                    mOpenedElgin = mOpenedElgin + 0
                ElseIf mRS("fldOpenDate").Value > mServiceLevelDate Then
                    mOpenedElgin = mOpenedElgin + 0
                Else
                    mOpenedElgin = mOpenedElgin + 1
                End If
                
            End If
        End If
        mRS.MoveNext
    Loop
    
    ' overall
    If mIncoming > 0 Then
        mServiceLevel = mOpened / mIncoming
    Else
        mServiceLevel = 0
    End If
    
    ' Columbus
    If mIncomingColumbus > 0 Then
        mServiceLevelColumbus = mOpenedColumbus / mIncomingColumbus
    Else
        mServiceLevelColumbus = 0
    End If
 
    ' Bangalore
    If mIncomingBangalore > 0 Then
        mServiceLevelBangalore = mOpenedBangalore / mIncomingBangalore
    Else
        mServiceLevelBangalore = 0
    End If
 
    ' Elgin
    If mIncomingElgin > 0 Then
        mServiceLevelElgin = mOpenedElgin / mIncomingElgin
    Else
        mServiceLevelElgin = 0
    End If
End Sub
>> END 5DAY OPEN <<
 
>> 2 DAY OPEN IMPELMENTATION <<
Private Sub ICalculate2_Calculate(ByVal RecieveDate As Date, ByVal ServiceLevelDate As Date)
    mRecieveDate = Format(RecieveDate, "Short Date")
    mServiceLevelDate = Format(ServiceLevelDate, "Short Date")
   
    mRS.MoveFirst
    
    Do While Not mRS.EOF
        ' ** OVERALL **
        If mRS("fldReceiveDate").Value = mRecieveDate Then
            mIncoming = mIncoming + 1
            
            If IsNull(mRS("fldOpenDate").Value) Then
                mOpened = mOpened + 0
            ElseIf mRS("fldOpenDate").Value > mServiceLevelDate Then
                mOpened = mOpened + 0
            Else
                mOpened = mOpened + 1
            End If
    
        
            ' ** COLUMBUS **
            If mRS("fldSite").Value = "Columbus" Then
                mIncomingColumbus = mIncomingColumbus + 1
                
                If IsNull(mRS("fldOpenDate").Value) Then
                    mOpenedColumbus = mOpenedColumbus + 0
                ElseIf mRS("fldOpenDate").Value > mServiceLevelDate Then
                    mOpenedColumbus = mOpenedColumbus + 0
                Else
                    mOpenedColumbus = mOpenedColumbus + 1
                End If
                
            End If
            
            ' ** BANGALORE **
            If mRS("fldSite").Value = "Bangalore" Then
                mIncomingBangalore = mIncomingBangalore + 1
                
                If IsNull(mRS("fldOpenDate").Value) Then
                    mOpenedBangalore = mOpenedBangalore + 0
                ElseIf mRS("fldOpenDate").Value > mServiceLevelDate Then
                    mOpenedBangalore = mOpenedBangalore + 0
                Else
                    mOpenedBangalore = mOpenedBangalore + 1
                End If
                
            End If
    
            ' ** ELGIN **
            If mRS("fldSite").Value = "Elgin" Then
                mIncomingElgin = mIncomingElgin + 1
                
                If IsNull(mRS("fldOpenDate").Value) Then
                    mOpenedElgin = mOpenedElgin + 0
                ElseIf mRS("fldOpenDate").Value > mServiceLevelDate Then
                    mOpenedElgin = mOpenedElgin + 0
                Else
                    mOpenedElgin = mOpenedElgin + 1
                End If
                
            End If
        End If
        mRS.MoveNext
    Loop
    
    ' overall
    If mIncoming > 0 Then
        mServiceLevel = mOpened / mIncoming
    Else
        mServiceLevel = 0
    End If
    
    ' Columbus
    If mIncomingColumbus > 0 Then
        mServiceLevelColumbus = mOpenedColumbus / mIncomingColumbus
    Else
        mServiceLevelColumbus = 0
    End If
 
    ' Bangalore
    If mIncomingBangalore > 0 Then
        mServiceLevelBangalore = mOpenedBangalore / mIncomingBangalore
    Else
        mServiceLevelBangalore = 0
    End If
 
    ' Elgin
    If mIncomingElgin > 0 Then
        mServiceLevelElgin = mOpenedElgin / mIncomingElgin
    Else
        mServiceLevelElgin = 0
    End If
End Sub
>> END 2DAY OPEN <<

Open in new window

0
 
jpmc_cmsopsAuthor Commented:
I amost forgot. Here is code within the service level class in the calculate memeber that uses the 3 implmentations I have previously provided.
Public Sub Calculate(ByVal RecieveDate As Date, ByVal ServiceLevelDate As Date, ByVal report As Integer)
    Dim iC As ICalculate
    Dim iC2 As ICalculate2
    
        
    Select Case report
        Case 1
            Set iC = New [30DayServiceLevelCalculation]
            iC.Init mRS
            Call UpdateVariablesIC(iC, RecieveDate, ServiceLevelDate)
          
        Case 2
            Set iC2 = New [5DayOpenServiceLevelCalculation]
            iC2.Init mRS
            Call UpdateVariablesIC2(iC2, RecieveDate, ServiceLevelDate)
            
        Case 3
            Set iC2 = New [2DayOpenServiceLevelCalculation]
            iC2.Init mRS
            Call UpdateVariablesIC2(iC2, RecieveDate, ServiceLevelDate)
            
        Case Else
            
    End Select
            
    Call Update
End Sub
 
Private Sub Update()
    'code to update tbl_ServiceLevel
    Dim SQL As String
    
    DoCmd.SetWarnings False
    
    SQL = "INSERT INTO tbl_ServiceLevel VALUES('Overall', #" & mRecieveDate & "#, #" & mServiceLevelDate & "#, " & mIncoming & ", " & mCompletes & ", " & mServiceLevel & ")"
    DoCmd.RunSQL SQL
    
    SQL = "INSERT INTO tbl_ServiceLevel VALUES('Bangalore', #" & mRecieveDate & "#, #" & mServiceLevelDate & "#, " & mIncomingBangalore & ", " & mCompletesBangalore & ", " & mServiceLevelBangalore & ")"
    DoCmd.RunSQL SQL
    
    SQL = "INSERT INTO tbl_ServiceLevel VALUES('Columbus', #" & mRecieveDate & "#, #" & mServiceLevelDate & "#, " & mIncomingColumbus & ", " & mCompletesColumbus & ", " & mServiceLevelColumbus & ")"
    DoCmd.RunSQL SQL
    
    SQL = "INSERT INTO tbl_ServiceLevel VALUES('Elgin', #" & mRecieveDate & "#, #" & mServiceLevelDate & "#, " & mIncomingElgin & ", " & mCompletesElgin & ", " & mServiceLevelElgin & ")"
    DoCmd.RunSQL SQL
 
    DoCmd.SetWarnings True
End Sub

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
BadotzCommented:
Specifically, I meant the code for line 41:

mSL.Calculate d, slDate, report

Anything else is too confusing.
0
 
BadotzCommented:
Ah, that is handled by the 3rd part.
0
 
BadotzCommented:
What are the actual values of

d, slDate, report

on line 41?
0
 
jpmc_cmsopsAuthor Commented:
I put together some screen shots in small pieces on bmp file.
I wanted you see it on a break in the ide.
Hopefully its legible, but if not.

The value of d is a date type although declared as variant so it can work within the for next loop.
The value slDate is a date type
The value of report is an integer type
DataValues.bmp
0
 
jpmc_cmsopsAuthor Commented:
Hi guys,

Don't worry about this anymore. I appreciate you all looking at it.
If the control does not work, it wont work. I will refactor my code and redesign the UI
without  the (non-.net framework) buggy common controls.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now