"Joan Wild" wrote:

> brem219 wrote:
> > I created a database with user level security for a group of five
> > supervisors, and their manager.  The database is configured so each
> > supervisor is directed to their own form.  The CurrentUser fucntion is
> > employed so each supervisor has the ability to look at their work
> > only.
>
> You can use a single form to accomplish this - no need for a separate form
> for each supervisor.
>
> > However, the manager's custom form has a combo box so he can
> > look at his work and the work of his subordinates.  It is not working
> > right now because as the queries are designed for CurrentUser, the
> > manager can only look at his own work.  I already added the manager
> > to the Admins group.  Can I give the manager the ability to look at
> > other's work without creating new queries and macros?  Thank you.
>
> You can use one form and in it's open event set the recordsource property
> for the form:
> There's code in the security FAQ you can use to determine if the current
> user is a member of a group.  Put that function in a module (give the module
> a differnent name than the function).
> Then use it like...
>
> If faqIsUserInGroup("Admins", CurrentUser()) then
> Me.RecordSource = "SELECT * FROM SomeTable"
> else
> Me.RecordSource = "SELECT * FROM SomeTable WHERE SomeField = " & chr(34) &
> CurrentUser() & chr(34)
> end if
>
> Watch for newsreader wrap in the above
> --
> Joan Wild
> Microsoft Access MVP
>
>
>

>
>
> "Joan Wild" wrote:
>
> > brem219 wrote:
> > > I created a database with user level security for a group of five
> > > supervisors, and their manager.  The database is configured so each
> > > supervisor is directed to their own form.  The CurrentUser fucntion is
> > > employed so each supervisor has the ability to look at their work
> > > only.
> >
> > You can use a single form to accomplish this - no need for a separate form
> > for each supervisor.
> >
> > > However, the manager's custom form has a combo box so he can
> > > look at his work and the work of his subordinates.  It is not working
> > > right now because as the queries are designed for CurrentUser, the
> > > manager can only look at his own work.  I already added the manager
> > > to the Admins group.  Can I give the manager the ability to look at
> > > other's work without creating new queries and macros?  Thank you.
> >
> > You can use one form and in it's open event set the recordsource property
> > for the form:
> > There's code in the security FAQ you can use to determine if the current
> > user is a member of a group.  Put that function in a module (give the module
> > a differnent name than the function).
> > Then use it like...
> >
> > If faqIsUserInGroup("Admins", CurrentUser()) then
> > Me.RecordSource = "SELECT * FROM SomeTable"
> > else
> > Me.RecordSource = "SELECT * FROM SomeTable WHERE SomeField = " & chr(34) &
> > CurrentUser() & chr(34)
> > end if
> >
> > Watch for newsreader wrap in the above
> > --
> > Joan Wild
> > Microsoft Access MVP
> >
> >
> >

> I am new to code, functions, and modules and don't know much about
> how to use them.  I entered the following SQL code that I found in
> the Security FAQ under the record source on the main form in a module
> I called PullUser: Function faq_IsUserInGroup (strGroup As String,
> strUser as String) As Integer 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(strUsers).Name
> faq_IsUserInGroup = (Err = 0)
> End Function
>
> Next I tried to place this SQL code in the record source of the Main
> form:
>
> If faqIsUserInGroup("Admins",CurrentUser()) then
> Me.RecordSource = "SELECT * FROM Main Table"
> Else
> Me.RecordSource = "SELECT * FROM SomeTable WHERE [Username] = "&
> CurrentUser() End If
>
> It kept rejecting the code, saying the SQL statement was invalid.
> Where should this be placed?  Where does the module come into play?
> I hope you can help me with this.
>
>
>
>
>
>>
>>
>> "Joan Wild" wrote:
>>
>>> brem219 wrote:
>>>> I created a database with user level security for a group of five
>>>> supervisors, and their manager.  The database is configured so each
>>>> supervisor is directed to their own form.  The CurrentUser
>>>> fucntion is employed so each supervisor has the ability to look at
>>>> their work only.
>>>
>>> You can use a single form to accomplish this - no need for a
>>> separate form for each supervisor.
>>>
>>>> However, the manager's custom form has a combo box so he can
>>>> look at his work and the work of his subordinates.  It is not
>>>> working right now because as the queries are designed for
>>>> CurrentUser, the manager can only look at his own work.  I already
>>>> added the manager to the Admins group.  Can I give the manager the
>>>> ability to look at other's work without creating new queries and
>>>> macros?  Thank you.
>>>
>>> You can use one form and in it's open event set the recordsource
>>> property for the form:
>>> There's code in the security FAQ you can use to determine if the
>>> current user is a member of a group.  Put that function in a module
>>> (give the module a differnent name than the function).
>>> Then use it like...
>>>
>>> If faqIsUserInGroup("Admins", CurrentUser()) then
>>> Me.RecordSource = "SELECT * FROM SomeTable"
>>> else
>>> Me.RecordSource = "SELECT * FROM SomeTable WHERE SomeField = " &
>>> chr(34) & CurrentUser() & chr(34)
>>> end if
>>>
>>> Watch for newsreader wrap in the above
>>> --
>>> Joan Wild
>>> Microsoft Access MVP

