Obtain a list of users in database in VBA

5 Aug 2005 4:19 PM

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.

5 Aug 2005 4:37 PM

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?

--
Rick B

Show quoteHide quote

"Kosio" <aaronge***@gmail.com> wrote in message
news:1123258755.112322.269780@g49g2000cwa.googlegroups.com...
> 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.
>

5 Aug 2005 5:07 PM

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.

5 Aug 2005 6:17 PM

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.

--
Rick B

Show quoteHide quote

"Kosio" <aaronge***@gmail.com> wrote in message
news:1123261644.389905.301610@g49g2000cwa.googlegroups.com...
> 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.
>

5 Aug 2005 6:27 PM

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

5 Aug 2005 8:19 PM

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.

--
Rick B

Show quoteHide quote

"Kosio" <aaronge***@gmail.com> wrote in message
news:1123266456.316647.178200@g14g2000cwa.googlegroups.com...
> 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
>

5 Aug 2005 10:05 PM

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

--
Joan Wild
Microsoft Access MVP

Kosio wrote:

Show quoteHide quote

> 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.

6 Aug 2005 3:45 AM

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