Obtain a list of users in database in VBA

[Kosio]

Is there a simple way to use VBA to obtain a list of usernames in the
current database? For example, if I want a combo box to contain a list
of usernames in the current database so that someone using the form can
choose a username from the combobox to store with that record.

 

[Rick B]

What do you mena users in the current database? You want a list to populate
with only the users currently logged in? What if someone closes the databse
and goes to lunch, do you not want them to show up?

Or do you want to pull all the userids previously entered in a particular
field or table? If so, do you want the users to be able to type a value
that is not in the list? What if you get a new user?

 

[Kosio]

I would like all the users in the database that now exist, even if
they've logged off and gone out to lunch. Yes, I would like them to be
the only thing they can type in the list. Basically every time the
form loads it will check all the users in the table. The way I have
the database set up is that only I will be able to add users to the
database, so the database will not be in use when I make a new user.
This database is only used by a few people and not spread out across a
large geographical area.

As far as all the userids previously entered in a particular field or
table, does Access store all of the userids somewhere that I can
access? If it does, the userids are what I want the combo box contents
to be. It's more or less a query on the userids and the contents of
the query are put in this combo box.

Thanks.

 

[Rick B]

So, you have a table that includes the userids?

If so, just use that at the data source for your combo-box. The combo-box
wizard will walk you right through the steps!

Let us know if you need more.

 

[Kosio]

No, I don't have a table that includes userids. I would like to make a
table of userids if that is possible.

Thanks,
Aaron

 

[Rick B]

Basically, you want to pull all the userids that have previously been
entered in the field. To do this, build a combo-box and put something like
the following as it's records source...



SELECT DISTINCT SomeTableName.UserID
FROM SomeTableName;




That will pull all the userids ever used, but only show one instance of each
one.

 

[Joan Wild]

Are you counting on the user to always select their own name? If so, you
can just set the default value of a textbox control to =CurrentUser() and
make the textbox locked so they can't change it. If however, you really
want them to maybe select someone else's name then the following code will
do what you want.

Dim wrk As Workspace
Dim strAccountName As String
Dim usr As User

Set wrk = DBEngine(0)

For Each usr In wrk.Users
If usr.Name <> "Creator" And usr.Name <> "Engine" Then
strAccountName = strAccountName & ";" & usr.Name
End If
Next

YourCombo.RowSource = strAccountName

 

[TC]

I assume that you have created some new users (Tom, Dick, Mary) as part
of the process of securing your database with user-level security?

If so, then, the following code will list all of those user names,
regardless of whether they are or are not currently logged in to the
database:

dim u as user
for each u in dbengine(0).users
msgbox u.name
next

Remember that the user names are not defined "in the database". They
are defined in the workgroup information file. That code retrieves the
user names from the workgroup information file - not from the database.

Does that help?

TC