Multiple Highlighted Entries in List Box from Table

[entonne]

I have tried to search the forums, but to no avail. I may just be blind or have overlooked a post, but here is my situation:

Many-to-many relationship(I think)

User Table: UserID, UserName
Group Table: GroupID, GroupName
UserGroup Table: UserID, GroupID (both referencing their respective tables)

I have a list box whose entries are retrieved from the Group Table. If User 1 belongs to groups A, B, C but not to groups D, E, F, and G I want to show groups A-G, but highlight A, B, and C as stored in the UserGroup Table. Is there a way to do this or should I do the whole dual listbox route?

 

[jbpez]

Your listbox will be based on all existing groups.
Then you will need to create a recordset based on the users selected containing all the groups that apply to the selected user. Then you will need to loop through the recordset, and as you loop through them you will need to compare to the groups listed in the listbox if there is a match highlight the match.

Let me know if you need some sample code.

John Borges

 

[entonne]

Hi jbpez thanks for your response, I would love to have some sample code, as I can't code in VB for beans. Can you possible recommend a good book for reference/learning VB.

 

[jbpez]

Here is a function that will do what you want.

Function HighlightGroupsThatApply(lngUserID as long)as boolean

Dim rst as DAO.Recordset
Dim strSQL as string
DIm x as integer
Dim varGroupName as variant

'create SQL statement
strSQL = "SELECT Group.GroupName "
strSQL = strSQL & "FROM (User INNER JOIN UserGroup ON User.UserID=UserGroup.UserID) INNER JOIN Group ON UserGroup.GroupID=Group.GroupID "
strSQL = strSQL & "WHERE UserID=" & intUserID

'create recordset
Set rst = CurrentDb.OpenRecordset(strSQL,dbOpenSnapshot)

'clear existing highligths of listbox
For x = 0 to Me.ListboxName.Count - 1
   Me.ListboxName.Selected(x) = False
Next x

'loop through recordset
Do Until rst.EOF
   varGroupName = rst("GroupName").value
   
   'loop through listbox to find matching group
   For x = 0 To Me.ListboxName.Count - 1
      If varGroupName = Me.ListboxName.ItemData(x) Then
         'highlight matching group
         Me.ListboxName.Selected(x) = True
      End If
   Next x

rst.MoveNext
Loop

rst.Close
Set rst = Nothing

HighlightGroupsThatApply = True

End Function

Then you can call this function from whatever triggered event. Example if the user id is 100 then:

HighlightGroupsThatApply(100)

I hope this helps

John Borges