load a filtered form based on user login

[najah via AccessMonster.com]

Hi all,

My question is:

I have a timesheet form that contains my depts employees timelog. One of the
fields is employeeID.

I've created a switchboard where the employee clicks on a command button and
it opens a form Timesheet which allows them to view/edit their time, However
they have the capabilities to view all 9 employee's records.

If possible, can someone help me with this task:
I've created logins and passwords for each employee

I need to create a code that does this:

If the user login=john doe then open a form so that it is filtered where the
employeeID =John Doe. This way they can only view their info.

I created an employee table/form that has [EmployeeID] and [EmployeeName]
fields and when setting up the login I just assigned the user name to match
the employeeID (consists of employees initials).

 

[shanesullaway via AccessMonster.com]

Hey Najah,

All you need to do is add criteria to the DoCmd.OpenForm line that opens your
timelog form, that equals the EmployeeID.

ie:

Dim stCriteria As String
stCriteria = "EmployeeID =" & Forms![NameOfLogInForm]![EmployeeID]
'this is if EmployeeID is numeric

use if text:
stCriteria = "EmployeeID ='" & Forms![NameOfLogInForm]![EmployeeID]
& "'"

DoCmd.OpenForm "FormNameBeingOpened",,stCriteria

HTH,
Shane

Hi all,

My question is:

I have a timesheet form that contains my depts employees timelog. One of the
fields is employeeID.

I've created a switchboard where the employee clicks on a command button and
it opens a form Timesheet which allows them to view/edit their time, However
they have the capabilities to view all 9 employee's records.

If possible, can someone help me with this task:
I've created logins and passwords for each employee

I need to create a code that does this:

If the user login=john doe then open a form so that it is filtered where the
employeeID =John Doe. This way they can only view their info.

I created an employee table/form that has [EmployeeID] and [EmployeeName]
fields and when setting up the login I just assigned the user name to match
the employeeID (consists of employees initials).

 

[najah via AccessMonster.com]

Thanks Shane,

I must let you know that I am fairly new to Access, so I am a little confused.
I created logins via the security wizard. Am I supposed to create a login
form and if so what fields do I include

Hey Najah,

All you need to do is add criteria to the DoCmd.OpenForm line that opens your
timelog form, that equals the EmployeeID.

ie:

Dim stCriteria As String
stCriteria = "EmployeeID =" & Forms![NameOfLogInForm]![EmployeeID]
'this is if EmployeeID is numeric

use if text:
stCriteria = "EmployeeID ='" & Forms![NameOfLogInForm]![EmployeeID]
& "'"

DoCmd.OpenForm "FormNameBeingOpened",,stCriteria

HTH,
Shane

Hi all,

[quoted text clipped - 18 lines]

fields and when setting up the login I just assigned the user name to match
the employeeID (consists of employees initials).

 

[Joan Wild]

If you are using the built-in security then set the record source of the
Timesheet form to
a query where the criteria under EmployeeID is =CurrentUser()

--
Joan Wild
Microsoft Access MVP

Thanks Shane,

I must let you know that I am fairly new to Access, so I am a little
confused. I created logins via the security wizard. Am I supposed to
create a login form and if so what fields do I include

Hey Najah,

All you need to do is add criteria to the DoCmd.OpenForm line that
opens your timelog form, that equals the EmployeeID.

ie:

Dim stCriteria As String
stCriteria = "EmployeeID =" &
Forms![NameOfLogInForm]![EmployeeID] 'this is if EmployeeID is
numeric

use if text:
stCriteria = "EmployeeID ='" &
Forms![NameOfLogInForm]![EmployeeID] & "'"

DoCmd.OpenForm "FormNameBeingOpened",,stCriteria

HTH,
Shane

Hi all,

[quoted text clipped - 18 lines]

fields and when setting up the login I just assigned the user name
to match the employeeID (consists of employees initials).

 

[najah via AccessMonster.com]

Joan,

Thanks for your reply. I am glad to hear from you for I am new to access as
well as access monster and as I read through the various forums I find you
very knowledgeable.

