Pre-selecting items in ListBox 1

[PennWhite]

ACC03: I have an "F_Contacts" form which contains a listbox control listing types of services that person (record) provides. Some examples might be "Carpentry", "Drywalling", "Heavy Equipment Operator", etc.

When the user moves from one record to another, I would like to display their previous selections for the listbox.

The selections for each record are stored in a table "T_ContactServices" with three fields.

T_ContactServices
Field 1: ContactServicesID - autonumber index
Field 2: ContactID - long integer
Field 3: ServiceID - long integer

In the F_Contacts OnCurrent event, I call a sub that (is supposed to) pre-select the items in the listbox based on the T_ContactServices table.

Here's the code:

Private Sub P_ResetServices()
Dim rst As DAO.Recordset
Dim ctlList As Control
Dim varItem As Variant
Dim cnt As Integer
Dim F as Form

set F = Forms!F_Contacts

Set ctlList = F!lstServices
Set rst = CurrentDb.OpenRecordset("Select * from T_ContactServices")
rst.FindFirst ("ContactID=" & ContactID)
If rst.NoMatch Then ' No Services assigned to this Contact
For Each varItem In ctlList.ItemsSelected
ctlList.Selected(varItem) = False
Next varItem
Else ' Contact has Services selected
' Clear listbox
For Each varItem In ctlList.ItemsSelected
ctlList.Selected(varItem) = False
Next varItem

' Reset Services selected from T_ContactServices
Set rst = _
CurrentDb.OpenRecordset("Select * from T_ContactServices WHERE ContactID=" _
& ContactID)
rst.MoveLast
rst.MoveFirst

For cnt = 1 To rst.RecordCount
varItem = rst!ServiceID - 1
' The problem is here.
' rst!ServiceID is NOT the same as the ItemData index in the lstServices
' ex: Service = "Heavy Equipment Operator"
' ServiceID = "13"
' ItemData Index for "Heavy Equipment Operator" in lstServices = 6
' lstServices.ItemData(12) = "Roofing"
ctlList.Selected(varItem) = True
rst.MoveNext
Next cnt
End If

rst.Close
Set rst = Nothing
Set ctlList = Nothing
Set F = Nothing

End Sub

The problem is that I can't figure out how to get the proper ItemData index (ie. row) based on the ServiceID which is unfortunately NOT the same.

Maybe it's simple. I can't see it.

Any help greatly appreciated,

Penn

 

[PennWhite]

Here's one solution that I just discovered. It ain't purty but it works.

For cnt = 1 To rst.RecordCount
Dim i As Integer
For i = 0 To ctlList.ListCount
If ctlList.Column(0, i) = CStr(rst!ServiceTypeID) Then
varItem = i
Exit For
End If
Next
ctlList.Selected(varItem) = True
rst.MoveNext
Next cnt


Penn

 

[mp9]

Here's a faster version of Penn's code - it's faster as it doesn't have to determine the value of ctlList.ListCount on every iteration of the For Next loop.

For cnt = 1 To rst.RecordCount
    Dim i As Integer, c As Integer
    c = ctlList.ListCount
    For i = 0 To c
        If ctlList.Column(0, i) = CStr(rst!ServiceTypeID) Then
            varItem = i
            Exit For
        End If
    Next
    ctlList.Selected(varItem) = True
    rst.MoveNext
Next cnt

http://www22.brinkster.com/accessory/

 

[PennWhite]

Hmm. Never thought of that. In fact, I didn't actually know it was recalculating the listcount for each iteration but, now that you mention it, it makes perfect sense.

Extending your thought, it would probably be quicker to assign rst.recordcount (which I use frequently) to a variable as well.

Thanks very much for the tip.

Penn

 

[PHV]

Anyway:
For i = 0 To ctlList.ListCount[!] - 1 [/!]

 

[PennWhite]

PHV:

Right. No particular reason to start with 0 since ListCount isn't zero based like the listitem index is.

Thanks,

Penn