"Joan Wild" wrote:

> Undo that.
>
> Go to the Modules tab, and create a new Module, paste the Function there.
> Save and close the module and call it
> basUtilities.
>
> Open your form in design view.  Go to the On Open event property and select
> [Event Procedure] in the drop down for that property.  Then click on the
> build button (...) to the right.  This will open a window with
> Private Sub Form_Open(Cancel As Integer)
>
> End Sub
>
> Between those two lines paste the If statement.
>
> Go to the debug menu, and choose compile.  Then close the window and save
> your form.
>
> --
> Joan Wild
> Microsoft Access MVP
>
> brem219 wrote:
> > I am new to code, functions, and modules and don't know much about
> > how to use them.  I entered the following SQL code that I found in
> > the Security FAQ under the record source on the main form in a module
> > I called PullUser: Function faq_IsUserInGroup (strGroup As String,
> > strUser as String) As Integer 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(strUsers).Name
> > faq_IsUserInGroup = (Err = 0)
> > End Function
> >
> > Next I tried to place this SQL code in the record source of the Main
> > form:
> >
> > If faqIsUserInGroup("Admins",CurrentUser()) then
> > Me.RecordSource = "SELECT * FROM Main Table"
> > Else
> > Me.RecordSource = "SELECT * FROM SomeTable WHERE [Username] = "&
> > CurrentUser() End If
> >
> > It kept rejecting the code, saying the SQL statement was invalid.
> > Where should this be placed?  Where does the module come into play?
> > I hope you can help me with this.
> >
> >
> >
> >
> >
> >>
> >>
> >> "Joan Wild" wrote:
> >>
> >>> brem219 wrote:
> >>>> I created a database with user level security for a group of five
> >>>> supervisors, and their manager.  The database is configured so each
> >>>> supervisor is directed to their own form.  The CurrentUser
> >>>> fucntion is employed so each supervisor has the ability to look at
> >>>> their work only.
> >>>
> >>> You can use a single form to accomplish this - no need for a
> >>> separate form for each supervisor.
> >>>
> >>>> However, the manager's custom form has a combo box so he can
> >>>> look at his work and the work of his subordinates.  It is not
> >>>> working right now because as the queries are designed for
> >>>> CurrentUser, the manager can only look at his own work.  I already
> >>>> added the manager to the Admins group.  Can I give the manager the
> >>>> ability to look at other's work without creating new queries and
> >>>> macros?  Thank you.
> >>>
> >>> You can use one form and in it's open event set the recordsource
> >>> property for the form:
> >>> There's code in the security FAQ you can use to determine if the
> >>> current user is a member of a group.  Put that function in a module
> >>> (give the module a differnent name than the function).
> >>> Then use it like...
> >>>
> >>> If faqIsUserInGroup("Admins", CurrentUser()) then
> >>> Me.RecordSource = "SELECT * FROM SomeTable"
> >>> else
> >>> Me.RecordSource = "SELECT * FROM SomeTable WHERE SomeField = " &
> >>> chr(34) & CurrentUser() & chr(34)
> >>> end if
> >>>
> >>> Watch for newsreader wrap in the above
> >>> --
> >>> Joan Wild
> >>> Microsoft Access MVP
>
>
>

