Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Output To Using Recordset or Filter

Discussion in 'Information Technology' started by SusanV, Oct 27, 2005.

  1. SusanV

    SusanV
    Expand Collapse
    Guest

    Hi all,

    I have a new user request which I can't seem to find an easy way to
    accommodate. Currently users have a button they click and a report opens for
    printing. The report is filtered to only show data for the current selected
    record. This works wonderfully. However, now they need to output the data
    for just the selected record to Excel.

    I know I can use a stored parameter query, but I have a couple of users who
    like to mess with my queries, and unfortunately, queries aren't protected
    via MDE (user level security is NOT an option, as per management).

    Is there a way to either filter an output to or use a recordsource for the
    output?

    If not i suppose i can dump the data to a temp table and use that for an
    output source, but would prefer to avoid the database bloat...


    TIA for any suggestions,

    SusanV
     
  2. Loading...


  3. Sheila D

    Sheila D
    Expand Collapse
    Guest

    You could alwayd hide the query - select the query and choose properties
    button on toolbar then hide

    HTH - Sheila

    "SusanV" wrote:

    > Hi all,
    >
    > I have a new user request which I can't seem to find an easy way to
    > accommodate. Currently users have a button they click and a report opens for
    > printing. The report is filtered to only show data for the current selected
    > record. This works wonderfully. However, now they need to output the data
    > for just the selected record to Excel.
    >
    > I know I can use a stored parameter query, but I have a couple of users who
    > like to mess with my queries, and unfortunately, queries aren't protected
    > via MDE (user level security is NOT an option, as per management).
    >
    > Is there a way to either filter an output to or use a recordsource for the
    > output?
    >
    > If not i suppose i can dump the data to a temp table and use that for an
    > output source, but would prefer to avoid the database bloat...
    >
    >
    > TIA for any suggestions,
    >
    > SusanV
    >
    >
    >
    >
     
  4. SusanV

    SusanV
    Expand Collapse
    Guest

    Hi Sheila,

    I already went this route before - and I have a couple of users who know
    enough to change their options to show hidden using a different database
    even though I hide the Tools>>Options in my MDE.

    I'll just dump out the related records to a temp table and delete thetable
    after, I have nightly compaction set up already anyways.

    Thanks for your input!

    Susan

    "Sheila D" <SheilaD@discussions.microsoft.com> wrote in message
    news:153834AC-79FB-4C0C-AEEE-53F99AF4C532@microsoft.com...
    > You could alwayd hide the query - select the query and choose properties
    > button on toolbar then hide
    >
    > HTH - Sheila
    >
    > "SusanV" wrote:
    >
    >> Hi all,
    >>
    >> I have a new user request which I can't seem to find an easy way to
    >> accommodate. Currently users have a button they click and a report opens
    >> for
    >> printing. The report is filtered to only show data for the current
    >> selected
    >> record. This works wonderfully. However, now they need to output the data
    >> for just the selected record to Excel.
    >>
    >> I know I can use a stored parameter query, but I have a couple of users
    >> who
    >> like to mess with my queries, and unfortunately, queries aren't protected
    >> via MDE (user level security is NOT an option, as per management).
    >>
    >> Is there a way to either filter an output to or use a recordsource for
    >> the
    >> output?
    >>
    >> If not i suppose i can dump the data to a temp table and use that for an
    >> output source, but would prefer to avoid the database bloat...
    >>
    >>
    >> TIA for any suggestions,
    >>
    >> SusanV
    >>
    >>
    >>
    >>
     
  5. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 26 Oct 2005 08:54:01 -0400, "SusanV"
    <svanallen@nospam-mvps.org> wrote:

    >Is there a way to either filter an output to or use a recordsource for the
    >output?
    >
    >If not i suppose i can dump the data to a temp table and use that for an
    >output source, but would prefer to avoid the database bloat...


    Create and save the Query, use it, and delete it:

    Dim qd As DAO.Querydef
    Dim db As DAO.Database
    Dim prm As Parameter
    Set db = CurrentDb
    On Error Resume Next ' ignore error if there's nothing to delete
    db.Querydefs.Delete "TempQuery"
    On Error Goto 0 ' or to your error handler code
    <create the SQL string for your filtered query>
    Set qd = db.CreateQuerydef("TempQuery", strSQL)
    For Each prm in qd.Parameters
    prm.Value = Eval(prm.Name) ' fill in parameters
    Next prm
    DoCmd.TransferSpreadsheet...

    John W. Vinson[MVP]
     
  6. SusanV

    SusanV
    Expand Collapse
    Guest

    Thanks John!

    I tried to do this for an earlier issue and couldn't seem to get the
    querydef created (I don't remember the error now).

    I will definitely give your code a go when I get a few minutes to go back to
    this project and let you know how it works out.

    SusanV

    "John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
    news:1kcvl1dch2sfamgfj6oqhllbrm7i9rt2ui@4ax.com...
    > On Wed, 26 Oct 2005 08:54:01 -0400, "SusanV"
    > <svanallen@nospam-mvps.org> wrote:
    >
    >>Is there a way to either filter an output to or use a recordsource for the
    >>output?
    >>
    >>If not i suppose i can dump the data to a temp table and use that for an
    >>output source, but would prefer to avoid the database bloat...

    >
    > Create and save the Query, use it, and delete it:
    >
    > Dim qd As DAO.Querydef
    > Dim db As DAO.Database
    > Dim prm As Parameter
    > Set db = CurrentDb
    > On Error Resume Next ' ignore error if there's nothing to delete
    > db.Querydefs.Delete "TempQuery"
    > On Error Goto 0 ' or to your error handler code
    > <create the SQL string for your filtered query>
    > Set qd = db.CreateQuerydef("TempQuery", strSQL)
    > For Each prm in qd.Parameters
    > prm.Value = Eval(prm.Name) ' fill in parameters
    > Next prm
    > DoCmd.TransferSpreadsheet...
    >
    > John W. Vinson[MVP]
     

Share This Page