TempVars; Why and What? (1 Viewer)

[zeroaccess]

I've never used them but am thinking about using them in one instance after reading though a few of the posts here.

Interested to hear your idea.

I like them because of their ease of use.

 

[zeroaccess]

SEPTEMBER 27, 2010
Power Tip: Maximize the use of TempVars in Access 2007 and 2010
By the Access team

 

[Uncle Gizmo]

You've got to see this excellent video on tempvars by Daniel Pineault:-

 

[GPGeorge]

You've got to see this excellent video on tempvars by Daniel Pineault:

One of my favorite techniques for filtering queries that are used as recordsources for forms and rowsources for list and combo boxes involves tempvars.
Here's an example.

SELECT tblpublication.publicationid,
       tblpublication.publicationtitle,
       tblpublication.volume,
       tblpublication.numberofvolumes,
       tblpublication.catalognumber,
       tblpublication.yearpublished,
       tblpublication.pages,
       tblpublication.edition,
       tblpublication.printing,
       tblpublication.coverphotolink,
       tblpublication.comments,
       tblpublication.mediaconditionid,
       tblpublication.publisherid,
       tblpublication.mediatypeid,
       tblpublication.isbn,
       tblpublication.confidencelevel,
       tblpublication.interalcomments,
       tblpublication.shelfid,
       tblpublication.listprice
