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

Question

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"));

What's with TOP 1 ? How many records have SettingName="Files Folder Location" ? — Fionnuala
– Fionnuala, Commented Sep 20, 2010 at 19:30
If you have only one record that matches the criteria, Hans's method is better. — KevenDenen
– KevenDenen, Commented Sep 21, 2010 at 0:38
Heh, ya, I didn't even think of that. I just copy/pasted his query. — KevenDenen
– KevenDenen, Commented Sep 21, 2010 at 2:40

HansUp · Accepted Answer · 2010-09-20 23:51:05Z

7

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""")

answered Sep 20, 2010 at 23:51

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David-W-Fenton Over a year ago

...and if you don't have but one record, then your settings table is designed wrong.

KevenDenen · Accepted Answer · 2010-09-20 19:20:45Z

3

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

answered Sep 20, 2010 at 19:20

KevenDenen

1,72213 silver badges26 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related