Multiple users, how can users view only their entries

[Marie-Lynn]

I have a databse which has been split. I have about 6 users and I want to
tailor their front-end forms so that when they browse back through their
records (which, in this case, is done on the same form they enter data on),
they can view onyl their information.

How do I tailor a front-end form to view only records where a certain field
(their name) will return only the forms where that field is true? Do I have
to create a query or something or is there a way to modify the main form to
allow only the forms where this field is true to be viewable?

Am I making sense?

thanks in advance,
ML

 

[Ken Sheridan]

Base the form on a query which restricts the result set to those rows where
the value of the field in question matches the current user. Users will need
to log in somehow of course. In pre-2007 version of Access you can implement
user and group security, so each user logs into the database. Or you can get
the current user's Windows log-in name by putting the following module in the
database and calling the GetUser() function. Be sure you save the module
under a different name to that of the function, e.g. mdlGetUser:

''''module starts''''
Option Compare Database
Option Explicit

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal _
lpBuffer As String, nSize As Long) As Long


Public Function GetUser() As String

Dim strBuffer As String
Dim lngSize As Long, lngRetVal As Long

lngSize = 199
strBuffer = String$(200, 0)

lngRetVal = GetUserName(strBuffer, lngSize)

GetUser = Left$(strBuffer, lngSize - 1)

End Function
''''module ends'''

If you use the latter method and the field in which the user names are
stored is called UserName for instance then a query would be something like
this:

SELECT *
FROM [YourTable]
WHERE [UserName] = GetUser();

You can use the same function to automatically insert the current user's
system log-in name into the UserName field by putting this in the form's
BeforeInsert event procedure:

Me.[UserName] = GetUser()

You can find a demo of how this sort of 'row level security' can be set up
at the following link:


http://community.netscape.com/n/pfx...libraryMessages&webtag=ws-msdevapps&tid=24069


The demo *simulates* how it can be done rather than actually implementing it
as the 'log-in' is within the demo database itself, not into the system, but
the principles are the same. Bear in mind, however, that unless you have
implemented true user and group security it won't be difficult for users to
circumvent the row level security by accessing the underlying table directly.

Ken Sheridan
Stafford, England

 

[Marie-Lynn]

I am trying to stay away from using the user and security groups as our
office will most likely migrate to 2007 in the not-too-distant-future.
Additionally, basing it on Windows user's login information sounds a little
mare advanced then I'll be in the no-too-distant-future. So I am choosing
your first suggestiong, bsing it on a query tailored within the front-end
report, but (here is how green I am), how can you base the whole FORM on a
query? Does that not have to be a seperate form?

Thank you in advance for your help.

ML

Base the form on a query which restricts the result set to those rows where
the value of the field in question matches the current user. Users will need
to log in somehow of course. In pre-2007 version of Access you can implement
user and group security, so each user logs into the database. Or you can get
the current user's Windows log-in name by putting the following module in the
database and calling the GetUser() function. Be sure you save the module
under a different name to that of the function, e.g. mdlGetUser:

''''module starts''''
Option Compare Database
Option Explicit

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal _
lpBuffer As String, nSize As Long) As Long


Public Function GetUser() As String

Dim strBuffer As String
Dim lngSize As Long, lngRetVal As Long

lngSize = 199
strBuffer = String$(200, 0)

lngRetVal = GetUserName(strBuffer, lngSize)

GetUser = Left$(strBuffer, lngSize - 1)

End Function
''''module ends'''

If you use the latter method and the field in which the user names are
stored is called UserName for instance then a query would be something like
this:

SELECT *
FROM [YourTable]
WHERE [UserName] = GetUser();

You can use the same function to automatically insert the current user's
system log-in name into the UserName field by putting this in the form's
BeforeInsert event procedure:

Me.[UserName] = GetUser()

You can find a demo of how this sort of 'row level security' can be set up
at the following link:


http://community.netscape.com/n/pfx...libraryMessages&webtag=ws-msdevapps&tid=24069


The demo *simulates* how it can be done rather than actually implementing it
as the 'log-in' is within the demo database itself, not into the system, but
the principles are the same. Bear in mind, however, that unless you have
implemented true user and group security it won't be difficult for users to
circumvent the row level security by accessing the underlying table directly.

