By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,955 Members | 817 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,955 IT Pros & Developers. It's quick & easy.

Getting Table/Field value to use in file path

P: 52
How do you reference the Table:Field to use in a file path? I have been trying the code below

I would like to reference a Tablel:Field to use in the path instead of hard coding the file name, as the name of the file will change between the branches that use it

Expand|Select|Wrap|Line Numbers
  1.     Dim strQryName As String, strXLFile As String, strXLFileName As String
  3.     strDB = CurrentDb.Name
  4.     strCurrentDir = Left(strDB, Len(strDB) - Len(Dir(strDB)))
  5.     strXLFileName = ("SELECT [File Name] FROM [Paths] WHERE [Paths].[File Description]= *E-Room*")   
  7.     strXLFile = "" & strCurrentDir & strXLFileName & ".xls"    strQryName = "Submit Road Rewards" 'Query Name
  9.     Set xlApp = CreateObject("Excel.Application")
  10.     Set wkb = xlApp.Workbooks.Open(strXLFile)
For now The Table name is "Paths"
The Field names are "File Name" and "File Description"

Thanks for any help you can give
Jun 15 '10 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 2.5K+
P: 2,545
Hi. Access provides what it calls Domain Aggregate functions whch can lookup the value of a field within a table, or its sum or average.

Although it is possible to open a recordset using an SQL statement and loop through the recordset to find a specific field value, it is much simpler to use domain aggregate function DLookup to retrieve your filename from table Paths.

Your assignment statement would be changed to:

Expand|Select|Wrap|Line Numbers
  1. strXLFileName = DLookup("[File Name]", "[Paths]", "[File Description]= '*E-Room*'")
DLookup's arguments are string expressions. The first argument is the name of the field you want to lookup, the second is the name of the table or query concerned, and the third (optional) argument is the Where clause being applied (but without the word Where). Note the use of single-colons on either side of the the string literal *E-Room* in the where clause, something you would also have needed in the SQL you provided had this been a viable solution.

Jun 15 '10 #2

P: 52
@Stewart Ross Inverness
Thanks for the suggestion, Works Great...
I did not think about doing it this way.
Jun 16 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.