> Thank you for your reply.  I did as you asked, and I am getting the
> following error:
>
> Run-time error '3131':
> Syntax error in FROM clause.
>
> I open up the code and the following statement is highlighted:
>
> Me.RecordSource = "SELECT * FROM Main Table WHERE [Username]= " &
> CurrentUser()
>
> The name of the table is Main Table.  Username is the field where the
> CurrentUser is identified.
>
> "Joan Wild" wrote:
>
>> Undo that.
>>
>> Go to the Modules tab, and create a new Module, paste the Function
>> there. Save and close the module and call it
>> basUtilities.
>>
>> Open your form in design view.  Go to the On Open event property and
>> select [Event Procedure] in the drop down for that property.  Then
>> click on the build button (...) to the right.  This will open a
>> window with
>> Private Sub Form_Open(Cancel As Integer)
>>
>> End Sub
>>
>> Between those two lines paste the If statement.
>>
>> Go to the debug menu, and choose compile.  Then close the window and
>> save your form.
>>
>> --
>> Joan Wild
>> Microsoft Access MVP
>>
>> brem219 wrote:
>>> I am new to code, functions, and modules and don't know much about
>>> how to use them.  I entered the following SQL code that I found in
>>> the Security FAQ under the record source on the main form in a
>>> module I called PullUser: Function faq_IsUserInGroup (strGroup As
>>> String, strUser as String) As Integer 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(strUsers).Name
>>> faq_IsUserInGroup = (Err = 0)
>>> End Function
>>>
>>> Next I tried to place this SQL code in the record source of the Main
>>> form:
>>>
>>> If faqIsUserInGroup("Admins",CurrentUser()) then
>>> Me.RecordSource = "SELECT * FROM Main Table"
>>> Else
>>> Me.RecordSource = "SELECT * FROM SomeTable WHERE [Username] = "&
>>> CurrentUser() End If
>>>
>>> It kept rejecting the code, saying the SQL statement was invalid.
>>> Where should this be placed?  Where does the module come into play?
>>> I hope you can help me with this.
>>>
>>>
>>>
>>>
>>>
>>>>
>>>>
>>>> "Joan Wild" wrote:
>>>>
>>>>> brem219 wrote:
>>>>>> I created a database with user level security for a group of five
>>>>>> supervisors, and their manager.  The database is configured so
>>>>>> each supervisor is directed to their own form.  The CurrentUser
>>>>>> fucntion is employed so each supervisor has the ability to look
>>>>>> at their work only.
>>>>>
>>>>> You can use a single form to accomplish this - no need for a
>>>>> separate form for each supervisor.
>>>>>
>>>>>> However, the manager's custom form has a combo box so he can
>>>>>> look at his work and the work of his subordinates.  It is not
>>>>>> working right now because as the queries are designed for
>>>>>> CurrentUser, the manager can only look at his own work.  I
>>>>>> already added the manager to the Admins group.  Can I give the
>>>>>> manager the ability to look at other's work without creating new
>>>>>> queries and macros?  Thank you.
>>>>>
>>>>> You can use one form and in it's open event set the recordsource
>>>>> property for the form:
>>>>> There's code in the security FAQ you can use to determine if the
>>>>> current user is a member of a group.  Put that function in a
>>>>> module (give the module a differnent name than the function).
>>>>> Then use it like...
>>>>>
>>>>> If faqIsUserInGroup("Admins", CurrentUser()) then
>>>>> Me.RecordSource = "SELECT * FROM SomeTable"
>>>>> else
>>>>> Me.RecordSource = "SELECT * FROM SomeTable WHERE SomeField = " &
>>>>> chr(34) & CurrentUser() & chr(34)
>>>>> end if
>>>>>
>>>>> Watch for newsreader wrap in the above
>>>>> --
>>>>> Joan Wild
>>>>> Microsoft Access MVP

"Joan Wild" wrote:

> Enclose Main Table is square brackets:
>
> ....FROM [Main Table] WHERE...
>
> Also I believe you need to change it to
> WHERE [Username] = " & chr(34) & CurrentUser() & chr(34)
>
> to put quotes around the string that CurrentUser() returns.
>
> I also assume that it's all on one line, and it just appears to be two due
> to newsreader wrap?
>
> --
> Joan Wild
> Microsoft Access MVP
>
> brem219 wrote:
> > Thank you for your reply.  I did as you asked, and I am getting the
> > following error:
> >
> > Run-time error '3131':
> > Syntax error in FROM clause.
> >
> > I open up the code and the following statement is highlighted:
> >
> > Me.RecordSource = "SELECT * FROM Main Table WHERE [Username]= " &
> > CurrentUser()
> >
> > The name of the table is Main Table.  Username is the field where the
> > CurrentUser is identified.
> >
> > "Joan Wild" wrote:
> >
> >> Undo that.
> >>
> >> Go to the Modules tab, and create a new Module, paste the Function
> >> there. Save and close the module and call it
> >> basUtilities.
> >>
> >> Open your form in design view.  Go to the On Open event property and
> >> select [Event Procedure] in the drop down for that property.  Then
> >> click on the build button (...) to the right.  This will open a
> >> window with
> >> Private Sub Form_Open(Cancel As Integer)
> >>
> >> End Sub
> >>
> >> Between those two lines paste the If statement.
> >>
> >> Go to the debug menu, and choose compile.  Then close the window and
> >> save your form.
> >>
> >> --
> >> Joan Wild
> >> Microsoft Access MVP
> >>
> >> brem219 wrote:
> >>> I am new to code, functions, and modules and don't know much about
> >>> how to use them.  I entered the following SQL code that I found in
> >>> the Security FAQ under the record source on the main form in a
> >>> module I called PullUser: Function faq_IsUserInGroup (strGroup As
> >>> String, strUser as String) As Integer 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(strUsers).Name
> >>> faq_IsUserInGroup = (Err = 0)
> >>> End Function
> >>>
> >>> Next I tried to place this SQL code in the record source of the Main
> >>> form:
> >>>
> >>> If faqIsUserInGroup("Admins",CurrentUser()) then
> >>> Me.RecordSource = "SELECT * FROM Main Table"
> >>> Else
> >>> Me.RecordSource = "SELECT * FROM SomeTable WHERE [Username] = "&
> >>> CurrentUser() End If
> >>>
> >>> It kept rejecting the code, saying the SQL statement was invalid.
> >>> Where should this be placed?  Where does the module come into play?
> >>> I hope you can help me with this.
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>>
> >>>>
> >>>> "Joan Wild" wrote:
> >>>>
> >>>>> brem219 wrote:
> >>>>>> I created a database with user level security for a group of five
> >>>>>> supervisors, and their manager.  The database is configured so
> >>>>>> each supervisor is directed to their own form.  The CurrentUser
> >>>>>> fucntion is employed so each supervisor has the ability to look
> >>>>>> at their work only.
> >>>>>
> >>>>> You can use a single form to accomplish this - no need for a
> >>>>> separate form for each supervisor.
> >>>>>
> >>>>>> However, the manager's custom form has a combo box so he can
> >>>>>> look at his work and the work of his subordinates.  It is not
> >>>>>> working right now because as the queries are designed for
> >>>>>> CurrentUser, the manager can only look at his own work.  I
> >>>>>> already added the manager to the Admins group.  Can I give the
> >>>>>> manager the ability to look at other's work without creating new
> >>>>>> queries and macros?  Thank you.
> >>>>>
> >>>>> You can use one form and in it's open event set the recordsource
> >>>>> property for the form:
> >>>>> There's code in the security FAQ you can use to determine if the
> >>>>> current user is a member of a group.  Put that function in a
> >>>>> module (give the module a differnent name than the function).
> >>>>> Then use it like...
> >>>>>
> >>>>> If faqIsUserInGroup("Admins", CurrentUser()) then
> >>>>> Me.RecordSource = "SELECT * FROM SomeTable"
> >>>>> else
> >>>>> Me.RecordSource = "SELECT * FROM SomeTable WHERE SomeField = " &
> >>>>> chr(34) & CurrentUser() & chr(34)
> >>>>> end if
> >>>>>
> >>>>> Watch for newsreader wrap in the above
> >>>>> --
> >>>>> Joan Wild
> >>>>> Microsoft Access MVP
>
>
>