Ken Sheridan
Stafford, England

I have a databse which has been split. I have about 6 users and I want to
tailor their front-end forms so that when they browse back through their
records (which, in this case, is done on the same form they enter data on),
they can view onyl their information.

How do I tailor a front-end form to view only records where a certain field
(their name) will return only the forms where that field is true? Do I have
to create a query or something or is there a way to modify the main form to
allow only the forms where this field is true to be viewable?

Am I making sense?

thanks in advance,
ML

 

[Ken Sheridan]

You simply set the form's RecordSource property to the name of the query.
You can either modify the design of the query in each copy of the front end
so the criterion on the UserName column (or whatever you call it) is a unique
name for the current user, e.g.

SELECT *
FROM MyTable
WHERE UserName = "PaterAeneas";

In query design view you'd simply enter KenSheridan into the criteria row of
the UserName column.

Then in each copy of the front end amend the BeforeInsert event procedure of
the form bound to this query so it enters the same name:

Me.UserName = " PaterAeneas"

Or you could have some sort of dummy log-in form in the front end when it
opens so the user inserts their name and its then assigned to a global
variable, txtUserName say. Queries can't se variables however, so you'd
need to write little function to get the name. The module containing the
variable declaration and the function would thus look like this:

Option Compare Database
Option Explicit

Public txtUserName As String

Public Function GetUser()

GetUser = txtUserName

End Function

You'd then call the function in the query:

SELECT *
FROM MyTable
WHERE UserName = GetUser()

and when inserting a new record:

Me.UserName = GetUser()

The user names would need to be secure of course, so users should be told
to make them cryptic; they are really passwords. Remember that this is very
low level security though.

Ken Sheridan
Stafford, England

I am trying to stay away from using the user and security groups as our
office will most likely migrate to 2007 in the not-too-distant-future.
Additionally, basing it on Windows user's login information sounds a little
mare advanced then I'll be in the no-too-distant-future. So I am choosing
your first suggestiong, bsing it on a query tailored within the front-end
report, but (here is how green I am), how can you base the whole FORM on a
query? Does that not have to be a seperate form?

Thank you in advance for your help.

ML

Base the form on a query which restricts the result set to those rows where
the value of the field in question matches the current user. Users will need
to log in somehow of course. In pre-2007 version of Access you can implement
user and group security, so each user logs into the database. Or you can get
the current user's Windows log-in name by putting the following module in the
database and calling the GetUser() function. Be sure you save the module
under a different name to that of the function, e.g. mdlGetUser:

''''module starts''''
Option Compare Database
Option Explicit

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal _
lpBuffer As String, nSize As Long) As Long


Public Function GetUser() As String

Dim strBuffer As String
Dim lngSize As Long, lngRetVal As Long

lngSize = 199
strBuffer = String$(200, 0)

lngRetVal = GetUserName(strBuffer, lngSize)

GetUser = Left$(strBuffer, lngSize - 1)

End Function
''''module ends'''

If you use the latter method and the field in which the user names are
stored is called UserName for instance then a query would be something like
this:

SELECT *
FROM [YourTable]
WHERE [UserName] = GetUser();

You can use the same function to automatically insert the current user's
system log-in name into the UserName field by putting this in the form's
BeforeInsert event procedure:

Me.[UserName] = GetUser()

You can find a demo of how this sort of 'row level security' can be set up
at the following link:


http://community.netscape.com/n/pfx...libraryMessages&webtag=ws-msdevapps&tid=24069


The demo *simulates* how it can be done rather than actually implementing it
as the 'log-in' is within the demo database itself, not into the system, but
the principles are the same. Bear in mind, however, that unless you have
implemented true user and group security it won't be difficult for users to
circumvent the row level security by accessing the underlying table directly.

Ken Sheridan
Stafford, England

I have a databse which has been split. I have about 6 users and I want to
tailor their front-end forms so that when they browse back through their
records (which, in this case, is done on the same form they enter data on),
they can view onyl their information.

