Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select a value from a table in current DB and use a variable in Access VBA

Tags:

vba

ms-access

In Access VBA, I want to use values from a "Settings" table, instead of hard-coding folder locations etc. in the code. I can't figure out how to load a value from the table and use it in the code.

Dim oFSystem As Object
Dim oFolder As Object
Dim oFile As Object
Dim sFolderPath As String



sFolderPath = "C:\Documents and Settings\Main\Desktop\Files" 'BAD BAD, I WANT TO AVOID THIS

I have created a table "Settings", and I want to use the value

SELECT TOP 1 Settings.SettingsValue FROM Settings WHERE (((Settings.SettingName)="Files Folder Location"));
like image 625
MaxTag Avatar asked Dec 28 '22 07:12

MaxTag


2 Answers

You could use the DLookup function if you have only one record where SettingName="Files Folder Location".

sFolderPath = DLookup("SettingsValue", "Settings", "SettingName=""Files Folder Location""")
like image 128
HansUp Avatar answered Jan 11 '23 08:01

HansUp


One way:

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim sFolderPath As String

Set db = CurrentDb()
Set rs1 = db.OpenRecordset("SELECT TOP 1 SettingsValue FROM Settings WHERE SettingName="Files Folder Location")

If rs1.RecordCount > 0 Then
    rs1.MoveFirst
    sFolderPath = rs1.Fields("SettingsValue")
End If
rs1.Close
set rs1 = Nothing
set db = Nothing
like image 20
KevenDenen Avatar answered Jan 11 '23 08:01

KevenDenen