?
Solved

Microsoft Access Defining date parameter in a form

Posted on 2015-01-22
13
Medium Priority
?
145 Views
Last Modified: 2015-01-23
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.
0
Comment
Question by:krfunk76
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +2
13 Comments
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40564221
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")
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40564226
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.

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40564230
You can ignore my post above, ..Dale has this all covered.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:krfunk76
ID: 40564363
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:

2015-01-22-ACCESS-QUESTION.png
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?

Thanks
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40564864
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
....
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40564900
Your DefaultValue expression can be simplified to:

=DateSerial(Year(Date()),Month(Date())-1,1)

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.

/gustav
0
 

Author Comment

by:krfunk76
ID: 40565047
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.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40565089
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:

qdf.Execute

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.
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40565304
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.

Also,
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.
0
 

Author Comment

by:krfunk76
ID: 40565405
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.
0
 
LVL 38

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 40565525
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?
0
 

Author Comment

by:krfunk76
ID: 40565646
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.
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40567870
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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question