I have a macro that imports a table from a network drive. I have hard coded in the path. However, each year file location changes. Is there a way in a Macro to adjust the code based on some other parameter?
Under the "TransferDatabase" action, for the "Database Name", I hard coded:
However, I want the 2006 to automatically come from some other location. Here’s what I tried to no avail (where I wanted the dlookup to return 2006):
You're asking dlookup to return the field [year] where the field [Item] contains the literal string 'year'? What does that backend look like exactly? Will your year parameter be based on todays date in some way? Maybe you could use intrinsic date functions?
There is a table called tblYEAR that only has one field in it called YEAR. This field is used throught many form, and report titles. The YEAR field currently has 2005 in it but will become 2006 for next years data. The 2005 database will be copied into a 2006 folder (all data will be cleared, and we'll start over for next year's construction).
If possible, I wanted to have the path in the macro for the table transfer to come over automatically based on what's in the YEAR field of the tblYEAR table.
Please, PLEASE reconsider how you're overall concept for tracking years.
Very, very seldom does it make any sense to start with a completely new database for a given time period. Real world example, the primary contract I'm working now is consolidating databases that where created fresh for each quarter. Now they need to develop trend statistics for the last 4 years... how do you do it? All the data is in different databases entirely...
You can pull the current year by using datepart("y", now()).
I don't know what the overall design looks like for you db, but I'll tell you from experience that a large portion of my consulting contracts are fixing problems that are the result of this type of logic...