Struggling with Personnel Specific Data

[bowieknife]

Hello All -
Is there a quick solution for this?

I have a TimeCard database (Access 2003) that six personnel use. There is one table that stores data for all employees and one form for entry into this table. Can I have each person "log in" and only add to/edit their records?

Thank you in advance.

JB

 

[GingerR]

Yes you can do that, but I don't know if it will be quick.
You would have a table that stores each user's login information. I use the Windows NT ID since it can be retrieved with a standard function. Otherwise, you can have each user enter an ID or password or whatever you want. So when someone opens the db, a little form pops up where they log in and hit a button which then opens the main form showing only their records.

Question: Are you using Access Security? If not, how "secure" do you want the DB to be? If you are not using that, anyone can see/edit all of the other records by getting directly into the data tables.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[bowieknife]

GingerR -
Thanks for your help...again. I'm not using Access security because the folder is maintained in a server folder that my group holds sole access to. This is a quick solution to - believe it or not - hand written timesheets. How can I make the form-fed filtering work per employee? Can you walk me through it?

JB

 

[smedvid]

Well, a couple of approaches, it really depends upon your requirements for data security... appears minimal...

Some suggestions:
1. Add code to remove ability to press Shift Key to open MS Access DB in design mode. Lots of examples of this - preform a search.
2. Create a User ID table with a User ID and Password entry and Role. Or, pick up Lan ID upon entry to database and match up to User ID Table.
3. Develop table of roles for application: User and Administrator. And, perhaps Report Only?
4. Design application form(s) to review what role a person has, and permit proper actions. For User: Set a filter on Data where User ID in Timesheet data table equals their login id. For Administrators, do not set any filter. For Read-Only, do not set a filter and set a property on form for Read-Only - prevent Add/Edit/Delete...

htwh,

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[GingerR]

first you have to tell me the structure of your table. what identifies each record to a person/employee? an ID? a name? please spell it all out, including some sample data.

what does the form look like? Form View with one record shown at a time? How do they search thru records to find one to edit? with a combo box? that would have to be changed too then.

Please supply more details. Thanks.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[smedvid]

Another option is to consider searching for an off-the-shelf (OTS) solution for time reporting... There are tons out there and provide built-in security and reports; some even permit customization. Also, search for MS Access based examples for Timesheet/Time Reporting. I recall seeing a few examples around many moons ago...

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[bowieknife]

Each record is identified to an employee by their name (first and last in same field). The form displays Mon through Sun, including date, time in, time out, and total time per day. Each record covers one week. It's pretty basic. To search through records, they use the navigation buttons. Should I send you more details?

 

[GingerR]

How does their name get in there? Do they type it in for every record?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[bowieknife]

Right now they do. I would prefer the password system take care of that for them.

 

[GingerR]

I don't know how your table is set up since you have not defined your table structure or your object/control names. You will have to tweak this to suit you.
This is quick and dirty, untested, and not as complete as I would do it.

1) Make a table called Users that holds the info of UserID and Name.
Two fields:
UserID (text)
UserName (text)
Fill it with data (6 records). I assume you are using Windows OS and you have user ID's you have to log in with every day. Put those ID's into the UserID field.

2) Add a new field (UserID) in your existing table. Write update query to fill this field with UserID's of each person from the table you just made. When you are done you can delete the UserName field from your existing table.

3) in your database window, go to the Modules tab and hit NEW. Paste in this:

Function GetUserID()
    GetUserID = Environ("Username")
End Function

3) In the same code window, paste in this:

Function OpenMainForm()
    'This opens the main form for the current user
    DoCmd.OpenForm "FormNameHere", acNormal,,"UserID = '" & GetUserID & "'"
End Function

4) File+Save and save this module, doesn't matter what you name it.

5) Make a new macro. make it's only command be
RunCode + OpenMainForm()
Close the macro and name it Autoexec. That way it will run when the db is launched.

6) On your current form change the text box for the UserName field to be for UserID. Make it's default setting be

=GetUserID()

this way the person's user ID will always just plunk in there.

These are only bits of a bigger picture. Depending on how your db is set up, you will have to tweak things and fool around to make it all work. Give it a shot and let us know how it goes.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html