Let me explain in detail what goes on with this db.

On my switchboard there is a command button to enter time which launches the
Timesheet form in add mode. The employee selects his name from a drop down
and enters a start and end date to reflect their biweekly time. They then
enter their details of a workday in a subform entitled Timesheet Hours. After
order entry their is a button on the form that opens a report which allows
for printing. The recordsource for this form is a table entitled Timesheet.

Their is also a button on the switchboard that allows them to view/edit their
timesheet in the event they come across an error once they have printed their
timesheet.

of course, it is when they view/edit their timesheet is when i need the form
to open for them to view just their time log.

I created a query with the criteria for EmployeeID=CurrentUser() and changed
the recordsource to match the
query so when I click the command button on the switchborad to edit/view
timesheet it opens based on the user. However when I click the button to
enter time the form doesn't load because by changing the recordsource to
match the query it affected Timesheet in add mode.

Also, I need the supervisors to be able to view all the records not just
theirs and with employeeID=currentuser that limits their view also.

I hope I clarified this, please help me it is driving me crazy

Najah

If you are using the built-in security then set the record source of the
Timesheet form to
a query where the criteria under EmployeeID is =CurrentUser()

Thanks Shane,

[quoted text clipped - 32 lines]

 

[Joan Wild]

On my switchboard there is a command button to enter time which
launches the Timesheet form in add mode. The
recordsource for this form is a table entitled Timesheet.

Their is also a button on the switchboard that allows them to
view/edit their timesheet in the event they come across an error once
they have printed their timesheet.

I created a query with the criteria for EmployeeID=CurrentUser() and
changed the recordsource to match the
query so when I click the command button on the switchborad to
edit/view timesheet it opens based on the user. However when I
click the button to enter time the form doesn't load because by
changing the recordsource to match the query it affected Timesheet in
add mode.

This makes no sense. You said the enter timesheet was based on a table
called Timesheet. The view/edit timesheet you said is based on a query.
Changing the query has no impact on the table, so should not affect your
enter timesheet. Please clarify.

Also, I need the supervisors to be able to view all the records not
just theirs and with employeeID=currentuser that limits their view
also.

You can set the recordsource at runtime. There is a function in the
security FAQ you can use to determine if the currentuser is a member of a
security group. Use that to determine if they are a supervisor. i.e.

If faq_IsUserInGroup("Supervisors",CurrentUser) then
Me.RecordSource = "SELECT * FROM TimeSheet"
Else
Me.RecordSource = "SELECT * FROM TimeSheet WHERE [EmployeeID] = " &
chr(34) & CurrentUser() & chr(34)
End If

You could put that in the open event of the view/edit form.

 

[najah via AccessMonster.com]

I apologize if it makes no sense. I'll try to clarify:

The enter time and edit/view timesheet are both based on the frmTimesheet
(recordsource is tblTimesheet). It is only the mode in which it opens, that
is different. When they enter time, the form opens in add mode, when they
edit/view time, the form opens in edit mode. I followed your advice and
created a query with the criteria for the employee to be current user.
Because both of these tasks( enter time and view/edit ) launch the
frmTimesheet, when I changed the recordsource to match the new query to
edit/view, It caused the enter time not to run properly.

On my switchboard there is a command button to enter time which
launches the Timesheet form in add mode. The

[quoted text clipped - 11 lines]

changing the recordsource to match the query it affected Timesheet in
add mode.

This makes no sense. You said the enter timesheet was based on a table
called Timesheet. The view/edit timesheet you said is based on a query.
Changing the query has no impact on the table, so should not affect your
enter timesheet. Please clarify.

Also, I need the supervisors to be able to view all the records not
just theirs and with employeeID=currentuser that limits their view
also.

You can set the recordsource at runtime. There is a function in the
security FAQ you can use to determine if the currentuser is a member of a
security group. Use that to determine if they are a supervisor. i.e.

If faq_IsUserInGroup("Supervisors",CurrentUser) then
Me.RecordSource = "SELECT * FROM TimeSheet"
Else
Me.RecordSource = "SELECT * FROM TimeSheet WHERE [EmployeeID] = " &
chr(34) & CurrentUser() & chr(34)
End If