> Okay, now the line of code looks like this, and it's all on one line:
>
> Me.RecordSource = "SELECT * FROM [Main Table] WHERE [Username]= " &
> chr(34) CurrentUser() & chr(34)
>
> However, now I am getting an error that highlights CurrentUser and
> reads "Expected: end of statement"  Do I need a semicolon or comma
> anywhere? Thanks again for your help.
>
> "Joan Wild" wrote:
>
>> Enclose Main Table is square brackets:
>>
>> ....FROM [Main Table] WHERE...
>>
>> Also I believe you need to change it to
>> WHERE [Username] = " & chr(34) & CurrentUser() & chr(34)
>>
>> to put quotes around the string that CurrentUser() returns.
>>
>> I also assume that it's all on one line, and it just appears to be
>> two due to newsreader wrap?
>>
>> --
>> Joan Wild
>> Microsoft Access MVP
>>
>> brem219 wrote:
>>> Thank you for your reply.  I did as you asked, and I am getting the
>>> following error:
>>>
>>> Run-time error '3131':
>>> Syntax error in FROM clause.
>>>
>>> I open up the code and the following statement is highlighted:
>>>
>>> Me.RecordSource = "SELECT * FROM Main Table WHERE [Username]= " &
>>> CurrentUser()
>>>
>>> The name of the table is Main Table.  Username is the field where
>>> the CurrentUser is identified.
>>>
>>> "Joan Wild" wrote:
>>>
>>>> Undo that.
>>>>
>>>> Go to the Modules tab, and create a new Module, paste the Function
>>>> there. Save and close the module and call it
>>>> basUtilities.
>>>>
>>>> Open your form in design view.  Go to the On Open event property
>>>> and select [Event Procedure] in the drop down for that property.
>>>> Then click on the build button (...) to the right.  This will open
>>>> a window with
>>>> Private Sub Form_Open(Cancel As Integer)
>>>>
>>>> End Sub
>>>>
>>>> Between those two lines paste the If statement.
>>>>
>>>> Go to the debug menu, and choose compile.  Then close the window
>>>> and save your form.
>>>>
>>>> --
>>>> Joan Wild
>>>> Microsoft Access MVP
>>>>
>>>> brem219 wrote:
>>>>> I am new to code, functions, and modules and don't know much about
>>>>> how to use them.  I entered the following SQL code that I found in
>>>>> the Security FAQ under the record source on the main form in a
>>>>> module I called PullUser: Function faq_IsUserInGroup (strGroup As
>>>>> String, strUser as String) As Integer 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(strUsers).Name
>>>>> faq_IsUserInGroup = (Err = 0)
>>>>> End Function
>>>>>
>>>>> Next I tried to place this SQL code in the record source of the
>>>>> Main form:
>>>>>
>>>>> If faqIsUserInGroup("Admins",CurrentUser()) then
>>>>> Me.RecordSource = "SELECT * FROM Main Table"
>>>>> Else
>>>>> Me.RecordSource = "SELECT * FROM SomeTable WHERE [Username] = "&
>>>>> CurrentUser() End If
>>>>>
>>>>> It kept rejecting the code, saying the SQL statement was invalid.
>>>>> Where should this be placed?  Where does the module come into
>>>>> play? I hope you can help me with this.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>
>>>>>>
>>>>>> "Joan Wild" wrote:
>>>>>>
>>>>>>> brem219 wrote:
>>>>>>>> I created a database with user level security for a group of
>>>>>>>> five supervisors, and their manager.  The database is
>>>>>>>> configured so each supervisor is directed to their own form.
>>>>>>>> The CurrentUser fucntion is employed so each supervisor has
>>>>>>>> the ability to look at their work only.
>>>>>>>
>>>>>>> You can use a single form to accomplish this - no need for a
>>>>>>> separate form for each supervisor.
>>>>>>>
>>>>>>>> However, the manager's custom form has a combo box so he can
>>>>>>>> look at his work and the work of his subordinates.  It is not
>>>>>>>> working right now because as the queries are designed for
>>>>>>>> CurrentUser, the manager can only look at his own work.  I
>>>>>>>> already added the manager to the Admins group.  Can I give the
>>>>>>>> manager the ability to look at other's work without creating
>>>>>>>> new queries and macros?  Thank you.
>>>>>>>
>>>>>>> You can use one form and in it's open event set the recordsource
>>>>>>> property for the form:
>>>>>>> There's code in the security FAQ you can use to determine if the
>>>>>>> current user is a member of a group.  Put that function in a
>>>>>>> module (give the module a differnent name than the function).
>>>>>>> Then use it like...
>>>>>>>
>>>>>>> If faqIsUserInGroup("Admins", CurrentUser()) then
>>>>>>> Me.RecordSource = "SELECT * FROM SomeTable"
>>>>>>> else
>>>>>>> Me.RecordSource = "SELECT * FROM SomeTable WHERE SomeField = " &
>>>>>>> chr(34) & CurrentUser() & chr(34)
>>>>>>> end if
>>>>>>>
>>>>>>> Watch for newsreader wrap in the above
>>>>>>> --
>>>>>>> Joan Wild
>>>>>>> Microsoft Access MVP

"Joan Wild" wrote:

