Loop through Listbox records

[jmaty23]

I am looking to loop through all of the rows in a listbox. For each row in the listbox, I want to check if the last name in the Labor column is contained in any records in the Labor column of a recordset. If it is contained, add the column value to a variable to eventually be shown in another listbox. It seems that the loop is not working correctly and the EstHours is always 0 and the instr function doesn't seem to be working.

Does anyone have any thoughts on what I am doing wrong??

Sub ScheduledHours()
'on error goto errorhandler

Dim LastName As String
Dim FullName As Variant
Dim EstHours As Long

Me.lstscheduled.RowSource = ""

With lstlabor
    For i = 0 To .ListCount - 1
        LastName = fExtractLastNAme(lstlabor.Column(4, i))
            EstHours = 0
            Dim rs As Recordset
            Set rs = Me.subwo.Form.RecordsetClone
            Do Until rs.EOF
                FullName = rs!Labor
                If InStr(1, FullName, LastName) = 1 Then
                EstHours = EstHours + rs!EstimatedTime
                End If
            rs.MoveNext
            Loop
            Set rs = Nothing
        Me.lstscheduled.AddItem LastName & ";" & lstlabor.Column(6, i) & ";" & EstHours
    Next i
End With

ProcedureExit:
    Exit Sub
    
ErrorHandler:
    MsgBox "Error" & ":  " & Err.Number & vbCrLf & "Description: " _
        & Err.Description, vbExclamation, Me.Name & ".ScheduledHours"
    Resume ProcedureExit

End Sub

 

[pbaldy]

I would set a breakpoint so you can step through the code line by line and what the values are as it runs:

http://www.baldyweb.com/Debugging.htm

 

[MarkK]

But more generally, your process is occurring way too far away from the data. Your code leverages user interface elements to provide programmatic access to data, and it adds a ton of complexity. You'd be much happier, I think, to learn how to use SQL to open recordsets directly.

See how you are looping through a subform's recordsetclone, summing the rs!EstimatedTime for matching LastName(s)? In SQL, that is as simple as . . .

SELECT LastName, Sum(EstimatedTime) As EstHours FROM subwosTable 
WHERE LastName = '<somename>' GROUP BY LastName;

. . . and those are the same fields you are adding to your other list. So I bet your could just write the proper SQL for your other list, and pretty much solve the whole thing that way.

 

[jmaty23]

The issue I was having with writing a SQL query was that there wasn't any same fields to connect the data. The names from one table were [Doe, John] format and the other were [John Doe]. I was able to change the names around in the table to make them all read [Doe, John] format and was now able to make the join using SQL. Thanks for the insight.