You could put that in the open event of the view/edit form.

 

[Joan Wild]

OK, that is clearer, however changing the recordsource should not cause a
problem.

Whether they are adding their time (opening the form for add data only), or
editing existing records, you'd want the recordsource to be restricted to
their username (except for the supervisors). So I don't understand how
changing the recordsource to a query, caused a problem for adding records?


--
Joan Wild
Microsoft Access MVP

I apologize if it makes no sense. I'll try to clarify:

The enter time and edit/view timesheet are both based on the
frmTimesheet (recordsource is tblTimesheet). It is only the mode in
which it opens, that is different. When they enter time, the form
opens in add mode, when they edit/view time, the form opens in edit
mode. I followed your advice and created a query with the criteria
for the employee to be current user. Because both of these tasks(
enter time and view/edit ) launch the frmTimesheet, when I changed
the recordsource to match the new query to edit/view, It caused the
enter time not to run properly.

On my switchboard there is a command button to enter time which
launches the Timesheet form in add mode. The

[quoted text clipped - 11 lines]

changing the recordsource to match the query it affected Timesheet
in add mode.

This makes no sense. You said the enter timesheet was based on a
table called Timesheet. The view/edit timesheet you said is based
on a query. Changing the query has no impact on the table, so should
not affect your enter timesheet. Please clarify.

Also, I need the supervisors to be able to view all the records not
just theirs and with employeeID=currentuser that limits their view
also.

You can set the recordsource at runtime. There is a function in the
security FAQ you can use to determine if the currentuser is a member
of a security group. Use that to determine if they are a
supervisor. i.e.

If faq_IsUserInGroup("Supervisors",CurrentUser) then
Me.RecordSource = "SELECT * FROM TimeSheet"
Else
Me.RecordSource = "SELECT * FROM TimeSheet WHERE [EmployeeID] = "
& chr(34) & CurrentUser() & chr(34)
End If

You could put that in the open event of the view/edit form.

 

[najah via AccessMonster.com]

Joan,

The query is just not working for me. I wish it did because it seems to be
the easiest way expecially because I'm not an access expert.

Is the following code an alternative to creating the query or something I
have to do in addition to changing the recordsource to the query?

Also, I created a group(Supervisors) and added they supervisors to this group,
however when I attempted to insert the code I got a compile error: Sub or
Function not defined for the faq_IsUserInGroup. Is this something I had to do
to this code to customize it to my db or could I simply copy and paste it
into the onopen event of the form? I really appreciate your patience with
this novice.


If faq_IsUserInGroup("Supervisors",CurrentUser) then
Me.RecordSource = "SELECT * FROM TimeSheet"
Else
Me.RecordSource = "SELECT * FROM TimeSheet WHERE [EmployeeID] = " &
chr(34) & CurrentUser() & chr(34)
End If

OK, that is clearer, however changing the recordsource should not cause a
problem.

Whether they are adding their time (opening the form for add data only), or
editing existing records, you'd want the recordsource to be restricted to
their username (except for the supervisors). So I don't understand how
changing the recordsource to a query, caused a problem for adding records?

I apologize if it makes no sense. I'll try to clarify:

[quoted text clipped - 40 lines]

 

[Joan Wild]

Joan,

Is the following code an alternative to creating the query or
something I have to do in addition to changing the recordsource to
the query?

Also, I created a group(Supervisors) and added they supervisors to
this group, however when I attempted to insert the code I got a
compile error: Sub or Function not defined for the faq_IsUserInGroup.
Is this something I had to do to this code to customize it to my db
or could I simply copy and paste it into the onopen event of the
form? I really appreciate your patience with this novice.

Create a new module and copy and paste the faq_IsUserInGroup code from the
security FAQ which you can download from
http://support.microsoft.com/?id=207793
Save the module giving a different name than the function.

Then in the On Open property for your form, choose Event Procedure, and then
click on the build button (...) Between the two lines put the following:

