FileSystemObject

[kit_sune]

I have run into trouble with an Access database. I didn't build this database I am merely trying to get it running for our office because we really think it could benefit us.

I created a shorter version of the VBA code that is giving me trouble so I can troubleshoot it. What it is trying to do is create a recordset with 1 record, then based off a value in that one record, check to see if a folder exists.

[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Private Sub Command91_Click()
Dim rst As Recordset, FS
Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM [Settings] WHERE [SettingsProfile] = """ & Forms![Switchboard]![ProfileNameSelect] & """")
Set FS = CreateObject("Scripting.FileSystemObject")
        If FS.FolderExists(rst!ArchiveLocation) = False Then
            MsgBox "The path specified in your Preferences profile does not exist anywhere on your system. Please correct this in your Preferences profile and try again.", vbCritical + vbOKOnly, "Missing File"
        Else
        MsgBox "Success!"
        End If
End Sub[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]

The above code give me the “Missing File” end result
However when I copy the value directly from the table and insert it in the code in place of the variable - If FS.FolderExists( “F:/location”) I get the success scenario.
Lastly, the value in the table is set to a text field, so that should mean it is a string. Can anyone explain what the issue is? This apparently worked at one point..
Thanks for your extra set of eyes,

~Kit

 

[MarkK]

This code is OK. If you get an unexpected result you need to check the file system of the machine, and the value returned by the recordset, and we can't know that info from your post.

 

[kit_sune]

You’re right. I modified the code to return the value in a message box. The value is exactly what I need it to be. Also, the database resides in the parent folder. I guess I'm going to have to keep playing around with it for a while. The end result is to be able to use this location to archive data to.

[FONT=Calibri][SIZE=3]Private Sub Command91_Click()[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim rst As Recordset, FS[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM [Settings] WHERE [SettingsProfile] = """ & Forms![ Switchboard]![ProfileNameSelect] & """")[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]MsgBox (rst! ArchiveLocation)[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]End Sub[/SIZE][/FONT]