Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Setting Report Filter property using Macros (XP)

    I have a Form that controls a report which, depending on radio button selection, runs a macro part of which is to set the Report Filter and Report FilterOn properties.

    On running the form and it's associated macros I get the error:

    "the object you referenced in the Visual Basic procedure as an OLE object isn't an OLE object"

    I am using SetValue with [Reports]![Contact List (042)].[Filter] set to a given value and then SetValue for [Reports]![Contact List (042)].[FilterOn] set to a value of "Yes"" or "No" (or perhaps it wants the numeric value? anyway this isn't the immediate problem)

    Help indicates I should be able to set this Property so what am I doing wrong?

    Many thanks........................ liz

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Setting Report Filter property using Macros (XP)

    In itself, this is possible. What did you enter as Expression for the first SetValue action? And what is the data type of the field(s) in this expression?

    The expression must be a string. If you want to set a filter on a numeric field, it should look like this:<pre>"[MemberID]=37"</pre>

    For a text field, the value must be enclosed in quotes:<pre>"[LastName]='Jones'"</pre>

    For date fields, the date must be enclosed in # characters:<pre>"[BirthDate]=#05/22/1970#"</pre>


  3. #3
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Setting Report Filter property using Macros (XP)

    Hans,

    Thank you once again, I think I'm almost there but what happens if the value is from a form? I have a form from which the user selects the subset of information he's interested in reporting; so, for example, from the total list of people he may select to see those from only one organisation. The selection form has an unbound combo field which presents the valid chioces based on an SQL query. On selecting to print a macro sets the report filter to the selected organisation e.g. "[QRYRPT Contact List]![ORG_Name] = Forms![RPT Contact List (042)]![SelectORG_Name] ", sets the report filter on and runs the report, since the ORG_Name field is tex should the fild reference be in single quotes and if it was a date would I need to enclose the field reference in hashes? Do you believe this should work if so then I have probably got some silly syntax error or invalid field reference that I can find for myself.

    The unbound combo field doesn't have a format set, is that OK? (and if I need to do this if it was an autonumber field what format would I use as Long Integer doesn't exist?).

    Related to this problem is the following (I haven't got to testing this bit yet). I have a habit of using an autonumber field with every table, this is obviously a long integer, in many cases I will use a lookup as part of a table design when defining foriegn keys; this means the user will see something meaningful such as a name even though the field holds a numeric value. This is the question: I assume that an unbound combo field on a form will work the same way e.g. the SQL will select an Id (long integer) and a name (text), the dropdown will display a name and once selected that name is what the user sees in the field although I believe that the underlying value is an Id (long integer). Is this your understanding?

    thanks again....................liz

  4. #4
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Setting Report Filter property using Macros (XP)

    Hans,

    I tried the second line of code on its own to, as I thought, have a small success; however...

    This is the macro line to set the filter off so I am using SetValue with the field [Reports]![Contact List (042)].[FilterOn] and the expression is No not in quotes, (because it works Ok for the visible property like that). I get the same message: "the object you referenced in the Visual Basic procedure as an OLE object isn't an OLE object".
    I must be missing something really obvious... help!!!!

    liz

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Setting Report Filter property using Macros (XP)

    Hi Liz,
    Hans doesn't seem to be around at the moment, so I'll try to give you a hand (he apparently does take time to eat meals occasionally).
    I believe what you need to do is put the entire control name that contains the value you want to filter on in single quotes if it is going to be a text value, i.e. "[QRYRPT Contact List]![ORG_Name] = 'Forms![RPT Contact List (042)]![SelectORG_Name]' " but if it is a number then it should work without the single quotes. However I don't normally with with macros to do this sort of thing, but use a VBA event procedure where the syntax is somewhat simpler. So give that a try - otherwise Hans should be back online before too long.
    Wendell

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Setting Report Filter property using Macros (XP)

    If you're opening a report and setting a filter, it would be easier to do it in one command: the OpenReport action has a WHERE-condition argument.

    If I understand you correctly, the first column in the combo box is numeric and hidden; I assume that you left the Bound Column property at 1. This means that the 'value' of the combo box is numeric too (even though the combo box displays text values). Therefore the filter should act on a numeric field too, something like Org_ID, not Org_Name.

    So I would remove the SetValue actions from the macro, and replace them by an OpenReport action with Where-condition set to somthing like

    [Org_ID]=[Forms]![...]![SelectORG_Name]

    (fill in the name of the form!)

    Like Wendell, I never use macros myself, I always use VBA code; it's much easier to debug than a macro.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •