Command button display report based on user

Discussion in 'Microsoft Access Forms' started by Guest, Jun 20, 2005.

  1. Guest

    Guest 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
     
    Guest, Jun 20, 2005
    #1
    1. Advertisements

  2. 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" <> wrote in message
    news:...
    > 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
    >
     
    Douglas J. Steele, Jun 21, 2005
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    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" <> wrote in message
    > news:...
    > > 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
    > >

    >
    >
    >
     
    Guest, Jun 21, 2005
    #3
  4. Guest

    Guest Guest

    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" <> wrote in message
    > > news:...
    > > > 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
    > > >

    > >
    > >
    > >
     
    Guest, Jun 21, 2005
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Eric

    Toggle Command Button based upon field value..

    Eric, Jan 9, 2004, in forum: Microsoft Access Forms
    Replies:
    1
    Views:
    245
    Jim Allensworth
    Jan 9, 2004
  2. Guest
    Replies:
    1
    Views:
    743
    Cheryl Fischer
    May 27, 2004
  3. Replies:
    6
    Views:
    483
  4. Replies:
    0
    Views:
    518
  5. Danny Lesandrini

    Re: Create a report based on a filtered form using a command button

    Danny Lesandrini, Feb 17, 2009, in forum: Microsoft Access Forms
    Replies:
    1
    Views:
    517
    Douglas J. Steele
    Feb 18, 2009
Loading...

Share This Page