Form with 3 subforms question

G

Guest

First of all, I want to thank all the people who take time to answer
questions in this newsgroup. By following Rick B.'s advice on searching for
specific wordage, I've found so many answers to problems I've been having
with a database I'm trying to create. Being given hints on words to do
searches on helped quite a bit and gave me ideas for how to design the
database to work the way I want it to.

Basically, I have a database that will be used to help accrue for unbilled
month-end costs associated with some consultants. I have one form that will
allow them to enter their timesheet data, any expenses and mileage that we
will reimburse them for. This form has three subforms based on the header
entry of consultant name and week ending date. Right now, the form and
subform's record source is set to the individual tables. I've tested the
form to see that the data in the individual tables are updated the way I want
them to and it works as expected.

I plan on setting up user security after I get everything working correctly.
I'm still tackling the Security FAQ before I do that, though. I will follow
the advice given in other posts to lock the records so that a consultant will
only be able to view/edit/add data related to him or her, i.e. CurrentUser( ).

From my research I know that I need to base this timesheet form on a query
to get it to work the way I want (locking records). However, when I base it
on a select query, the form is blank as there is no data in the tables. When
I put in dummy data in the tables involved, the form has the fields showing.
Is this the type of query I need to use to lock the records and let the
consultants only use data pertaining to them? If so, what settings do I need
to use to avoid having a blank page?

It's been a while since I've had programming classes, but I'm slowly getting
reacquainted with VBA code. Thank you in advance for any help you can
provide.

Suzann
 
G

Guest

Suzann:

Its not question of locking records as such; that refers to how Access
prevents conflicts when two or more users try to update the same record(s)
simultaneously. In your case you want to restrict access to individual
records on the basis of who is the current user; what is known as 'row level
security'. This means storing data about the user(s) who are permitted
access to each record in a table in that record and then using queries so
that users can only access the relevant records. In the simplest scenario
where each record is limited to just one user then you can simply store their
user name in a column called PermittedUser say, and restrict the query along
the lines of:

WHERE PermittedUser = CurrentUser() OR CurrentUser = "DataManager"

DataManager here is the user name of the database administrator, so if they
log on as the administrator they can access all records. Its important of
course that users are denied any means of changing the PermittedUser values
in the table, which means using Access Security as detailed in the SecFAQ
article.

More commonly row level security is applied via groups rather than
individual users. The basic principle is the same here but records need to
store data about one or more groups and it’s the group to which the users
belong which determine which records they can access. You'll find my demo of
one way of doing this at:

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

The demo does not implement security itself, but simulates it by storing the
user names in a table rather than getting the current user with the
CurrentUser function. The only real difference, however, is that in a real
life application the latter would be used rather than the custom
GetCurrentUser function in the demo.

Ken Sheridan
Stafford, England
 
G

Guest

Thank you so much for your reply, Ken. I'll try this on the database this
weekend. I usually build databases for my own use where I'm the only one
working on the data, or linking to pre-existing ODBC tables. I'll
eventually split this database and set the security as mentioned in the
article. In turn, I'll link the timesheet data to another database with
actual GL account data and invoice history data that I've made for
management. Once again, thanks so much to all of y'all for sharing your
expertise. I've bookmarked a lot of the sites mentioned in other posts so I
can have handy references available for future use.

Suzann
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top