Form_Open use variable for filter in Form's RecordSource using QBE grid Design View (1 Viewer)

Status
Not open for further replies.

Rx_

Nothing In Moderation
Local time
Today, 09:55
Joined
Oct 22, 2009
Messages
2,803
Method for Developers to open Form Stand-Alone and add Filter in Form_Open

During the Form_Open event, a variable (in this case a number) can be passed to the Form's Record Source property. The Record Source shows up in the Form's property Data tab. Use the builder to view the QBE (Query by Example) Grid view.
Typically, one of the columns require a filter e.g Customer_ID
This filters the data for the form.

There are several ways to accomplish this. The QBE column filter for example can take a reference from a ListBox on another form.

The code below checks to see if the Form was called with OpenArgs. Or if the form was called from another form where a listbox was selected.

Developer or Tester Stand-Alone Mode
What if as a developer, I know the Primary Key's used for the Query's filter and just want to open the form.
An InputBox with a default value sure comes in handy!
Here is the problem, assigning the inputbox value to a form's object (like a textbox) won't work with the Form Open.
The Textbox isn't created until after the Form Open is finished and by then the Form's Query has run.

Of course, we could use a Public Variable. But, my preference is to have everything self contained in a form.

Solution - TempVars
TempVars (sicne Access 2007) are the solution. They behave like a public variable in the QBE grid. They can be declared on demand without a Dim statement. Another bonus, each form can have up to 255 of them!
They also clean up and go away every time the form is closed.

The following code example gives me all three options of opening a form.

The big deal is assigning a filter on demand during the Form Open event.

Code:
Private Sub Form_Open(Cancel As Integer)
  On Error GoTo Bar_Order_One_bourbon_One_Scotch_And_One_Beer
Dim lngID_Wells As Long
'   TempVars.Add "lngID_Well", 4969 ' used for testing TempVars

' This form can be opened up with OpenArgs, another form (if its open) or a Developer with Inputbox in standalone mode

If Not IsNull(Me.OpenArgs) Then
  Me.ID_Wells.Value = Me.OpenArgs
  TempVars.Add "lngID_Well", Me.ID_Wells.Value
Else
    If Not CurrentProject.AllForms("frmsr_NewWellEntry_cjc").IsLoaded Then
        lngID_Wells = InputBox("Enter PK ID for Wells", "Developer Popup form Testing option", "4969") ' no other input use Developers input
        Me.ID_Wells.Value = lngID_Wells
        TempVars.Add "lngID_Well", lngID_Wells ' create temp var of lngID_Well (singular) with value in varialbe
    Else
        strRegId = Forms![frmsr_NewWellEntry_cjc].txtRegWellID
        Me.ID_Wells.Value = strRegId
        TempVars.Add "lngID_Well", Me.ID_Wells.Value
    End If
lngID_Wells = Me.ID_Wells
' The Form's RecordSource uses [TempVars]![lngID_Well] as the filter in the ID_Wells column
' Open Attachment to view QBE Grid
During Form_Open in break mode the paste the following in the Debug Payne
? TempVars!lngId_well
or TempVars.Item(0)
 

Attachments

  • TempVars in QBE Query Grid.jpg
    TempVars in QBE Query Grid.jpg
    70.9 KB · Views: 506

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:55
Joined
Jan 20, 2009
Messages
12,849
They can be declared on demand without a Dim statement.
That is because TempVars is a Collection and you are simply adding an item to that collection.

Another bonus, each form can have up to 255 of them!
Incorrect. There is just one TempVars collection and it is at the Application level, not Form level.

As such you have not contained the scope of the elements in the procedure to the form at all. This application wide scope is actually a good reason not to use TempVars.

They also clean up and go away every time the form is closed.

No they don't. TempVars persist until the database is closed unless otherwise removed from the collection.

Also note that a TempVar item is a Variant (string or number only) so there is no control over the datatype. Personally I find they have so many down sides that I have never seen any real use for TempVars at all.
 
  • Like
Reactions: Rx_

Rx_

Nothing In Moderation
Local time
Today, 09:55
Joined
Oct 22, 2009
Messages
2,803
Thanks for that valuable correction! They are an Application level collection, not Form level. You are also very correct about the TempVar not having a data type.

But, I made one even larger, horrible mistake that Microsoft really doesn't document.

My objective was to create a Form with all of its objects within the Form itself. This objective was to not have Global variables required by the Form. In this way a Form could be copied to another Access application without the need to remember to include a required Global variable. The other objective was to allow the Developer to just open the Form directly, enter in a Primary Key ID (e.g. InputBox) and use the form as if it was opened by another process.

Setting the Primary Key ID during the Form Open event was a problem. The ListBox query filter (see QBE attachment) needs to use the Primary Key.

Since I kept one part of the Access application open, the TempVar fooled me into thinking it was avialable.
The reality of opening my applicaiton first thing this morning, then directly opening up the Form, using the Inputbox value failed!
The second time I opened the form, it worked.

Conclusion: Setting the TempVar as the first thing in a Form Open event will NOT work as the filter for my QBE List box in the form. Not the first time. It probably requires another Refresh / Repaint in the Form Load event.
The consistancy of using TempVar in the Form's Listbox is horrible.
My advice is to fall back to the tried and true method of setting the ListBox's RowSource Property with SQL Code incuding a filter in the Where clause.

Microsoft Office Developer states: it will remain in memory until you close the database. It is a good practice to remove TempVar object variables when you are finished using them.

Perhaps this could be said about creating any variable. Basically, it is using up memory.

In the latest edition of my The Hitchhiker's Guide to the Galaxy, it indicates the TempVar is "Mostly Useless".
 

HiTechCoach

Well-known member
Local time
Today, 10:55
Joined
Mar 6, 2006
Messages
4,357
Galaxiom is spot on on how TempVars really work and pointing out the incorrect assumptions made by Rx_.

TempVarre allow global variable for Macros! This is a must have for Web Apps since they do not allow VBA. You can set a TempVars item in a Marco and use it to filter a query.

The reason TempVars work in a query is due to their global (application level) scope.

Another issue I see in this solution:

I would never recommend to any professional level developer to use InputBox().

You should use a form that allows data validation. Since TempVars are global, you can use a popup form to collect the filter criteria ans store the value(s) in TempVars.
 
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom