Assign the value or a specific field and row in a table to a variable 2

[sterlecki]

I have a lookup table that stores file paths of various excel spreadsheets called on from my Access application.

To prevent alot of recoding everytime someone moves these excel files or changes their name I would like to set the variable:

strWorkBookFileAddress = (some way to reference the table, field,recordnumber).value

(ie. if it were excel it would be a specific cell ("b3"))

and use this in my application code. Then I would only have to maintain the lookup table with changes.

I haven't seen a way to reference this.

Thanks for any suggestions.

 

[Ed2020]

Something along the lines of this should work:

strWorkBookFileAddress = DLookup("NameOfTable","NameOfField","RecordID=5")

Bear in mind that DLookup can be slow on large tables. If you find it too slow you could write a routine to open the table as a recordset and access the value that way...

Ed Metcalfe.

Please do not feed the trolls.....

 

[TheAceMan1]

How are ya sterlecki . . .

To save yourself a headache, you shuold choose static paths/filenames accessible by or can be set on all machines. Allowing users to change paths/filenames is not a good idea!

Now . . . other than an error, there's no way for access to know a path and/or filename has changed. So a bottom line way of installing changes has to be made. Consider the following:

[ol][li]Use the [blue]Dir[/blue] function to tell if the [blue]selected[/blue] path & filename exist.[/li]
[li]If it doesn't, use the API [blue]GetFileNameFromBrowse[/blue] thread702-1289131 (see my post), to get the new path & filename and update the LookUp Table[/li]
[li]Continue with your code.[/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[sterlecki]

Thank you both Ed and TheAceman1. Those are both valuable suggestions. I do think checking for the file path would be a great idea.

I will try both suggestions out when I'm bak at the office.

I think these posts answers my questions.

Thanks again and a star for each of you.