Getting Table/Field value to use in file path

[timleonard]

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

    Dim strQryName As String, strXLFile As String, strXLFileName As String
 
    strDB = CurrentDb.Name
    strCurrentDir = Left(strDB, Len(strDB) - Len(Dir(strDB)))
    strXLFileName = ("SELECT [File Name] FROM [Paths] WHERE [Paths].[File Description]= *E-Room*")   
 
    strXLFile = "" & strCurrentDir & strXLFileName & ".xls"    strQryName = "Submit Road Rewards" 'Query Name
 
    Set xlApp = CreateObject("Excel.Application")
    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

[Stewart Ross]

@timleonard
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:

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.

-Stewart

[timleonard]

@Stewart Ross Inverness
Thanks for the suggestion, Works Great...
I did not think about doing it this way.