Access tables in backend from VBA

[uaguy3005]

I have a form with a command button that opens an excel file in a specified location and populates some of the fields based on information collected in the form. I currently have the file location hard coded in the VBA and would like to change this so that the file location is stored in a table on the back end of the database and allow administrators change the file location as required. The code currently looks like the below



Private Sub CreateQA_Click()
' Creates a QA and saves it to the network.


Dim objXLApp As Object
Dim objXLBook As Object
Dim strQAName As String
Dim strQAFileLoc As String
Dim strQAFile As String
Dim ctl As Control


Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("S:\QUALITY\QUALITY ALERTS\Quality_Alert.xlsx")
strQAName = Me.ID & " - " & Me.Line & ".xlsx"
strQAFileLoc = "S:\QUALITY\QUALITY ALERTS\Alerts\"


strQAFile = strQAFileLoc + strQAName

I'm storing the file location in a table called tblMaintenance under a field called QALocation. What do I need to replace the Red text with to pull from that table the file location?

Thanks for the help.

[pbaldy]

One option would be a DLookup() function.

[ssanfu]

Just an FYI.....

"Line" (as in Me.Line) is a reserved word in Access and shouldn't be used as an object name.
For a list of reserved words, see http://allenbrowne.com/AppIssueBadWord.html
Also, I am not crazy about "ID" as a name either. (not very descriptive)

Using special characters and spaces (except the underscore) is not a good practice. It will/can cause you headaches.
See http://access.mvps.org/access/tencommandments.htm