Setting Report Filter property using Macros (XP)

Topic

New Reply

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

Reply | Quote

Replies

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:

"[MemberID]=37"

For a text field, the value must be enclosed in quotes:

"[LastName]='Jones'"

For date fields, the date must be enclosed in # characters:

"[BirthDate]=#05/22/1970#"

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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:

"[MemberID]=37"

For a text field, the value must be enclosed in quotes:

"[LastName]='Jones'"

For date fields, the date must be enclosed in # characters:

"[BirthDate]=#05/22/1970#"

Reply | Quote