07-02-2001, 04:52 PM
When a user opens a form in Access, I want to populate a combo box with the names of document files that reside on our file server. Then I want to allow the user to pick a filename to add to a table.
How do I do this?
07-02-2001, 05:55 PM
There is a way you can do it using the Dir$ function, which can be used to iterate the files in a given folder/directory. You could use some code like this when your form loads (using a combo box named cboFilenames) to populate your combo (make sure the 'Row Source Type' property of the combo is set to 'Value List'):
<PRE>Private Sub Form_Load()
Dim strFilename As String
Dim strFilenames As String
' Change folder path below as desired:
strFilenames = Dir$("c:\*.*")
strFilename = Dir
strFilenames = strFilenames & ";" & strFilename
Loop Until strFilename = ""
cboFilenames.RowSource = strFilenames
cboFilenames.Value = Mid$(strFilenames, 1, InStr(strFilenames, ";") - 1)
End Sub</PRE>One way to add the selected filename to your table with an SQL INSERT INTO statement like this:
<PRE>Private Sub cmdAdd_Click()
' This step is optional - it checks to see if the filename is already in the Filenames table and
' ...exits the routine if it's already there (this prevents duplicates).
If DCount("Filename", "Filenames", "[Filename]='" & cboFilenames.Value & "'") > 0 Then
' Execute a SQL INSERT INTO statement to add the selected value to the Filenames table.
CurrentDb.Execute "INSERT INTO Filenames (Filename) VALUES ('" & cboFilenames.Value & "')"
End Sub</PRE>In this example there's a simple table called Filenames that has an autoincrement ID field and one other field called Filename that holds selected filenames.
An example of this working is attatched if you want to check it out.
For more information and examples look up Dir$ in the MS Access help file.