> Enclose Main Table is square brackets:
>
> ....FROM [Main Table] WHERE...
>
> Also I believe you need to change it to
> WHERE [Username] = " & chr(34) & CurrentUser() & chr(34)
>
> to put quotes around the string that CurrentUser() returns.
>
> I also assume that it's all on one line, and it just appears to be two due
> to newsreader wrap?
>
> --
> Joan Wild
> Microsoft Access MVP
>
> brem219 wrote:
> > Thank you for your reply.  I did as you asked, and I am getting the
> > following error:
> >
> > Run-time error '3131':
> > Syntax error in FROM clause.
> >
> > I open up the code and the following statement is highlighted:
> >
> > Me.RecordSource = "SELECT * FROM Main Table WHERE [Username]= " &
> > CurrentUser()
> >
> > The name of the table is Main Table.  Username is the field where the
> > CurrentUser is identified.
> >
> > "Joan Wild" wrote:
> >
> >> Undo that.
> >>
> >> Go to the Modules tab, and create a new Module, paste the Function
> >> there. Save and close the module and call it
> >> basUtilities.
> >>
> >> Open your form in design view.  Go to the On Open event property and
> >> select [Event Procedure] in the drop down for that property.  Then
> >> click on the build button (...) to the right.  This will open a
> >> window with
> >> Private Sub Form_Open(Cancel As Integer)
> >>
> >> End Sub
> >>
> >> Between those two lines paste the If statement.
> >>
> >> Go to the debug menu, and choose compile.  Then close the window and
> >> save your form.
> >>
> >> --
> >> Joan Wild
> >> Microsoft Access MVP
> >>
> >> brem219 wrote:
> >>> I am new to code, functions, and modules and don't know much about
> >>> how to use them.  I entered the following SQL code that I found in
> >>> the Security FAQ under the record source on the main form in a
> >>> module I called PullUser: Function faq_IsUserInGroup (strGroup As
> >>> String, strUser as String) As Integer 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(strUsers).Name
> >>> faq_IsUserInGroup = (Err = 0)
> >>> End Function
> >>>
> >>> Next I tried to place this SQL code in the record source of the Main
> >>> form:
> >>>
> >>> If faqIsUserInGroup("Admins",CurrentUser()) then
> >>> Me.RecordSource = "SELECT * FROM Main Table"
> >>> Else
> >>> Me.RecordSource = "SELECT * FROM SomeTable WHERE [Username] = "&
> >>> CurrentUser() End If
> >>>
> >>> It kept rejecting the code, saying the SQL statement was invalid.
> >>> Where should this be placed?  Where does the module come into play?
> >>> I hope you can help me with this.
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>>
> >>>>
> >>>> "Joan Wild" wrote:
> >>>>
> >>>>> brem219 wrote:
> >>>>>> I created a database with user level security for a group of five
> >>>>>> supervisors, and their manager.  The database is configured so
> >>>>>> each supervisor is directed to their own form.  The CurrentUser
> >>>>>> fucntion is employed so each supervisor has the ability to look
> >>>>>> at their work only.
> >>>>>
> >>>>> You can use a single form to accomplish this - no need for a
> >>>>> separate form for each supervisor.
> >>>>>
> >>>>>> However, the manager's custom form has a combo box so he can
> >>>>>> look at his work and the work of his subordinates.  It is not
> >>>>>> working right now because as the queries are designed for
> >>>>>> CurrentUser, the manager can only look at his own work.  I
> >>>>>> already added the manager to the Admins group.  Can I give the
> >>>>>> manager the ability to look at other's work without creating new
> >>>>>> queries and macros?  Thank you.
> >>>>>
> >>>>> You can use one form and in it's open event set the recordsource
> >>>>> property for the form:
> >>>>> There's code in the security FAQ you can use to determine if the
> >>>>> current user is a member of a group.  Put that function in a
> >>>>> module (give the module a differnent name than the function).
> >>>>> Then use it like...
> >>>>>
> >>>>> If faqIsUserInGroup("Admins", CurrentUser()) then
> >>>>> Me.RecordSource = "SELECT * FROM SomeTable"
> >>>>> else
> >>>>> Me.RecordSource = "SELECT * FROM SomeTable WHERE SomeField = " &
> >>>>> chr(34) & CurrentUser() & chr(34)
> >>>>> end if
> >>>>>
> >>>>> Watch for newsreader wrap in the above
> >>>>> --
> >>>>> Joan Wild
> >>>>> Microsoft Access MVP
>
>
>

"Joan Wild" wrote:

> Enclose Main Table is square brackets:
>
> ....FROM [Main Table] WHERE...
>
> Also I believe you need to change it to
> WHERE [Username] = " & chr(34) & CurrentUser() & chr(34)
>
> to put quotes around the string that CurrentUser() returns.
>
> I also assume that it's all on one line, and it just appears to be two due
> to newsreader wrap?
>
> --
> Joan Wild
> Microsoft Access MVP
>
> brem219 wrote:
> > Thank you for your reply.  I did as you asked, and I am getting the
> > following error:
> >
> > Run-time error '3131':
> > Syntax error in FROM clause.
> >
> > I open up the code and the following statement is highlighted:
> >
> > Me.RecordSource = "SELECT * FROM Main Table WHERE [Username]= " &
> > CurrentUser()
> >
> > The name of the table is Main Table.  Username is the field where the
> > CurrentUser is identified.
> >
> > "Joan Wild" wrote:
> >
> >> Undo that.
> >>
> >> Go to the Modules tab, and create a new Module, paste the Function
> >> there. Save and close the module and call it
> >> basUtilities.
> >>
> >> Open your form in design view.  Go to the On Open event property and
> >> select [Event Procedure] in the drop down for that property.  Then
> >> click on the build button (...) to the right.  This will open a
> >> window with
> >> Private Sub Form_Open(Cancel As Integer)
> >>
> >> End Sub
> >>
> >> Between those two lines paste the If statement.
> >>
> >> Go to the debug menu, and choose compile.  Then close the window and
> >> save your form.
> >>
> >> --
> >> Joan Wild
> >> Microsoft Access MVP
> >>
> >> brem219 wrote:
> >>> I am new to code, functions, and modules and don't know much about
> >>> how to use them.  I entered the following SQL code that I found in
> >>> the Security FAQ under the record source on the main form in a
> >>> module I called PullUser: Function faq_IsUserInGroup (strGroup As
> >>> String, strUser as String) As Integer 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(strUsers).Name
> >>> faq_IsUserInGroup = (Err = 0)
> >>> End Function
> >>>
> >>> Next I tried to place this SQL code in the record source of the Main
> >>> form:
> >>>
> >>> If faqIsUserInGroup("Admins",CurrentUser()) then
> >>> Me.RecordSource = "SELECT * FROM Main Table"
> >>> Else
> >>> Me.RecordSource = "SELECT * FROM SomeTable WHERE [Username] = "&
> >>> CurrentUser() End If
> >>>
> >>> It kept rejecting the code, saying the SQL statement was invalid.
> >>> Where should this be placed?  Where does the module come into play?
> >>> I hope you can help me with this.
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>>
> >>>>
> >>>> "Joan Wild" wrote:
> >>>>
> >>>>> brem219 wrote:
> >>>>>> I created a database with user level security for a group of five
> >>>>>> supervisors, and their manager.  The database is configured so
> >>>>>> each supervisor is directed to their own form.  The CurrentUser
> >>>>>> fucntion is employed so each supervisor has the ability to look
> >>>>>> at their work only.
> >>>>>
> >>>>> You can use a single form to accomplish this - no need for a
> >>>>> separate form for each supervisor.
> >>>>>
> >>>>>> However, the manager's custom form has a combo box so he can
> >>>>>> look at his work and the work of his subordinates.  It is not
> >>>>>> working right now because as the queries are designed for
> >>>>>> CurrentUser, the manager can only look at his own work.  I
> >>>>>> already added the manager to the Admins group.  Can I give the
> >>>>>> manager the ability to look at other's work without creating new
> >>>>>> queries and macros?  Thank you.
> >>>>>
> >>>>> You can use one form and in it's open event set the recordsource
> >>>>> property for the form:
> >>>>> There's code in the security FAQ you can use to determine if the
> >>>>> current user is a member of a group.  Put that function in a
> >>>>> module (give the module a differnent name than the function).
> >>>>> Then use it like...
> >>>>>
> >>>>> If faqIsUserInGroup("Admins", CurrentUser()) then
> >>>>> Me.RecordSource = "SELECT * FROM SomeTable"
> >>>>> else
> >>>>> Me.RecordSource = "SELECT * FROM SomeTable WHERE SomeField = " &
> >>>>> chr(34) & CurrentUser() & chr(34)
> >>>>> end if
> >>>>>
> >>>>> Watch for newsreader wrap in the above
> >>>>> --
> >>>>> Joan Wild
> >>>>> Microsoft Access MVP
>
>
>

"Joan Wild" wrote:

