CurrentUser and WhoOwns

[Joy]

I posted this last week and received this very good advice but it isn't
working the way I need it to work. It works great if you
want all records, but the form must open records between a begin and end
work date. The form must return records from two tables in a one to many
relationship. One table (the parent)
stores the employee that entered the hours and WhoOwns the record, and the
second table (the child) stores the dates worked, hours etc. An unbound
form would be used to type in
the begin and end dates. I've been working on this for days. Can't believe
it's that complicated.

I created an unbound form with date criteria fields and a field called
WhoOwns with a control source of =CurrentUser.

Then tried to set the criteria to open the form (the one with the criteria
records) with a criteria string stating the following:
stLinkCriteria = "[WhoOwns]=" & Me!WhoOwns
DoCmd.OpenForm stDocName, , , stLinkCriteria
(Above - Me!WhoOwns being the control on the unbound criteria form)

The query the form is bound to has the following in the criteria field of
WorkDate: >=Forms!dlgDateRange!BeginDate AND <=Forms!dlgDateRange!EndDate

I get an error message shen I click the open form button stating "Syntax
error (missing operator) in query expression "WhoOwns = name of CurrentUser"
The current user is the name of the CurrentUser and the criteria string is
in the OnClick event of the unbound criteria form.

I've tried to do this more times than I can now count. What I doing wrong.
I hope I am explaining clearly.
Joy

 

[Keith Wilby]

stLinkCriteria = "[WhoOwns]=" & Me!WhoOwns

Try

stLinkCriteria = "[WhoOwns] = ' " & Me!WhoOwns & " ' "

(extra spaces added for clarity).

Keith.
www.keithwilby.com

 

[Joan Wild]

You really don't need a textbox on your unbound form for the CurrentUser.

But if you want to include it, be sure to set the locked property to no (or
the user could just change it).

Change
stLinkCriteria = "[WhoOwns]=" & Me!WhoOwns
to
stLinkCriteria = "[WhoOwns]=" & chr(34) & Me!WhoOwns & chr(34)


--
Joan Wild
Microsoft Access MVP

I posted this last week and received this very good advice but it isn't
working the way I need it to work. It works great if you
want all records, but the form must open records between a begin and end
work date. The form must return records from two tables in a one to many
relationship. One table (the parent)
stores the employee that entered the hours and WhoOwns the record, and the
second table (the child) stores the dates worked, hours etc. An unbound
form would be used to type in
the begin and end dates. I've been working on this for days. Can't
believe it's that complicated.

I created an unbound form with date criteria fields and a field called
WhoOwns with a control source of =CurrentUser.

Then tried to set the criteria to open the form (the one with the criteria
records) with a criteria string stating the following:
stLinkCriteria = "[WhoOwns]=" & Me!WhoOwns
DoCmd.OpenForm stDocName, , , stLinkCriteria
(Above - Me!WhoOwns being the control on the unbound criteria form)

The query the form is bound to has the following in the criteria field of
WorkDate: >=Forms!dlgDateRange!BeginDate AND <=Forms!dlgDateRange!EndDate

I get an error message shen I click the open form button stating "Syntax
error (missing operator) in query expression "WhoOwns = name of
CurrentUser"
The current user is the name of the CurrentUser and the criteria string is
in the OnClick event of the unbound criteria form.

I've tried to do this more times than I can now count. What I doing
wrong. I hope I am explaining clearly.
Joy

Since you have used the built-in Users Group, your database isn't secure.
That group is common to all mdw files, so anyone can open your database
using any workgroup file. The Admins Group is fine, but you need to
create your own group for the users.

Then since your users will be logging in, you can identify them using the
CurrentUser() function. They don't need to select their name from a
combobox.

You can set the recordsource of your form when it opens depending on the
group the current user is in. There is a function in the security FAQ
you
can use to determine if the current user is in the Admins Group.
http://support.microsoft.com/?id=207793

If faq_IsUserInGroup("Admins", CurrentUser) then
Me.RecordSource = "SELECT * FROM YourTable;"
Else
Me.RecordSource = "SELECT * FROM YourTable WHERE WhoOwns = " & chr(34)
& CurrentUser() & chr(34)
End If

This assumes that you add a field called WhoOwns in YourTable to identify
the employee for the record.

 

[Joy]

Thanks to both for the replies. Joan, this worked. stLinkCriteria =
"[WhoOwns]=" & chr(34) & Me!WhoOwns & chr(34)

I know I don''t need the text box for the CurrentUser but I didn't know else
how to include the date criteria when the form opens. I based the form on a
query that has >=Forms!DateRange!BeginDate etc in the criteria row.

