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:

y:\sidewalks\2006\swk_master.mdb

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):

y:\sidewalks\(DLookUp("[year]","[tblYear]","[Item]='year'"))\swk_master.mdb

Thanks,
SKK