rather than trying to do all of this *within* the query, suggest you set up
a "login" form. add an unbound textbox control for the user to enter the
SSN, i'll call it txtSSN. run code from the control's AfterUpdate event, or
from a command button, to check the person's "area" (admin, support, or
whatever) - which i'll assume is stored in the DutySection field, and run
the query accordingly. also suggest you bind the query to a form, rather
than opening the query directly, so you have better control of the user and
the data. example:
Dim strDuty As String
strDuty = DLookup("DutySection", "Personnel", "SSN = '" _
& Me!txtSSN & "'")
If strDuty = "Admin" Or strDuty = "Support" Then
DoCmd.OpenForm "FormName"
Else
DoCmd.OpenForm "FormName", , , "SSN = '" & Me!txtSSN & "'"
End If
hth
Darrin said:
What I am trying to do is have people enter their SSN to access the form and
only show their records. The duty section in Expr1 is to show all records if
someone in (admin or support) logs in with their SSN.
What I am trying to do is limit the amount of data any one person can view
(data they don't need). The people in admin and support need to be able to
see all info to update it as needed. I was thinking about a seperate login
event but I am back to square 1 when trying to show all if (admin or support)
logs in.
Any suggestions?
John Vinson said:
On Tue, 28 Feb 2006 19:26:27 -0800, Darrin
The attached is giving me fits. Is there any way to make this query
updateable and still do the some thing? I need to be able to view [Expr1]
because it validates a field in my table. If I don't show this field then it
works fine.
SELECT Personnel.*, (SELECT DutySection FROM Personnel WHERE SSN=[What is
your SSN]) AS Epr1, *
FROM Personnel
WHERE (((Personnel.SSN)=[What is your SSN])) OR ((((SELECT DutySection FROM
Personnel WHERE SSN=[What is your SSN])) In ('Admin','Support')));
What are you trying to ACCOMPLISH?
It looks like you're selecting all fields - including DutySection -
from Personnel, and then trying to select DutySection (again) as Expr1
from Personnel - and then selecting all fields in Personnel *AGAIN*
(the second asterisk).
Is the SSN unique within Personnel? or might you have many records
(with different DutySections) for each SSN?
As it stands, I don't understand the purpose of the query. Could you
explain the nature of the Personnel table, and - perhaps more
importantly - what you want to see?
John W. Vinson[MVP]
