Correct syntax for SetTempVar

[SimonG]

Hi,

Using Access 2007 (sp1) on a Vista pc.
I'm having problems using the SetTempVar command, can someone advise me how
this command should be used?

I have a split view form. In the header are three text boxes (unbound). The
objective is for the user to type into these and have the list section of the
form filter accordingly. I am trying to achieve this using an embedded macro
(the macro may become external in the fullness of time, but this is where I
currently am). The macro needs to read the text box contents into variables,
using SetTempVar, which are then built into a filer and applied to the form.

Keeping things simple in the first instance, I'm still trying to get the
first box to work. As soon as the macro reaches the SetTempVar line Access
gives an error message: “A problem occurred while Microsoft Access was
communicating with the OLE server or ActiveX controlâ€

I have searched the around the web, and the books I have, but haven't found
anything that helps. Can anyone offer some advice please?

Many thanks,
Simon

 

[Steve Schapel]

Simon,

What Event do you have your macro assigned to? And what are the Actions
of your macro?

My other comment is that this seems to be an unduly complicated
approach. Why not use an ApplyFilter action, or a SetValue action to
adjust the form's Record Source property? This doesn't really seem to
be the ideal usage for a SetTempVar anyway.

 

[SimonG via AccessMonster.com]

Hi Steve,

The event the macro is linked to is “After Updateâ€.

The original code was intended to take the three values (from the three text
boxes), build this into a string and then pass this to an ApplyFilter.
Original code:
[CurrentProject].[IsTrusted]
SetTempVar
vFilterRange , [Forms]!
[f_StockSelect]![CmbFindRange]
Not IsNull([TempVars]![vFilterRange])
ApplyFilter
, [q_Stock].[Range]=[vFilterRange],


RemoveTempVar
vFilterRange
(I've split the lines into three to show Condition, Action and Argument
columns.)

Following your advice (and as I've currently only got one box to deal with)
I've simplified this now to one line:
ApplyFilter , [q_Stock]![Range]=[Forms]!
[f_StockSelect]![CmbFindRange],

When this simplified version is activated, a pop-up appears asking for a
value for [Forms]![f_StockSelect]![CmbFindRange]
Does this mean the macro isn't picking up the value from my input box(?), and
if so how I get the entered value passed to the macro?

Many thanks,
Simon

Simon,

What Event do you have your macro assigned to? And what are the Actions
of your macro?

My other comment is that this seems to be an unduly complicated
approach. Why not use an ApplyFilter action, or a SetValue action to
adjust the form's Record Source property? This doesn't really seem to
be the ideal usage for a SetTempVar anyway.

Hi,

[quoted text clipped - 19 lines]

Many thanks,
Simon

 

[SimonG via AccessMonster.com]

Ok, the simple ApplyFilter works when followed by a Requery line (thanks
Steve for putting me on the right lines) (no idea where, or why, the value
request pop-up, reported in last posting, was appearing, but its gone now).

This brings me full circle to now needing to read up to three values from
unbound controls and build them into a filter. Usually I would read these
into individual variables and manipulate these, but I'm still no clearer on
how to read a value from and unbound control into a TempVar.

Any advice gladly received,
Simon

Hi Steve,

The event the macro is linked to is “After Updateâ€.

The original code was intended to take the three values (from the three text
boxes), build this into a string and then pass this to an ApplyFilter.
Original code:
[CurrentProject].[IsTrusted]
SetTempVar
vFilterRange , [Forms]!
[f_StockSelect]![CmbFindRange]
Not IsNull([TempVars]![vFilterRange])
ApplyFilter
, [q_Stock].[Range]=[vFilterRange],


RemoveTempVar
vFilterRange
(I've split the lines into three to show Condition, Action and Argument
columns.)

Following your advice (and as I've currently only got one box to deal with)
I've simplified this now to one line:
ApplyFilter , [q_Stock]![Range]=[Forms]!
[f_StockSelect]![CmbFindRange],

When this simplified version is activated, a pop-up appears asking for a
value for [Forms]![f_StockSelect]![CmbFindRange]
Does this mean the macro isn't picking up the value from my input box(?), and
if so how I get the entered value passed to the macro?

Many thanks,
Simon

Simon,

[quoted text clipped - 11 lines]