How do I tailor a front-end form to view only records where a certain field
(their name) will return only the forms where that field is true? Do I have
to create a query or something or is there a way to modify the main form to
allow only the forms where this field is true to be viewable?

Am I making sense?

thanks in advance,
ML

 

[Marie-Lynn]

Oh. My. God! I think, for the first time, I basically understand an answer
with some slighter-more-than-really-really-really-beginner steps.

Thank you for this feedback. I will try to impliment what you are
suggesting, though it will take a bit of time. I will post when I have
results.

Thanks again,
ML

You simply set the form's RecordSource property to the name of the query.
You can either modify the design of the query in each copy of the front end
so the criterion on the UserName column (or whatever you call it) is a unique
name for the current user, e.g.

SELECT *
FROM MyTable
WHERE UserName = "PaterAeneas";

In query design view you'd simply enter KenSheridan into the criteria row of
the UserName column.

Then in each copy of the front end amend the BeforeInsert event procedure of
the form bound to this query so it enters the same name:

Me.UserName = " PaterAeneas"

Or you could have some sort of dummy log-in form in the front end when it
opens so the user inserts their name and its then assigned to a global
variable, txtUserName say. Queries can't se variables however, so you'd
need to write little function to get the name. The module containing the
variable declaration and the function would thus look like this:

Option Compare Database
Option Explicit

Public txtUserName As String

Public Function GetUser()

GetUser = txtUserName

End Function

You'd then call the function in the query:

SELECT *
FROM MyTable
WHERE UserName = GetUser()

and when inserting a new record:

Me.UserName = GetUser()

The user names would need to be secure of course, so users should be told
to make them cryptic; they are really passwords. Remember that this is very
low level security though.

Ken Sheridan
Stafford, England

I am trying to stay away from using the user and security groups as our
office will most likely migrate to 2007 in the not-too-distant-future.
Additionally, basing it on Windows user's login information sounds a little
mare advanced then I'll be in the no-too-distant-future. So I am choosing
your first suggestiong, bsing it on a query tailored within the front-end
report, but (here is how green I am), how can you base the whole FORM on a
query? Does that not have to be a seperate form?

Thank you in advance for your help.

ML

Base the form on a query which restricts the result set to those rows where
the value of the field in question matches the current user. Users will need
to log in somehow of course. In pre-2007 version of Access you can implement
user and group security, so each user logs into the database. Or you can get
the current user's Windows log-in name by putting the following module in the
database and calling the GetUser() function. Be sure you save the module
under a different name to that of the function, e.g. mdlGetUser:

''''module starts''''
Option Compare Database
Option Explicit

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal _
lpBuffer As String, nSize As Long) As Long


Public Function GetUser() As String

Dim strBuffer As String
Dim lngSize As Long, lngRetVal As Long

lngSize = 199
strBuffer = String$(200, 0)

lngRetVal = GetUserName(strBuffer, lngSize)

GetUser = Left$(strBuffer, lngSize - 1)

End Function
''''module ends'''

If you use the latter method and the field in which the user names are
stored is called UserName for instance then a query would be something like
this:

SELECT *
FROM [YourTable]
WHERE [UserName] = GetUser();

You can use the same function to automatically insert the current user's
system log-in name into the UserName field by putting this in the form's
BeforeInsert event procedure:

Me.[UserName] = GetUser()

You can find a demo of how this sort of 'row level security' can be set up
at the following link:


http://community.netscape.com/n/pfx...libraryMessages&webtag=ws-msdevapps&tid=24069


The demo *simulates* how it can be done rather than actually implementing it
as the 'log-in' is within the demo database itself, not into the system, but
the principles are the same. Bear in mind, however, that unless you have
implemented true user and group security it won't be difficult for users to
circumvent the row level security by accessing the underlying table directly.

Ken Sheridan
Stafford, England

:

I have a databse which has been split. I have about 6 users and I want to
tailor their front-end forms so that when they browse back through their
records (which, in this case, is done on the same form they enter data on),
they can view onyl their information.

How do I tailor a front-end form to view only records where a certain field
(their name) will return only the forms where that field is true? Do I have
to create a query or something or is there a way to modify the main form to
allow only the forms where this field is true to be viewable?

Am I making sense?

thanks in advance,
ML