query based on user login

[PETER]

I have a two level security system to protect my data. The first uses the MS
Access security, the second is a simple login system wherby a user enters
their name and personal password to gain a customised view of the screen eg
if the user is me, I can see all tool bars, other users cannot. What I would
like to be able to do is write a query wherby I can use the users login
details to display only their data. An example of the script I built is as
follows

'IF USERNAME AND PASSWORDS CORRECT THEN LOGIN TO THE MAIN SWITCHBOARD

If Me.qpwd.Value = DLookup("PWD", "AUTHORISED", "USERID = '" &
Me.quserid.Value & " '") Or UCase$(Me.qpwd.Value) = "abcd" Then
DoCmd.Close
DoCmd.OpenForm "Course Booking System"
Else
MsgBox "Invalid Password"
End If

Is there a way of including a select criteria like the following

SELECT USERID, DETAIL
FROM COURSE
WHERE USERID="Me.qpwd.Value"

I have tried several differnet versions for this syntax without success!

Any help would be greatly appreciated.

 

[Ofer]

Try this

SELECT USERID, DETAIL
FROM COURSE
WHERE USERID=Forms![EnterHereLoginFormName]![qpwd]

but in this statement you are filtering the UserId with the Password field.
Mybe, that what you are looking for
SELECT USERID, DETAIL
FROM COURSE
WHERE USERID=Forms![EnterHereLoginFormName]![quserid]

 

[PETER]

Hi and thanks for the reply. I corrected the error so that the query looks
for the userid rather than the password. Unfortunately it did not work!
When I run the query I get a request to enter the userid. Could this be
because the login form closes once the user has logged in? If so, is there a
way of storing the user login details in a variable after the form has closed?

Try this

SELECT USERID, DETAIL
FROM COURSE
WHERE USERID=Forms![EnterHereLoginFormName]![qpwd]

but in this statement you are filtering the UserId with the Password field.
Mybe, that what you are looking for
SELECT USERID, DETAIL
FROM COURSE
WHERE USERID=Forms![EnterHereLoginFormName]![quserid]

--
\\// Live Long and Prosper \\//
BS"D

I have a two level security system to protect my data. The first uses the MS
Access security, the second is a simple login system wherby a user enters
their name and personal password to gain a customised view of the screen eg
if the user is me, I can see all tool bars, other users cannot. What I would
like to be able to do is write a query wherby I can use the users login
details to display only their data. An example of the script I built is as
follows

'IF USERNAME AND PASSWORDS CORRECT THEN LOGIN TO THE MAIN SWITCHBOARD

If Me.qpwd.Value = DLookup("PWD", "AUTHORISED", "USERID = '" &
Me.quserid.Value & " '") Or UCase$(Me.qpwd.Value) = "abcd" Then
DoCmd.Close
DoCmd.OpenForm "Course Booking System"
Else
MsgBox "Invalid Password"
End If

Is there a way of including a select criteria like the following

SELECT USERID, DETAIL
FROM COURSE
WHERE USERID="Me.qpwd.Value"

I have tried several differnet versions for this syntax without success!

Any help would be greatly appreciated.

 

[Ofer]

Yes, it is because the form is closed
====================================
To overcome this problem, create a Global variable to holds the user id in
one of the modules.
Global UserId As Long ' enter the type of the field
====================================
In the same module create a function that return the Id
Function GetUserId()
GetUserId = UserId
End Function
===================================
In the Login form initiate the userid variable
UserId = Me.quserid
===================================
Change the query to
SELECT USERID, DETAIL
FROM COURSE
WHERE USERID=GetUserId()
===================================
Remember that the variable and the function need to be kept in a module and
not the form

--
\\// Live Long and Prosper \\//
BS"D

Hi and thanks for the reply. I corrected the error so that the query looks
for the userid rather than the password. Unfortunately it did not work!
When I run the query I get a request to enter the userid. Could this be
because the login form closes once the user has logged in? If so, is there a
way of storing the user login details in a variable after the form has closed?

Try this

