Rx_
Nothing In Moderation
- Local time
- Today, 13:54
- 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.
During Form_Open in break mode the paste the following in the Debug Payne
? TempVars!lngId_well
or TempVars.Item(0)
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
? TempVars!lngId_well
or TempVars.Item(0)