> Enclose Main Table is square brackets:
>
> ....FROM [Main Table] WHERE...
>
> Also I believe you need to change it to
> WHERE [Username] = " & chr(34) & CurrentUser() & chr(34)
>
> to put quotes around the string that CurrentUser() returns.
>
> I also assume that it's all on one line, and it just appears to be two due
> to newsreader wrap?
>
> --
> Joan Wild
> Microsoft Access MVP
>
> brem219 wrote:
> > Thank you for your reply.  I did as you asked, and I am getting the
> > following error:
> >
> > Run-time error '3131':
> > Syntax error in FROM clause.
> >
> > I open up the code and the following statement is highlighted:
> >
> > Me.RecordSource = "SELECT * FROM Main Table WHERE [Username]= " &
> > CurrentUser()
> >
> > The name of the table is Main Table.  Username is the field where the
> > CurrentUser is identified.
> >
> > "Joan Wild" wrote:
> >
> >> Undo that.
> >>
> >> Go to the Modules tab, and create a new Module, paste the Function
> >> there. Save and close the module and call it
> >> basUtilities.
> >>
> >> Open your form in design view.  Go to the On Open event property and
> >> select [Event Procedure] in the drop down for that property.  Then
> >> click on the build button (...) to the right.  This will open a
> >> window with
> >> Private Sub Form_Open(Cancel As Integer)
> >>
> >> End Sub
> >>
> >> Between those two lines paste the If statement.
> >>
> >> Go to the debug menu, and choose compile.  Then close the window and
> >> save your form.
> >>
> >> --
> >> Joan Wild
> >> Microsoft Access MVP
> >>
> >> brem219 wrote:
> >>> I am new to code, functions, and modules and don't know much about
> >>> how to use them.  I entered the following SQL code that I found in
> >>> the Security FAQ under the record source on the main form in a
> >>> module I called PullUser: Function faq_IsUserInGroup (strGroup As
> >>> String, strUser as String) As Integer 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(strUsers).Name
> >>> faq_IsUserInGroup = (Err = 0)
> >>> End Function
> >>>
> >>> Next I tried to place this SQL code in the record source of the Main
> >>> form:
> >>>
> >>> If faqIsUserInGroup("Admins",CurrentUser()) then
> >>> Me.RecordSource = "SELECT * FROM Main Table"
> >>> Else
> >>> Me.RecordSource = "SELECT * FROM SomeTable WHERE [Username] = "&
> >>> CurrentUser() End If
> >>>
> >>> It kept rejecting the code, saying the SQL statement was invalid.
> >>> Where should this be placed?  Where does the module come into play?
> >>> I hope you can help me with this.
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>>
> >>>>
> >>>> "Joan Wild" wrote:
> >>>>
> >>>>> brem219 wrote:
> >>>>>> I created a database with user level security for a group of five
> >>>>>> supervisors, and their manager.  The database is configured so
> >>>>>> each supervisor is directed to their own form.  The CurrentUser
> >>>>>> fucntion is employed so each supervisor has the ability to look
> >>>>>> at their work only.
> >>>>>
> >>>>> You can use a single form to accomplish this - no need for a
> >>>>> separate form for each supervisor.
> >>>>>
> >>>>>> However, the manager's custom form has a combo box so he can
> >>>>>> look at his work and the work of his subordinates.  It is not
> >>>>>> working right now because as the queries are designed for
> >>>>>> CurrentUser, the manager can only look at his own work.  I
> >>>>>> already added the manager to the Admins group.  Can I give the
> >>>>>> manager the ability to look at other's work without creating new
> >>>>>> queries and macros?  Thank you.
> >>>>>
> >>>>> You can use one form and in it's open event set the recordsource
> >>>>> property for the form:
> >>>>> There's code in the security FAQ you can use to determine if the
> >>>>> current user is a member of a group.  Put that function in a
> >>>>> module (give the module a differnent name than the function).
> >>>>> Then use it like...
> >>>>>
> >>>>> If faqIsUserInGroup("Admins", CurrentUser()) then
> >>>>> Me.RecordSource = "SELECT * FROM SomeTable"
> >>>>> else
> >>>>> Me.RecordSource = "SELECT * FROM SomeTable WHERE SomeField = " &
> >>>>> chr(34) & CurrentUser() & chr(34)
> >>>>> end if
> >>>>>
> >>>>> Watch for newsreader wrap in the above
> >>>>> --
> >>>>> Joan Wild
> >>>>> Microsoft Access MVP
>
>
>