Command button display report based on user

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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 said:
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

(no e-mails, please!)



Mary said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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

(no e-mails, please!)



Mary said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top