FROM   tblpublication
WHERE   Iif(Tempvarslong("lngpublicationid") = 0, 0, [publicationid]) ) 
               IN (0,             Tempvarslong("lngpublicationid") 
ORDER  BY tblpublication.publicationtitle;

The way it works is this. In a combo or list box on the form, the row source is a Union Query.

SELECT 0 AS PublicationID, " <ALL>" AS PublicationTitle
FROM tblPublication
UNION SELECT  PublicationID,   PublicationTitle
FROM tblPublication
ORDER BY PublicationTitle

The AfterUpdate event of the combo box sets the value of TempVar lngPublicationID to the value selected, either 0 or one of the existing Publication Primary Keys.

If the user selects 0 , " <ALL>" in the combo box, the form's recordsource query will match 0 and 0 and therefore return all of the records in the table. If any single PublicationID is selected in the Combo box, the form's recordsource is filtered to that specific PublicationID.

 

[GPGeorge]

Spoiler:

One of my favorite techniques for filtering queries that are used as recordsources for forms and rowsources for list and combo boxes involves tempvars.
Here's an example.

SELECT tblpublication.publicationid,
       tblpublication.publicationtitle,
       tblpublication.volume,
       tblpublication.numberofvolumes,
       tblpublication.catalognumber,
       tblpublication.yearpublished,
       tblpublication.pages,
       tblpublication.edition,
       tblpublication.printing,
       tblpublication.coverphotolink,
       tblpublication.comments,
       tblpublication.mediaconditionid,
       tblpublication.publisherid,
       tblpublication.mediatypeid,
       tblpublication.isbn,
       tblpublication.confidencelevel,
       tblpublication.interalcomments,
       tblpublication.shelfid,
       tblpublication.listprice
FROM   tblpublication

WHERE   Iif(Tempvarslong("lngpublicationid") = 0, 0, [publicationid]) ) 
               IN (0,             Tempvarslong("lngpublicationid") 



	

		Spoiler:  
	
	

		

			
ORDER  BY tblpublication.publicationtitle;
		
	

Spoiler:

The way it works is this. In a combo or list box on the form, the row source is a Union Query.

SELECT 0 AS PublicationID, " <ALL>" AS PublicationTitle
FROM tblPublication
UNION SELECT  PublicationID,   PublicationTitle
FROM tblPublication
ORDER BY PublicationTitle

The AfterUpdate event of the combo box sets the value of TempVar lngPublicationID to the value selected, either 0 or one of the existing Publication Primary Keys.

If the user selects 0 , " <ALL>" in the combo box, the form's recordsource query will match 0 and 0 and therefore return all of the records in the table. If any single PublicationID is selected in the Combo box, the form's recordsource is filtered to that specific PublicationID.

I use a series of helper functions in VBA, rather than the basic TempVars!lngPublicationID for a couple of reasons. One is that it forces the evaluation of the expression, which isn't always the case. Some people use Eval(TempVars!lngPublicationID) for the same reason.

I also like it to ensure the datatype I'm looking for is appropriate.

 

[Uncle Gizmo]

WHERE Iif(Tempvarslong("lngpublicationid") = 0, 0, [publicationid]) ) IN (0, Tempvarslong("lngpublicationid")

I like the look of that, but I can't quite grasp how it works!

Seeing as your query is based on a single table you could take out the table reference like this:-

You can remove most of the references to the table name when your SQL Statement is based on a single table. This trick depends on NOT just selecting the table name, but also the DOT “.” following it. You need to select the dot (.) to prevent your action removing the actual reference to the table in the FROM Clause.

I demo how to do it in this video clip:-

It's the seventh tip down on my "Nifty Tips" page here:-

Nifty Tips - Nifty Access

Nifty Video Tips Using an excellent new feature of Elementor Page Builder I am now able to show you time indexed clips from my MS Access Videos. This way you can quickly and easily see a video demonstration of just a few minutes, a Demo that gives you a leg up in your MS Access

www.niftyaccess.com

 

[GPGeorge]

I like the look of that, but I can't quite grasp how it works!

Seeing as your query is based on a single table you could take out the table reference like this:-

You can remove most of the references to the table name when your SQL Statement is based on a single table. This trick depends on NOT just selecting the table name, but also the DOT “.” following it. You need to select the dot (.) to prevent your action removing the actual reference to the table in the FROM Clause.

I demo how to do it in this video clip:-

It's the seventh tip down on my "Nifty Tips" page here:-

Nifty Tips - Nifty Access

Nifty Video Tips Using an excellent new feature of Elementor Page Builder I am now able to show you time indexed clips from my MS Access Videos. This way you can quickly and easily see a video demonstration of just a few minutes, a Demo that gives you a leg up in your MS Access

www.niftyaccess.com

I get that, but this example was pulled from a demo that I wanted to be fully explicit. In most cases I would not specify the table as you point out.

How it works in more detail.

The row source for the combo box is:

SELECT P.PublicationID, P.PublicationTitle
            FROM tblPublication P 
            UNION
            SELECT 0 AS PublicationID, " <All Publications> " AS PublicationTitle
            FROM tblPublication     
            ORDER BY PublicationTitle
        END

When the user selects a publication title from the combo box, the AfterUpdate event of that combo box fires, changing the value of the tempvar.

If the user has selected "All Publications", the tempvar value is set to 0; any other selection sets the tempvar to an existing Primary Key value.
Then, the form is requeried, which applies the selected tempvar value to its recordsource.

Private Sub cboSelectPublication_AfterUpdate()

    With Me
        TempVars.Add Name:="lngPublicationID", Value:=Nz(.cboSelectPublication, 0)
        .Requery
    End With

End Sub

This is a rare occasion where the QBE grid is maybe easier to grasp than the SQL.

The In() clause in the criteria has two values, 0 and the current value of the tempvar, i.e. 0 and 123.

In the return field line, one of two values will be displayed, 0 or the value of the Primary Key selected in the combo box, i.e. 0 or 123.

If the tempvar is 0, then 0 matches 0, which is True, and all records are returned. Otherwise, 123 would match the currently selected PublicationID, returning that record only.

 

[Uncle Gizmo]

In most cases I would not specify the table as you point out.

Acknowledged, typically the table isn't specified. This was an opportunity to showcase my 'Take out the Tables' technique...

 

[Uncle Gizmo]

I don't recall where I found it, but I found a technique using a function which I blogged about on my website here:- https://www.niftyaccess.com/combo-filter-all-or-some/

There's also an excellent example from Microsoft here:- (Not Tried it Yet)

Add '(All)' to a combo box or list box

Office VBA reference topic

learn.microsoft.com

 

[GPGeorge]

I settled on the tempvars alternative with the use of the criteria I explained above for two reasons.

• The VBA is about as simple as it gets. Two lines are needed.

• One to set the tempvars value
• One to requery the form.

That's it. There is no need to swap or modify the row source or the form's record source.​

• The query opens regardless of whether the form is open or not. It relies on the current value of the tempvar, not a reference to a control on a form. And that value persists until it is reset in the AfterUpdate event of the combo box. I like that for trouble-shooting.

 

[Uncle Gizmo]

I settled on the tempvars alternative

Re:- TempVars - I have been discussing the various methods of Passing data between forms with Pat.

I prefer using custom properties within the form code module. Pat utilizes a hidden form. I never took to the hidden form method but Pat explained that one of the advantages of this method is that She can unhide the form when developing. This aids fault finding / bug tracing as you can see the variables changing in the form. This is an attractive feature that I could do well to assimilate!

There are other methods like global variables, directly setting the controls in the called form, hidden controls which are there just to facilitate the transfer of this sort of information. They normally have their back color set to yellow. I call them "yellow perils"... I dislike them which is why I went over to custom properties it of the VBA.

And then there are "TempVars".. I really like this method and have been updating my code to take advantage of them. I particularly like Daniel Pineaults video - which gave me more reason for adopting them!

Microsoft Access - Working With TempVars in VBA​

Daniel Pineaults YouTube Channel

https://www.youtube.com/@DanielPineault/videos

 

[gemma-the-husky]

Though I knew about them, I have not had problems that required their use. But if I recall correctly, they persist after a RESET operation. Could be wrong about that, but ...

Let's say you were debugging code that had user info in public variables in a general module and you get one of those dreated pop-ups that offer you the chance to DEBUG or RESET. If you pick RESET then the variables are reset too. But I believe TempVars don't reset so you could continue working with the values you had set in them.

It was precisely because they are limited in what they can store that I never used them. I was always storing complex data types (created via TYPE statement) which were unsuitable for TempVars.

I don't use them. I've just never got in the habit. I think if you get an unhandled rte, you may have problems beside a reset public variable. I just try to build robust code.

I think one benefit is that you can refer directly to the tempvar in a query, rather than having to use a function to read a variable value.

 

[NauticalGent]

I think one benefit is that you can refer directly to the tempvar in a query, rather than having to use a function to read a variable value.

AFAICT, that is the only advantage.

 

[Gasman]

Strangely enough I have always used Tempvars("MyName") = "Paul" syntax.