TempVars; Why and What?

[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.

 

[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.

 

[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.

 

[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.