How can I create a flexible query where the parameter is created by environ("username")

[SirArch]

To control access to specific data in a multi-user database on a network without having to request the users to do anything, I want to take the Environ("username") return and pass it as a parameter to a flexible query.



Many searches and reading of my three thick reference works have failed to turn up any way of doing that?

Any thoughts?

Thanks

[ranman256]

in a form put valuers, then run the query using the form as params:

txtUser = Environ("username")
docmd.openquery "qsMyQuery"

where qsmyQuery =
select * from table where [userID] = forms!frmMain!txtUser

[orange]

You may want to investigate Role Based Access Control to see how the pieces fit together.
It isn't clear what exactly you want to "control/restrict access to", nor how many users there are.

Typically you identify users and associate a user with a Role; then you have additional info that allows/prohibits a Role to accessing/seeing/using specifically identified objects.

[June7]

Access does not recognize Environ() function but VBA does. So build a custom function in a general module that calls Environ("USERNAME") then call the custom function from query or form.

[SirArch]

I'm not quite sure what you mean by 'valuers', though.

Thanks for the help so far.

in a form put valuers, then run the query using the form as params:

txtUser = Environ("username")
docmd.openquery "qsMyQuery"

where qsmyQuery =
select * from table where [userID] = forms!frmMain!txtUser

[SirArch]

Thanks for the tip. Not quite what I need, though. I want to grant access automatically by using username as the role definer to run the flexible query.

You may want to investigate Role Based Access Control to see how the pieces fit together.
It isn't clear what exactly you want to "control/restrict access to", nor how many users there are.

Typically you identify users and associate a user with a Role; then you have additional info that allows/prohibits a Role to accessing/seeing/using specifically identified objects.

[orange]

Here is a data model showing the entities and relationships involved in role based access control.

The model and response are located here.

[June7]

Did you see post 4?

How will you manage each user's permissions? Most likely will involve a Users table with info on user permissions.

Do records have UserID that can be used as filter criteria?

Do you want to manage availability of forms/buttons?

[ranman256]

to use it in a query , build this FUNCTION for use in queries...

Public Function getUserID() As String
getUserID = Environ("Username")
End Function

in sql ...
select getUserID() as USER from table