Hello, I have a command button on a form that opens a report in print
preview. This is an Access 97 secured db. I would like to use one button,
but have it include records based on the current user. For example, if user
ms1234 clicks the button, I would like data for Workgroups 1, 2 and 3. If
user dc9999 clicks the button, just display data for Workgroup 4. I think it
will look something like this, but need your help. Thanks! Mary

Function OpenProjectRpt()
Select Case CurrentUser
Case "ms1234"
DoCmd.OpenReport "DailyProjectActivity"
WHERE Forms![Menu]![Workgroup] = “1” or “2” or “3”
Case "at1234"
DoCmd.OpenReport "DailyProjectActivity"
WHERE Forms![Menu]![Workgroup] = “4”
Case "ms9876"
DoCmd.OpenReport "DailyProjectActivity"
WHERE Forms![Menu]![Workgroup] = “1” or “4”
Case Else
MsgBox "Restricted Database! Please verify Username and Password."
End Select
End Function

I'm not sure what Forms![Menu]![Workgroup] is supposed to be in conjunction
with opening reports, but try something like:

Function OpenProjectRpt()
Select Case CurrentUser
Case "ms1234"
DoCmd.OpenReport "DailyProjectActivity", , ,
"Forms![Menu]![Workgroup] IN ('1', '2', '3')"
Case "at1234"
DoCmd.OpenReport "DailyProjectActivity", , ,
"Forms![Menu]![Workgroup] = '4'"
Case "ms9876"
DoCmd.OpenReport "DailyProjectActivity", , ,
"Forms![Menu]![Workgroup] IN ('1', '4')"
Case Else
MsgBox "Restricted Database! Please verify Username and Password."
End Select
End Function

Note that I've changed the double quotes to single quotes in the WHERE
clauses. If Forms![Menu]![Workgroup] is numeric, not text, lose the single
quotes.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Mary" <(E-Mail Removed)> wrote in message
news:4FA5D6C7-8839-4E0A-B817-(E-Mail Removed)...
> Hello, I have a command button on a form that opens a report in print
> preview. This is an Access 97 secured db. I would like to use one
> button,
> but have it include records based on the current user. For example, if
> user
> ms1234 clicks the button, I would like data for Workgroups 1, 2 and 3. If
> user dc9999 clicks the button, just display data for Workgroup 4. I think
> it
> will look something like this, but need your help. Thanks! Mary
>
> Function OpenProjectRpt()
> Select Case CurrentUser
> Case "ms1234"
> DoCmd.OpenReport "DailyProjectActivity"
> WHERE Forms![Menu]![Workgroup] = "1" or "2" or "3"
> Case "at1234"
> DoCmd.OpenReport "DailyProjectActivity"
> WHERE Forms![Menu]![Workgroup] = "4"
> Case "ms9876"
> DoCmd.OpenReport "DailyProjectActivity"
> WHERE Forms![Menu]![Workgroup] = "1" or "4"
> Case Else
> MsgBox "Restricted Database! Please verify Username and Password."
> End Select
> End Function
>

I had the Forms part wrong, I changed that to show the Report control that I
want to refer to. I want to see a report on 3 workgroups if the user id is
ms4753. I'm getting a syntax error. Here is the actual code I tried.
[Workgroup] is the name of the control on the report. I added the
acViewPreview because it wanted to go right to printing without it.

Function OpenProjectRpt()
Select Case CurrentUser
Case "ms4753"
DoCmd.OpenReport "Detailed Activity_Team", acViewPreview
"Reports![Detailed Activity_Team]![Workgroup] IN ('Change /
Notify', 'AM_ChgNfy', 'AD')"
End Select
End Function

Thanks again.

"Douglas J. Steele" wrote:

> I'm not sure what Forms![Menu]![Workgroup] is supposed to be in conjunction
> with opening reports, but try something like:
>
> Function OpenProjectRpt()
> Select Case CurrentUser
> Case "ms1234"
> DoCmd.OpenReport "DailyProjectActivity", , ,
> "Forms![Menu]![Workgroup] IN ('1', '2', '3')"
> Case "at1234"
> DoCmd.OpenReport "DailyProjectActivity", , ,
> "Forms![Menu]![Workgroup] = '4'"
> Case "ms9876"
> DoCmd.OpenReport "DailyProjectActivity", , ,
> "Forms![Menu]![Workgroup] IN ('1', '4')"
> Case Else
> MsgBox "Restricted Database! Please verify Username and Password."
> End Select
> End Function
>
> Note that I've changed the double quotes to single quotes in the WHERE
> clauses. If Forms![Menu]![Workgroup] is numeric, not text, lose the single
> quotes.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
>
> "Mary" <(E-Mail Removed)> wrote in message
> news:4FA5D6C7-8839-4E0A-B817-(E-Mail Removed)...
> > Hello, I have a command button on a form that opens a report in print
> > preview. This is an Access 97 secured db. I would like to use one
> > button,
> > but have it include records based on the current user. For example, if
> > user
> > ms1234 clicks the button, I would like data for Workgroups 1, 2 and 3. If
> > user dc9999 clicks the button, just display data for Workgroup 4. I think
> > it
> > will look something like this, but need your help. Thanks! Mary
> >
> > Function OpenProjectRpt()
> > Select Case CurrentUser
> > Case "ms1234"
> > DoCmd.OpenReport "DailyProjectActivity"
> > WHERE Forms![Menu]![Workgroup] = "1" or "2" or "3"
> > Case "at1234"
> > DoCmd.OpenReport "DailyProjectActivity"
> > WHERE Forms![Menu]![Workgroup] = "4"
> > Case "ms9876"
> > DoCmd.OpenReport "DailyProjectActivity"
> > WHERE Forms![Menu]![Workgroup] = "1" or "4"
> > Case Else
> > MsgBox "Restricted Database! Please verify Username and Password."
> > End Select
> > End Function
> >
>
>
>

I found another way to handle this - my query now has sql based on user id,
so the report opens with only the records for the current users assigned
groups. The button just opens the report in print preview, the recordset is
determined by the underlying query based on user id.

"Mary" wrote:

> I had the Forms part wrong, I changed that to show the Report control that I
> want to refer to. I want to see a report on 3 workgroups if the user id is
> ms4753. I'm getting a syntax error. Here is the actual code I tried.
> [Workgroup] is the name of the control on the report. I added the
> acViewPreview because it wanted to go right to printing without it.
>
> Function OpenProjectRpt()
> Select Case CurrentUser
> Case "ms4753"
> DoCmd.OpenReport "Detailed Activity_Team", acViewPreview
> "Reports![Detailed Activity_Team]![Workgroup] IN ('Change /
> Notify', 'AM_ChgNfy', 'AD')"
> End Select
> End Function
>
> Thanks again.
>
> "Douglas J. Steele" wrote:
>
> > I'm not sure what Forms![Menu]![Workgroup] is supposed to be in conjunction
> > with opening reports, but try something like:
> >
> > Function OpenProjectRpt()
> > Select Case CurrentUser
> > Case "ms1234"
> > DoCmd.OpenReport "DailyProjectActivity", , ,
> > "Forms![Menu]![Workgroup] IN ('1', '2', '3')"
> > Case "at1234"
> > DoCmd.OpenReport "DailyProjectActivity", , ,
> > "Forms![Menu]![Workgroup] = '4'"
> > Case "ms9876"
> > DoCmd.OpenReport "DailyProjectActivity", , ,
> > "Forms![Menu]![Workgroup] IN ('1', '4')"
> > Case Else
> > MsgBox "Restricted Database! Please verify Username and Password."
> > End Select
> > End Function
> >
> > Note that I've changed the double quotes to single quotes in the WHERE
> > clauses. If Forms![Menu]![Workgroup] is numeric, not text, lose the single
> > quotes.
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no e-mails, please!)
> >
> >
> >
> > "Mary" <(E-Mail Removed)> wrote in message
> > news:4FA5D6C7-8839-4E0A-B817-(E-Mail Removed)...
> > > Hello, I have a command button on a form that opens a report in print
> > > preview. This is an Access 97 secured db. I would like to use one
> > > button,
> > > but have it include records based on the current user. For example, if
> > > user
> > > ms1234 clicks the button, I would like data for Workgroups 1, 2 and 3. If
> > > user dc9999 clicks the button, just display data for Workgroup 4. I think
> > > it
> > > will look something like this, but need your help. Thanks! Mary
> > >
> > > Function OpenProjectRpt()
> > > Select Case CurrentUser
> > > Case "ms1234"
> > > DoCmd.OpenReport "DailyProjectActivity"
> > > WHERE Forms![Menu]![Workgroup] = "1" or "2" or "3"
> > > Case "at1234"
> > > DoCmd.OpenReport "DailyProjectActivity"
> > > WHERE Forms![Menu]![Workgroup] = "4"
> > > Case "ms9876"
> > > DoCmd.OpenReport "DailyProjectActivity"
> > > WHERE Forms![Menu]![Workgroup] = "1" or "4"
> > > Case Else
> > > MsgBox "Restricted Database! Please verify Username and Password."
> > > End Select
> > > End Function
> > >
> >
> >
> >