Solved

Microsoft Access Defining date parameter in a form

Posted on 2015-01-22
13
133 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
 

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 34

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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 34

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 34

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 34

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now