Know Moore About Access

I knew that Access provided an in-session variable array called TempVars and I have used them a few times but never had much need for them.  However, a project that I have been working on this past couple of weeks required some creativity.  TempVars were just the ticket.  You can use TempVars through either macros or VBA code.  Up to this point, I had only worked with TempVars through the macros.  I had never attempted to manipulate them through VBA code.

The Goal:

I had created a datasheet form but was using simple parameter value prompts from the underlying query to obtain user input.  I wanted to embed those parameter prompts into the datasheet form to clean up the process and make it more of a single-step approach on the user interface.  (There are 3 parameter values being obtained in this particular form.)

The Solution:

I created un-bound text boxes at the top of the datasheet form and then added a command button to the form.  In the VBA code of the command button, I added the code to establish the temporary variables and set them to the values provided in the text boxes.  I also added some code to check the values of the text boxes to make sure that they were not empty when the Update Display button was pressed.  The three text boxes that I created are txtProdCode, txtBegDate and txtEndDate.  The three temp variables that I am establishing are ProdCode, BegDate and EndDate.  Here is a code sample of how those variables are established and the values are set:

Private Sub cmdUpdate_Click()
On Error GoTo cmdUpdate_Click_Err

 On Error Resume Next
 lblErrMsg.Visible = False

 If IsNull(Me.cboProdCode) Then
     Beep
     sMsg = "Please Select a Product Code before clicking Update."
     lblErrMsg.Caption = sMsg
     lblErrMsg.Visible = True
     GoTo cmdUpdate_Click_Exit
 Else
     lblErrMsg.Visible = False
 End If

 If IsNull(txtStartDate) Then
     Beep
     sMsg = "Please Enter a Start Date before clicking Update."
     lblErrMsg.Caption = sMsg
     lblErrMsg.Visible = True
     GoTo cmdUpdate_Click_Exit
 Else
     lblErrMsg.Visible = False
 End If

 If IsNull(txtEndDate) Then
     Beep
     sMsg = "Please Enter an End Date before clicking Update."
     lblErrMsg.Caption = sMsg
     lblErrMsg.Visible = True
     GoTo cmdUpdate_Click_Exit
 Else
     lblErrMsg.Visible = False
 End If

 TempVars.Add "ProdCode", Me.cboProdCode.Value
 TempVars.Add "StartDate", Me.txtStartDate.Value
 TempVars.Add "EndDate", Me.txtEndDate.Value
 DoCmd.Requery

cmdUpdate_Click_Exit:
     Exit Sub

cmdUpdate_Click_Err:
     MsgBox Error$
     Resume cmdUpdate_Click_Exit

End Sub

Here is a screen clipping of what the form looks like:

Example datasheet for with three un-bound fields set to temp variables through VBA code.