PC Review


Reply
Thread Tools Rate Thread

Command button display report based on user

 
 
=?Utf-8?B?TWFyeQ==?=
Guest
Posts: n/a
 
      20th Jun 2005
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

 
Reply With Quote
 
 
 
 
Douglas J. Steele
Guest
Posts: n/a
 
      21st Jun 2005
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:(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
>



 
Reply With Quote
 
 
 
 
=?Utf-8?B?TWFyeQ==?=
Guest
Posts: n/a
 
      21st Jun 2005
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:(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
> >

>
>
>

 
Reply With Quote
 
=?Utf-8?B?TWFyeQ==?=
Guest
Posts: n/a
 
      21st Jun 2005
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:(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
> > >

> >
> >
> >

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: Create a report based on a filtered form using a command button Danny Lesandrini Microsoft Access Forms 1 18th Feb 2009 03:11 PM
Deselect Command Button by Selecting another Command Button gmcnaugh Microsoft Excel Programming 3 2nd Sep 2008 05:59 PM
Command Button Pictures Taken from Command Bar Button Icons acx@centrum.cz Microsoft Access Forms 0 2nd Dec 2007 01:23 PM
Help plz. Command button to execute a report preview and filtered based on item selected in combo box???? abrandt1@gmail.com Microsoft Access Reports 0 26th May 2006 09:47 PM
VB's Command Button vs Form's Command Button Ronald Dodge Microsoft Excel Programming 3 24th May 2006 02:23 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:57 AM.