Solved

Microsoft Access Defining date parameter in a form

Posted on 2015-01-22
13
140 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
  • 4
  • 4
  • 2
  • +2
13 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 36

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 49

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 47

Expert Comment

by:Dale Fye (Access MVP)
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 36

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 36

Accepted Solution

by:
PatHartman earned 500 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 36

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

831 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