Solved Need help querying subform's combo box based on the main form.

[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?

Edit: posting answer here for others to see, as Claude gave me a solution that does what the post was asking:

Main Form Setup​

Your main form should be bound to the Accounts table or a query based on it.

Subform Setup​

1. Subform Record Source Query​

Create a query for your subform that joins AccountGroups with Groups to show group names:

SELECT AccountGroups.AccountGroupsID, AccountGroups.AccountId, AccountGroups.GroupID, Groups.GroupName, Groups.SystemID FROM AccountGroups INNER JOIN AccountGroups ON AccountGroups.GroupID = Groups.GroupID
2. Combo Box Row Source
For the Group combo box in the subform, you'll need a dynamic row source. Call this something like qryFilteredComboBox:
SELECT GroupID, GroupName FROM Groups WHERE SystemID = Forms![YourFormName]!SystemID
Set this as your combo box’s rowsource.
Now make an On Current event in your main form:

Sub Form_Current()
Me.YourSubformControlname.form!YourGroupCombo.requery
End Sub

Hope this can help someone in the future!

 

[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

 

[SkeletorPlus]

Thank you for the answer. This database is for an administration team who manages air gapped (collateral) systems in various locations. This is so they can track and manage accounts and privileges. They cannot query AD directly and there is no connectivity whatsoever.

 

[mike60smart]

Can you give me an example of which values the User would be able to select for the Group Combo ?

 

[LarryE]

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.

If

1. Each System has multiple Groups
2. Each Group has multiple Accounts

Then the table design needs to only be:

1. A system table with SystemID
2. A Group table with GroupID and SystemID (FK)
3. An Account table with AccountID and GroupID (FK)

You are using the Accounts table as a junction table and the AccountGroups table as a junction table which are unnecessary.

 

[Mike Krailo]

Waste of time putting this info into a database when Active Directory is already a database. I work on air gapped systems and understand the difficulty involved. The simplest reasonable solution is to run the queries on each domain server (Power Shell). It's the easiest way to handle it. For myself, this falls under account auditing to ensure all users have the designated privileges only. Just create a script to gather and print the needed information out. The sysadmins are going to have to do this on each domain controller at each site or have dedicated personnel to do it for them. That's part of their job. It's not possible to manage air gapped systems without a physical admin present at the site so they can gather the info.

 

[SkeletorPlus]

If

1. Each System has multiple Groups
2. Each Group has multiple Accounts

Then the table design needs to only be:

1. A system table with SystemID
2. A Group table with GroupID and SystemID (FK)
3. An Account table with AccountID and GroupID (FK)

You are using the Accounts table as a junction table and the AccountGroups table as a junction table which are unnecessary.

An account can be a member of many groups is why the 4th table is needed.

 

[SkeletorPlus]

Can you give me an example of which values the User would be able to select for the Group Combo ?

They would be selecting "GroupName" (a, b, c) from the combo box.

 

[LarryE]

An account can be a member of many groups is why the 4th table is needed.

Ok then:

1. A system table with SystemID
2. An Account table with AccountID and SystemID (FK)
3. A Group table with GroupID and AccountID (FK)

It does not appear that the AccountGroups table has a purpose. There is not any field in that table that tracks anything like a transaction of some sort or event or data that requires input. What is that table for?

 

[SkeletorPlus]

Ok then:

1. A system table with SystemID
2. An Account table with AccountID and SystemID (FK)
3. A Group table with GroupID and AccountID (FK)

It does not appear that the AccountGroups table has a purpose. There is not any field in that table that tracks anything like a transaction of some sort or event or data that requires input. What is that table for?

We are talking about potentially 100 systems that are completely separate from each other. These groups truly do belong to the System. They have other assignments such as resources and permissions. Groups can have other groups nested within them. An account could be in 10 groups and vice versa. Trust me when I say there is more to the story where it makes sense.

I also have up to 50 groups on many systems. I need to be able to track which groups actually BELONG to a system to filter them for selection. Do you have any advice on what I am trying to do?

 

[mike60smart]

We are talking about potentially 100 systems that are completely separate from each other. These groups truly do belong to the System. They have other assignments such as resources and permissions. Groups can have other groups nested within them. An account could be in 10 groups and vice versa. Trust me when I say there is more to the story where it makes sense.

I also have up to 50 groups on many systems. I need to be able to track which groups actually BELONG to a system to filter them for selection. Do you have any advice on what I am trying to do?

Can you map out an example in Excel or Word ?

 

[LarryE]

We are talking about potentially 100 systems that are completely separate from each other. These groups truly do belong to the System. They have other assignments such as resources and permissions. Groups can have other groups nested within them. An account could be in 10 groups and vice versa. Trust me when I say there is more to the story where it makes sense.

I also have up to 50 groups on many systems. I need to be able to track which groups actually BELONG to a system to filter them for selection. Do you have any advice on what I am trying to do?

OK well we can only comment based on what you have told us. It sounds like you want to do many-to-many relationships then. ACCESS can do that with junction tables, and if you truly do need a junction table to bring together "Systems" and "Groups" to track resources and permissions then use the 4th table. It just didn't appear to be initially used for anything that's all. Perhaps if you define what a "System" is and a "Group" and an "Account", that would be helpful.

 

[Mike Krailo]

We are talking about potentially 100 systems that are completely separate from each other.

I would have to be paid extremely well to take on something like that in the way your proposing. Good luck.

 

[SkeletorPlus]

Solved, see note in initial post