• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 153
  • Last Modified:

Microsoft Access Defining date parameter in a form

I have a form in access in which I have several dates saved that are then used throughout the database.  However, I am having issues that from time to time when I run queries access will ask me to define the parameter instead of it reading the date defined in the form.  If I go into the form, change the name of the date field and then open the query in design mode and rerun, then it is fine.  However i want to solve the issue.
  • 4
  • 4
  • 2
  • +2
1 Solution
Dale FyeCommented:
I have generally stopped using form controls to store global variables, and have replaced that technique with using TempVars.  TempVars is a collection of variables which can be set in design view as well as via code.  They retain their values when your code stops running, and even when the code encounters a unhandled error.  To set a Tempvars value you can use syntax like:

TempVars.Add "SomeDate", #1/22/15#
Tempvars!SomeDate = #1/22/15#
Tempvars("SomeDate") = #1/22/15#

If you want to set the value of a TempVars variable from a forms control, you must use the .Value property of the controls or you will raise an error.
Tempvars!SomeDate = me.txt_SomeDate.Value
or perhaps
Tempvars!SomeDate = cdate(me.txt_SomeDate.Value)

Open in new window

To use Tempvars variable in a query, the syntax looks like: [Tempvars]![SomeDate]

However, I have found that occassionally, this syntax does not work, so I created a wrapper function that returns the value of the TempVars as a variant.
Public Function fnTempvars(VarName as string) as Variant

    fnTempvars = Tempvars(VarName)

End Function

Open in new window

Which will return the value of the TempVar or a NULL if that Tempvar Variable name has not been defined.  In your query, you would simply refer to:

WHERE [SomeDate] = fnTempvars("SomeDate")
Jeffrey CoachmanMIS LiasonCommented:
It is not clear how, (or if), you are storing these dates...
It is also not clear how you are referencing the dates in your query.

One common approach is to create a Public variable to store the dates.
Then use a Public Function to retrieve it (for use as a parameter in a query)

Another issue may be that you are running the queries before the date textbox "Updates"
Hence the date may not be available to the query.

Jeffrey CoachmanMIS LiasonCommented:
You can ignore my post above, ..Dale has this all covered.
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

krfunk76Author Commented:
Thanks Dale.  I need some help though as I am a bit of a access novice.  Outside of the GUI controls, I am outside my comfort zone.  I have the below now:

The benefit that I get with this is I have a default value that will be used in my queries.  It also allows me to quickly change the value using the form to run different scenarios.

If I understand you correctly, you are saying that I can maintain this however, I should reference the form value through code and then reference the code in my queries?

Taking the form variables which already exist and which you change manually and copying them to tempvars is overkill.  There is nothing wrong with referencing the form directly.  I think you may be running into trouble when you are relying on a default.  Can you verify that?

I use defaults all the time for form supplied date parms but I don't use the default property to supply them, I use code in the form's Current event.  So, you could try moving the expression from the default property and adding it as a line of code in the Current event.

Me.date1 = someexpression
Me.date2 = someexpression
Gustav BrockCIOCommented:
Your DefaultValue expression can be simplified to:


Your problem is that the field is unbound so the query may not know what to expect.
Go to your query in design mode and open Parameters and insert:

    [Forms]![frm_Control]![DT_SETTLE] and specify Date for data type.

Also make sure, it cannot be Null.

krfunk76Author Commented:
I run into problems when I link to the Access database through excel and return results using the "get external data" set-up in excel.  At least there seems to be a connection to the issues I face when the param stops working and accessing it through excel.  

I did try to add the parameter info to the query in design mode but that did not seem to help.

I appreciate the simplified expression, I didn't realize that by subtracting from the month that the function would adjust the year also.

I will try to add the expression as an event instead of the default.
Dale FyeCommented:
Going back to my suggestion to use TempVars.

Pat may consider it overkill, but when you want to test a query in design view, and don't have the main parameters form open, it can be a pain in the butt.  Additionally, whenever you refer to a forms control in a query, you should parameterize the query, and if you want to execute an action query, you have to define the querydef and either set the value of the parameters or Eval( ) them, before you run:


When you use tempvars, you don't have to do that, as fnTempVars() can be used in the query without having to redefine it.

I would add a line of code to the AfterUpdate event of each of the controls on that form; something like:

Private Sub txt_DT_Settle_AfterUpdate

    Tempvars!DT_Settle = me.txt_DT_Settle.Value

End Sub

Open in new window

Then, in the Form Load event, I would set the value of those fields, similar to the method Pat mentioned above, and would then call the AfterUpdate event of each control.
I think TempVars have a place.  Just not this one.

If the dates are entered into the form then, you need the form open so trying to run the queries when the form isn't open won't happen except perhaps during testing and that would be a programmer error.  The programmer would hit his head with his hand, say a few curse words, and open the form.  This is exactly why I have a dent in the middle of my forehead.  If you want to use the dates for many reports run from various places once the dates are entered, it is reasonable to make them TempVars but for one-time use when the form has to be open anyway, I wouldn't use TempVars.  That is simply an extra step of abstraction that isn't necessary.

If you do elect to use TempVars for this situation, you have to deal with the possibility that no one ever opened the form to set the value of the TempVar.

I don't think you can reference queries that require parameters from Excel.  You might have to push the export from Access to Excel so that the query runs when Access (and the form) are open.  If you need to run the query from Excel, I think you need to create it in Excel so it will prompt you there for the parameter.
krfunk76Author Commented:
To be clear, i am not trying to refer to parameters that are in excel to run the queries, i have simply linked to tables in the db to return values in excel for analysis purposes.

I am open to using Pat's suggestion of not using the "default value", however as I said I have never used code before in Access.  So Pat I see how I can go into the "On Current" to put the suggested code in Me.date1 = someexpression, therefore defining the expression here.  This may seem very basic, but where in the form do i reference "Me.date1" so that will be the default value?  I would assume that has to go into an unbound text box?  Then when i open the form it will populate with the date based upon the expression in the code, and allow me to change it?

I appreciate the help.
In design view, look at the Name property of each control.  If you didn't specificly name them, they will be some generated name like text48.  You should change the name of each date to be something meaningful.  You don't want to write code referring to text08.  Who can read that and know what it means.  I prefix my textbox controls with "txt" so on my form I might have txtStartDate and txtEndDate

After you change the names of all the relevant controls, then go into the Current event and set the values.  You would cut them from where they are now and paste them into the code.

Me.txtStartDate = pasted from default property

Notice as you are typing that you get intellisense after the Me.  -  that helps you to avoid typos.

It sounded like you were trying to run these parameter driven queries FROM Excel.  Are you trying to do that?
krfunk76Author Commented:
In excel I simply have connections to the database to retrieve data that has been saved out to tables from the series of queries that run analysis.  

I want to use VBA code to pull the information from access but I haven't figured that out yet so I am using the "get external data" process within excel.  However, there are some issues with it because it creates a live connection and so if I am in both at the same time, it causes some issues.  

Thank you for the instructions on how to setup the dates in the current region, it works as described and it seems to have solved the issue that I was facing previously.
This question seems to have morphed from a question about using a form to supply dates to a report to something to do with Excel.  Please start a new question with a new topic.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 4
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now