I like the code you gave me best: (listed directly below, but could not add
the additional code correctly to prompt for a date. I'm not very good with
strings. Is there a book I can buy that deals with this?

You really don't need a textbox on your unbound form for the CurrentUser.

But if you want to include it, be sure to set the locked property to no
(or the user could just change it).

Change
stLinkCriteria = "[WhoOwns]=" & Me!WhoOwns
to
stLinkCriteria = "[WhoOwns]=" & chr(34) & Me!WhoOwns & chr(34)


--
Joan Wild
Microsoft Access MVP

I posted this last week and received this very good advice but it isn't
working the way I need it to work. It works great if you
want all records, but the form must open records between a begin and end
work date. The form must return records from two tables in a one to many
relationship. One table (the parent)
stores the employee that entered the hours and WhoOwns the record, and
the second table (the child) stores the dates worked, hours etc. An
unbound form would be used to type in
the begin and end dates. I've been working on this for days. Can't
believe it's that complicated.

I created an unbound form with date criteria fields and a field called
WhoOwns with a control source of =CurrentUser.

Then tried to set the criteria to open the form (the one with the
criteria records) with a criteria string stating the following:
stLinkCriteria = "[WhoOwns]=" & Me!WhoOwns
DoCmd.OpenForm stDocName, , , stLinkCriteria
(Above - Me!WhoOwns being the control on the unbound criteria form)

The query the form is bound to has the following in the criteria field of
WorkDate: >=Forms!dlgDateRange!BeginDate AND <=Forms!dlgDateRange!EndDate

I get an error message shen I click the open form button stating "Syntax
error (missing operator) in query expression "WhoOwns = name of
CurrentUser"
The current user is the name of the CurrentUser and the criteria string
is in the OnClick event of the unbound criteria form.

I've tried to do this more times than I can now count. What I doing
wrong. I hope I am explaining clearly.
Joy

Since you have used the built-in Users Group, your database isn't
secure.
That group is common to all mdw files, so anyone can open your database
using any workgroup file. The Admins Group is fine, but you need to
create your own group for the users.

Then since your users will be logging in, you can identify them using
the
CurrentUser() function. They don't need to select their name from a
combobox.

You can set the recordsource of your form when it opens depending on the
group the current user is in. There is a function in the security FAQ
you
can use to determine if the current user is in the Admins Group.
http://support.microsoft.com/?id=207793

If faq_IsUserInGroup("Admins", CurrentUser) then
Me.RecordSource = "SELECT * FROM YourTable;"
Else
Me.RecordSource = "SELECT * FROM YourTable WHERE WhoOwns = " &
chr(34)
& CurrentUser() & chr(34)
End If

This assumes that you add a field called WhoOwns in YourTable to
identify
the employee for the record.

If multiple users use the same form to edit hours entered in a time and
billing database, is it possible to prevent a user from viewing other
users/employees hours worked. What I'm trying to do is identify if the
current user selecting the employee name is selecting his or her own
name. Then if not, and the user is not in the
Admins group, the form won't open and they can't see what other
employees
have done. Only a user in the Admins group would be permitted to view
all records.

The database is created in Access 2003 and is secured. It has group
level security (2 groups - Admins and Users) and each user has a copy
of
the front-end on their pc. The users/employees select their name from
a
drop down box on a form, then the hours form opens listing projects and
hours logged for the employee selected.

Any help is appreciated.
Thanks.

 

[Joan Wild]

Hi Joy,

Actually I think you are mixing up separate forms. As I understand it you
have an unbound form to gather two dates from the user.

You then have a command button to open a form. This form is actually a main
form (Employees) with a subform on it (Hours Worked) The criteria gathered
on the unbound form is only used in the Hours Worked subform. That subform
is based on a query with
WorkDate: >=Forms!dlgDateRange!BeginDate AND <=Forms!dlgDateRange!EndDate

Your command button just needs to open the Employees form. The subform will
take care of itself as long as your dlgDateRange form is open.

In the Open Event for your Employees form put
If faq_IsUserInGroup("Admins", CurrentUser) then
Me.RecordSource = "SELECT * FROM YourTable;"
Else
Me.RecordSource = "SELECT * FROM YourTable WHERE WhoOwns = " & chr(34) &
CurrentUser() & chr(34)
End If

(Watch for newsreader wrap in that)

Assuming that your Hours Worked subform control has the Link Master/Link
Child properties set correctly, everything should work fine. You don't need
to have a textbox on your dlgDateRange form for the CurrentUser.


--
Joan Wild
Microsoft Access MVP

Thanks to both for the replies. Joan, this worked. stLinkCriteria =
"[WhoOwns]=" & chr(34) & Me!WhoOwns & chr(34)

I know I don''t need the text box for the CurrentUser but I didn't know
else how to include the date criteria when the form opens. I based the
form on a query that has >=Forms!DateRange!BeginDate etc in the criteria
row.

I like the code you gave me best: (listed directly below, but could not
add the additional code correctly to prompt for a date. I'm not very good
with strings. Is there a book I can buy that deals with this?

 

[Joy]

Thank you. I will try again. I thought I tried this method but I'm sure I
missed something important.

Hi Joy,

Actually I think you are mixing up separate forms. As I understand it you
have an unbound form to gather two dates from the user.

You then have a command button to open a form. This form is actually a
main form (Employees) with a subform on it (Hours Worked) The criteria
gathered on the unbound form is only used in the Hours Worked subform.
That subform is based on a query with
WorkDate: >=Forms!dlgDateRange!BeginDate AND <=Forms!dlgDateRange!EndDate

Your command button just needs to open the Employees form. The subform
will take care of itself as long as your dlgDateRange form is open.

In the Open Event for your Employees form put
If faq_IsUserInGroup("Admins", CurrentUser) then
Me.RecordSource = "SELECT * FROM YourTable;"
Else
Me.RecordSource = "SELECT * FROM YourTable WHERE WhoOwns = " & chr(34)
& CurrentUser() & chr(34)
End If

(Watch for newsreader wrap in that)

Assuming that your Hours Worked subform control has the Link Master/Link
Child properties set correctly, everything should work fine. You don't
need to have a textbox on your dlgDateRange form for the CurrentUser.


--
Joan Wild
Microsoft Access MVP

Thanks to both for the replies. Joan, this worked. stLinkCriteria =
"[WhoOwns]=" & chr(34) & Me!WhoOwns & chr(34)

I know I don''t need the text box for the CurrentUser but I didn't know
else how to include the date criteria when the form opens. I based the
form on a query that has >=Forms!DateRange!BeginDate etc in the criteria
row.

I like the code you gave me best: (listed directly below, but could not
add the additional code correctly to prompt for a date. I'm not very
good with strings. Is there a book I can buy that deals with this?

If faq_IsUserInGroup("Admins", CurrentUser) then
Me.RecordSource = "SELECT * FROM YourTable;"
Else
Me.RecordSource = "SELECT * FROM YourTable WHERE WhoOwns = " &
chr(34)
& CurrentUser() & chr(34)
End If

 

[Joy]

It worked! It worked!
Thanks.
Joy

Thank you. I will try again. I thought I tried this method but I'm sure
I missed something important.

Hi Joy,

Actually I think you are mixing up separate forms. As I understand it
you have an unbound form to gather two dates from the user.

You then have a command button to open a form. This form is actually a
main form (Employees) with a subform on it (Hours Worked) The criteria
gathered on the unbound form is only used in the Hours Worked subform.
That subform is based on a query with
WorkDate: >=Forms!dlgDateRange!BeginDate AND <=Forms!dlgDateRange!EndDate

Your command button just needs to open the Employees form. The subform
will take care of itself as long as your dlgDateRange form is open.

In the Open Event for your Employees form put
If faq_IsUserInGroup("Admins", CurrentUser) then
Me.RecordSource = "SELECT * FROM YourTable;"
Else
Me.RecordSource = "SELECT * FROM YourTable WHERE WhoOwns = " & chr(34)
& CurrentUser() & chr(34)
End If

(Watch for newsreader wrap in that)

Assuming that your Hours Worked subform control has the Link Master/Link
Child properties set correctly, everything should work fine. You don't
need to have a textbox on your dlgDateRange form for the CurrentUser.


--
Joan Wild
Microsoft Access MVP

Thanks to both for the replies. Joan, this worked. stLinkCriteria =
"[WhoOwns]=" & chr(34) & Me!WhoOwns & chr(34)

I know I don''t need the text box for the CurrentUser but I didn't know
else how to include the date criteria when the form opens. I based the
form on a query that has >=Forms!DateRange!BeginDate etc in the criteria
row.

I like the code you gave me best: (listed directly below, but could not
add the additional code correctly to prompt for a date. I'm not very
good with strings. Is there a book I can buy that deals with this?

If faq_IsUserInGroup("Admins", CurrentUser) then
Me.RecordSource = "SELECT * FROM YourTable;"
Else
Me.RecordSource = "SELECT * FROM YourTable WHERE WhoOwns = " &
chr(34)
& CurrentUser() & chr(34)
End If