Fill ListBox with user login names

[Stuart Jack via AccessMonster.com]

Hi

I have a database in which I have applied Access security, I have created a
form which the admin user can create new users and they are added to the
Users group - code below.

Code:
----------------------------------------------------------------------------

Public Function sCreateUser(ByVal strUser As String, ByVal strPID As String,
Optional varPwd As Variant) As Integer

' Create a new user and add them to the users group
' Returns True on success, False if already exists
Dim db As Database
Dim ws As Workspace
Dim usr As User
Dim grpUsers As Group
Dim strSQL As String

'If the password isn't supplied, make sure you
' pass an empty string for the password argument
If IsMissing(varPwd) Then varPwd = ""
Set ws = DBEngine.Workspaces(0)
ws.Users.Refresh
On Error Resume Next
' Check to see if the user already exists by using inline
' error handling to trap any errors caused by setting
' a reference to a possibly non-existent user
strUser = ws.Users(strUser).Name
If Err.Number = 0 Then
MsgBox "The user you are trying to add already exists.", _
vbInformation, "Can't Add User"
sCreateUser = False
Else
' go ahead and create user account
Set usr = ws.CreateUser(strUser, strPID, varPwd)
ws.Users.Append usr
ws.Users.Refresh
' now add the user to the Users group
Set grpUsers = ws.Groups("Users")
Set usr = grpUsers.CreateUser(strUser)
grpUsers.Users.Append usr
grpUsers.Users.Refresh
sCreateUser = True
End If

End Function

I would like to be able to create another form which has a listbox of users
names in the Users group from which the Admin db can then select a user name
to remove from the system. I can enumerate the users by the using the code
below, but I am not too good at getting this into a listbox or combo box.

Code:
------------------------------------------------------------------------------
-


Function EnumGrpsNUsers()

Dim grp As Group
Dim usr As User

For Each usr In DBEngine(0).Users
Debug.Print usr.Name
For Each grp In usr.Groups
Debug.Print vbTab; grp.Name
Next
Next

End Function

Can anyone suggest where I start please.

Thanks

Stuart

 

[Brotha Lee]

Hi Stuart,

I think you are almost there! There are several ways to add items to a
combobox and a listbox. Both syntaxes are fairly the same.

I think the best solution for you is to add items based on a value list.

To add an items do the following:
1) Add a combobox to your form
2) In design mode set the column count to 2 (one column for the group and
one for the username)
3) Set the field type to "Value List"
4) Create a button and add the following code:
Dim grp As Group
Dim usr As User
Dim sValues As String

For Each usr In DBEngine(0).Users
For Each grp In usr.Groups
sValues = sValues & "," & usr.Name & "," & grp.Name
Next
Next

Me.List3.RowSource = Mid(sValues, 2, Len(sValues))

The codes works for listboxes and comboxes.
Furthermore there is a lot more information on combo's and listboxes in the
helpfiles.

HTH

 

[Stuart Jack via AccessMonster.com]

Hi Brotha

Thanks for the reply, tried as you suggested and is working fine except the
list that appears repeats some user names obviously because they are in more
than one group, i.e Admins and Users.

Is there a way to limit to only showing the name once in the list, I suppose
something like only showing "Users" and not Admin would do because I dont
want Admin to delete Admin users, only to be able to add and delete Users.

would something like

For Each grp In usr.Groups <> "Admins"

work ?

Regards

Stuart

Hi Stuart,

I think you are almost there! There are several ways to add items to a
combobox and a listbox. Both syntaxes are fairly the same.

I think the best solution for you is to add items based on a value list.

To add an items do the following:
1) Add a combobox to your form
2) In design mode set the column count to 2 (one column for the group and
one for the username)
3) Set the field type to "Value List"
4) Create a button and add the following code:
Dim grp As Group
Dim usr As User
Dim sValues As String

For Each usr In DBEngine(0).Users
For Each grp In usr.Groups
sValues = sValues & "," & usr.Name & "," & grp.Name
Next
Next

Me.List3.RowSource = Mid(sValues, 2, Len(sValues))

The codes works for listboxes and comboxes.
Furthermore there is a lot more information on combo's and listboxes in the
helpfiles.

HTH

Hi

[quoted text clipped - 73 lines]

Stuart

 

[Stuart Jack via AccessMonster.com]

Hi

I have worked it out, I think; at least it seems to work.

Set ws = DBEngine.Workspaces(0)

For Each usr In DBEngine(0).Users
sValues = sValues & "," & usr.Name
Next

Me.cboUsers.RowSource = Mid(sValues, 2, Len(sValues))

Should have thought about it a bit more before last post, sorry.

thanks again

Stuart

Hi Brotha

Thanks for the reply, tried as you suggested and is working fine except the
list that appears repeats some user names obviously because they are in more
than one group, i.e Admins and Users.

Is there a way to limit to only showing the name once in the list, I suppose
something like only showing "Users" and not Admin would do because I dont
want Admin to delete Admin users, only to be able to add and delete Users.

would something like

For Each grp In usr.Groups <> "Admins"

work ?

Regards

Stuart

Hi Stuart,

[quoted text clipped - 32 lines]