If faq_IsUserInGroup("Supervisors",CurrentUser) then
Me.RecordSource = "SELECT * FROM TimeSheet"
Else
Me.RecordSource = "SELECT * FROM TimeSheet WHERE [EmployeeID] = " &
chr(34) & CurrentUser() & chr(34)
End If

Watch for newsreader wrap on the second Me.RecordSource line - it's all on
one line. Go to Debug menu and choose Compile - you should get no errors
(if you do post back). Then click on the save button. Close this window
and try your form.

 

[najah via AccessMonster.com]

Joan,

Still not working. I'm getting compile error; User defined type not defined

This is the code in which I based the module

Function faq_IsUserInGroup (strGroup As String, strUser as String) As Integer
' Returns True if user is in group, False otherwise
' This only works if you're a member of the Admins group.
Dim ws As WorkSpace
Dim grp As Group
Dim strUserName as string

Set ws = DBEngine.Workspaces(0)
Set grp = ws.Groups(strGroup)
On Error Resume Next
strUserName = ws.groups(strGroup).users(strUser).Name
faq_IsUserInGroup = (Err = 0)
End Function

When the error occurs the
Dim ws As WorkSpace is selected

I think this is getting over my head

Joan,

[quoted text clipped - 8 lines]

or could I simply copy and paste it into the onopen event of the
form? I really appreciate your patience with this novice.

Create a new module and copy and paste the faq_IsUserInGroup code from the
security FAQ which you can download from
http://support.microsoft.com/?id=207793
Save the module giving a different name than the function.

Then in the On Open property for your form, choose Event Procedure, and then
click on the build button (...) Between the two lines put the following:

If faq_IsUserInGroup("Supervisors",CurrentUser) then
Me.RecordSource = "SELECT * FROM TimeSheet"
Else
Me.RecordSource = "SELECT * FROM TimeSheet WHERE [EmployeeID] = " &
chr(34) & CurrentUser() & chr(34)
End If

Watch for newsreader wrap on the second Me.RecordSource line - it's all on
one line. Go to Debug menu and choose Compile - you should get no errors
(if you do post back). Then click on the save button. Close this window
and try your form.

 

[Joan Wild]

Open the module, Tools, References. Put a check beside
Microsoft DAO 3.6 Object Library
If you have a check beside Microsoft ActiveX Data Objects ...Library -
remove the check as you are unlikely to use this.

Click OK and try compile again.


--
Joan Wild
Microsoft Access MVP

Joan,

Still not working. I'm getting compile error; User defined type not
defined

This is the code in which I based the module

Function faq_IsUserInGroup (strGroup As String, strUser as String) As
Integer ' Returns True if user is in group, False otherwise
' This only works if you're a member of the Admins group.
Dim ws As WorkSpace
Dim grp As Group
Dim strUserName as string

Set ws = DBEngine.Workspaces(0)
Set grp = ws.Groups(strGroup)
On Error Resume Next
strUserName = ws.groups(strGroup).users(strUser).Name
faq_IsUserInGroup = (Err = 0)
End Function

When the error occurs the
Dim ws As WorkSpace is selected

I think this is getting over my head

Joan,

[quoted text clipped - 8 lines]

or could I simply copy and paste it into the onopen event of the
form? I really appreciate your patience with this novice.

Create a new module and copy and paste the faq_IsUserInGroup code
from the security FAQ which you can download from
http://support.microsoft.com/?id=207793
Save the module giving a different name than the function.

Then in the On Open property for your form, choose Event Procedure,
and then click on the build button (...) Between the two lines put
the following:

If faq_IsUserInGroup("Supervisors",CurrentUser) then
Me.RecordSource = "SELECT * FROM TimeSheet"
Else
Me.RecordSource = "SELECT * FROM TimeSheet WHERE [EmployeeID] = "
& chr(34) & CurrentUser() & chr(34)
End If

Watch for newsreader wrap on the second Me.RecordSource line - it's
all on one line. Go to Debug menu and choose Compile - you should
get no errors (if you do post back). Then click on the save button.
Close this window and try your form.