SELECT USERID, DETAIL
FROM COURSE
WHERE USERID=Forms![EnterHereLoginFormName]![qpwd]

but in this statement you are filtering the UserId with the Password field.
Mybe, that what you are looking for
SELECT USERID, DETAIL
FROM COURSE
WHERE USERID=Forms![EnterHereLoginFormName]![quserid]

--
\\// Live Long and Prosper \\//
BS"D

I have a two level security system to protect my data. The first uses the MS
Access security, the second is a simple login system wherby a user enters
their name and personal password to gain a customised view of the screen eg
if the user is me, I can see all tool bars, other users cannot. What I would
like to be able to do is write a query wherby I can use the users login
details to display only their data. An example of the script I built is as
follows

'IF USERNAME AND PASSWORDS CORRECT THEN LOGIN TO THE MAIN SWITCHBOARD

If Me.qpwd.Value = DLookup("PWD", "AUTHORISED", "USERID = '" &
Me.quserid.Value & " '") Or UCase$(Me.qpwd.Value) = "abcd" Then
DoCmd.Close
DoCmd.OpenForm "Course Booking System"
Else
MsgBox "Invalid Password"
End If

Is there a way of including a select criteria like the following

SELECT USERID, DETAIL
FROM COURSE
WHERE USERID="Me.qpwd.Value"

I have tried several differnet versions for this syntax without success!

Any help would be greatly appreciated.

 

[John Spencer]

I would recommend that the OP just hide the form (Forms!FormName.Visible =
False versus closing the form) and use the form control reference rather
than populate a global variable. Global variables are nice as long as you
don't run into any unhandled errors in the code. In that case, all
variables are likely to get re-initialized - while the form will retain the
value.

Yes, it is because the form is closed
====================================
To overcome this problem, create a Global variable to holds the user id in
one of the modules.
Global UserId As Long ' enter the type of the field
====================================
In the same module create a function that return the Id
Function GetUserId()
GetUserId = UserId
End Function
===================================
In the Login form initiate the userid variable
UserId = Me.quserid
===================================
Change the query to
SELECT USERID, DETAIL
FROM COURSE
WHERE USERID=GetUserId()
===================================
Remember that the variable and the function need to be kept in a module
and
not the form

--
\\// Live Long and Prosper \\//
BS"D

Hi and thanks for the reply. I corrected the error so that the query
looks
for the userid rather than the password. Unfortunately it did not work!
When I run the query I get a request to enter the userid. Could this be
because the login form closes once the user has logged in? If so, is
there a
way of storing the user login details in a variable after the form has
closed?

Try this

SELECT USERID, DETAIL
FROM COURSE
WHERE USERID=Forms![EnterHereLoginFormName]![qpwd]

but in this statement you are filtering the UserId with the Password
field.
Mybe, that what you are looking for
SELECT USERID, DETAIL
FROM COURSE
WHERE USERID=Forms![EnterHereLoginFormName]![quserid]

--
\\// Live Long and Prosper \\//
BS"D


:

I have a two level security system to protect my data. The first uses
the MS
Access security, the second is a simple login system wherby a user
enters
their name and personal password to gain a customised view of the
screen eg
if the user is me, I can see all tool bars, other users cannot. What
I would
like to be able to do is write a query wherby I can use the users
login
details to display only their data. An example of the script I built
is as
follows

'IF USERNAME AND PASSWORDS CORRECT THEN LOGIN TO THE MAIN SWITCHBOARD

If Me.qpwd.Value = DLookup("PWD", "AUTHORISED", "USERID = '" &
Me.quserid.Value & " '") Or UCase$(Me.qpwd.Value) = "abcd" Then
DoCmd.Close
DoCmd.OpenForm "Course Booking System"
Else
MsgBox "Invalid Password"
End If

Is there a way of including a select criteria like the following

SELECT USERID, DETAIL
FROM COURSE
WHERE USERID="Me.qpwd.Value"

I have tried several differnet versions for this syntax without
success!

Any help would be greatly appreciated.