Need help querying subform's combo box based on the main form. (2 Viewers)

[SkeletorPlus]

Hi. I have the following basic structure:
A System table with SystemID and SystemName;
A Groups table with GroupID, SystemID (FK), and GroupName;
An Accounts table with AccountID, SystemID (FK), and AccountUsername;
An AccountGroups table with AccountGroupID, GroupID, and AccountID.

I have a form that shows Accounts, and want to have a subform where the user can add what Active Directory groups that user is apart of. Groups will be on the subform and link master/child based on AccountID, but I want the options in the Group combo box to only show options where the SystemID is the same for both. I am having trouble understanding what I need to do for the query (base it off a record set or a control in the main form?) and for what type of events I need to have to make this work (after update, on current, etc). Anyone have some good examples or advice?

 

[KitaYama]

want to have a subform where the user can add what Active Directory groups that user is apart of.

While I'm sure others will answer your specific question, why you want users select which group of active directory they belong to?
They may make a wrong selection, or most of them don't know which group they belong to.

Use VBA to get a list of AD groups a user is a member of :

Sub ListADGroupsForUser()
    Dim objUser As Object
    Dim objGroup As Variant
    Dim userName As String
    Dim groupsList As String
 
    userName = "Ando"  'Change it to an account of your AD'

    On Error Resume Next
    Set objUser = GetObject("WinNT://ChangeThisToYourDomainName/" & userName & ",user")
    On Error GoTo 0

    If objUser Is Nothing Then
        MsgBox "User not found in Active Directory", vbCritical
        Exit Sub
    End If
 
    For Each objGroup In objUser.Groups
        groupsList = groupsList & objGroup.Name & vbCrLf
    Next

    If Len(groupsList) > 0 Then
        MsgBox "Groups for user '" & userName & "':" & vbCrLf & vbCrLf & groupsList, vbInformation
    Else
        MsgBox "No groups found or user has no group memberships.", vbExclamation
    End If
End Sub