Limiting displayed records

  • Thread starter Thread starter Duncan Edment
  • Start date Start date
D

Duncan Edment

Hi all!

I've implemented User Level Security in the departmental
Access 2000 database. However, now what I want to do, is
take the id of the currently logged user--CurrentUser I
believe--and retrieve from the database only those records
that relate to that user. But, if I log in--as database
Administrator--it should show me all records.

Can anyone advise?

Many thanks for your help.

Duncan
 
Duncan Edment said:
Hi all!

I've implemented User Level Security in the departmental
Access 2000 database. However, now what I want to do, is
take the id of the currently logged user--CurrentUser I
believe--and retrieve from the database only those records
that relate to that user. But, if I log in--as database
Administrator--it should show me all records.
Click to expand...

You'll need to add a field to each table which holds the current user's name
(also update all existing records). Assuming you control all data access
via forms, you can set the record source in the open event based on the
current user's name. Something like

If CurrentUser()='you' then
me.RecordSource = "SELECT * from whatever"
else
me.RecordSource = "SELECT * from whatever WHERE YourField = " & CurrentUser
end if
 
Sorry for the delay in getting back Joan.

Thank you for your reply and suggestion, which works well...up to a point. My
fault for not giving you the full picture in the first place.

You see, the form contains within it two controls which retrieve their value
from auxiliary table...one for the users name and one for their current work
project. In my main table, I simply store the index key to each of the relevant
items in the auxiliary tables and would like each of these fields to display the
relevant text.

I tried to create the query in query design mode and view the SQL. Everything
went well, until I tried to paste the query into the VB code...VB doesn't like
the "INNER JOIN" statements that appeared.

Can you help?

TIA & regards.

Duncan
 
Hi Duncan,

Duncan Edment said:
You see, the form contains within it two controls which retrieve their value
from auxiliary table...one for the users name and one for their current work
project. In my main table, I simply store the index key to each of the relevant
items in the auxiliary tables and would like each of these fields to display the
relevant text.
Click to expand...

Well I'm not entirely clear about this. Are you saying you use two combo
boxes to lookup values in a table, and then you want the form to display
just the records that apply? Or something else? I thought you wanted just
the currently logged in user's records to appear?
I tried to create the query in query design mode and view the SQL. Everything
went well, until I tried to paste the query into the VB code...VB doesn't like
the "INNER JOIN" statements that appeared.
Click to expand...

Well it would never work with just a cut 'n paste. Can you post the code
segment?

Also you could build up the sql statement as a string. Then use
Debug.Print strSQL

You would then go to the debug window and copy and paste your sql statement
back into a new query and see if it works.

That's one way to track down the syntax errors.
 
Thanks for replying Joan.

Let's see is I can clear this up.

I have a form, on which there are several controls...Name, Date, Project, Start
& End. Using your earlier suggestion in the "Open" event works perfectly.
However, the Name field within my main table simply holds a numerical value that
links to an Employee table--the field originally was a combo but now, since I
put in the user level security, it no longer needs to be.

Previously, employees selected their name form the drop-down and the form
displayed their information. However now, I'd like the form to automatically
display their name and retrieve their information...something which I just can't
get it to do. I don't want to go through the database, and change al the
numeric keys to the employees name, but if I have to...

So, in a nut-shell, what I'm looking for is, how do I get what is now a simple
text control to display the name of the employee, rather than the numeric ID?

My apologies for my previous post, which did not make this at all clear.

Rgds

Duncan
 
Hi Duncan,

Now I understand. So you are displaying records based on the person's Access
login name when the form opens. What you'd like is to display their 'real'
name in a textbox. I would add a field to the Employee table called
AccessLogin, and put their username in this field. On your form you can put
a textbox, with a control source of
=DLookUp("RealName","tblEmployee","AccessLogin = '" & CurrentUser() & "'")

Substitute the field in the Employee table with their name for RealName, and
the actual name of your Employee table for tblEmployee.
The quotes may be hard to decipher so with spaces they are
....AccessLogin = ' " & currentuser() & " ' ")
 
Back
Top