Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Closed Thread
 
Thread Tools Rating: Thread Rating: 4 votes, 5.00 average. Display Modes
Old 11-11-2015, 02:38 PM   #1
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,800
Thanks: 636
Thanked 337 Times in 307 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Lightbulb Form_Open use variable for filter in Form's RecordSource using QBE grid Design View

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)
Attached Images
File Type: jpg TempVars in QBE Query Grid.jpg (70.9 KB, 270 views)

Rx_ is offline  
The Following User Says Thank You to Rx_ For This Useful Post:
hassanogaibi (06-28-2016)
Old 11-11-2015, 06:59 PM   #2
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,573
Thanks: 88
Thanked 1,486 Times in 1,402 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Form_Open use variable for filter in Form's RecordSource using QBE grid Design Vi

Quote:
Originally Posted by Rx_ View Post
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.

Quote:
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.

Quote:
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.
Galaxiom is offline  
The Following 2 Users Say Thank You to Galaxiom For This Useful Post:
hassanogaibi (06-28-2016), Rx_ (11-12-2015)
Old 11-12-2015, 09:12 AM   #3
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,800
Thanks: 636
Thanked 337 Times in 307 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Thumbs up Re: Form_Open use variable for filter in Form's RecordSource using QBE grid Design Vi

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

__________________
Were you lucky enough to get an answer? Please mark your question as [SOLVED] The original poster can go to Thread Tools to mark it as Solved.

Quotation Thomas Jefferson: "Peace is that brief glorious moment in history when everybody stands around reloading."

There are 2 Kinds of Countries on this Planet
1. Those that use the Metric System
2. Those that had a man walk on the moon

Denver, Colorado - The "Mile High City" - non-metric!
Rx_ is offline  
Old 11-12-2015, 11:30 AM   #4
HiTechCoach
Newly Registered User
 
HiTechCoach's Avatar
 
Join Date: Mar 2006
Location: Oklahoma City, OK
Posts: 4,254
Thanks: 8
Thanked 156 Times in 144 Posts
HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough
Re: Form_Open use variable for filter in Form's RecordSource using QBE grid Design Vi

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.

__________________
Boyd Trimmell
Specializing in Accounting, CRM, and Business solutions
Microsoft Access MVP 2010-2015 , aka
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
HiTechCoach is offline  
Closed Thread

Tags
filter criteria , form_open , qbe , record source , tempvars

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Unable to view object properties in form design view (Access 2010) Groundrush Forms 3 05-16-2014 05:54 AM
In access 2007 convert from form view to design view fllopez65 Introduce Yourself 5 09-22-2011 07:39 PM
How to keep filter functions, but disable design view function? selvsagt Forms 0 08-15-2006 06:56 AM
Open Query design grid from a form. sehmke Modules & VBA 1 09-03-2004 02:07 AM
Form Grid in Design View? AccessKurzo General 0 05-03-2004 10:22 AM




All times are GMT -8. The time now is 12:17 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World