I've been having trouble with this all day and I was hoping someone could help. Here is what I am trying to do:
1) The user selects a value from a combo box. This combo box has 2 columns. The first column is a username and the second
column is that user's initials. The only visible column is the username column.
2) Upon selecting a user from the combo box, the list box is updated to show a list of files written by that user. The
naming convention for all files in the system is user initials followed by a date (ie. AB141003.ext).
3) The user highlights a file or files in the list box that belong to the user selected in the combo box and then presses a
button to open them.
So far I have been able to get files to open by hard-coding in a value for the initials. The problem is that I can't seem to
get the initials to change when the combo box changes so that I can get a new list of files for another user.
Here is the code for what I have done so far. I've purposely left out the code to open files that are highlighted in the
list box to save space and b/c I know it works.
Option Compare Database
Dim username As String
Dim userabbrev As String
Private Sub combo_box_user_AfterUpdate()
username = Me.combo_box_user.Column(0)
userabbrev = Me.combo_box_user.Column(1)
Private Sub Form_Open(Cancel As Integer)
Dim file_search As Object
Dim i As Integer
'The following section is hard-coded b/c setting username and
'userabbrev = Me.combo_box_user.Column(x) causes an error
username = "Al Jones"
userabbrev = "AJ"
' Declare file search objects
Set file_search = Application.FileSearch
' Set the folder to search.
file_search.LookIn = "C:Documents"
' Set file name to search for.
file_search.FileName = userabbrev + "*.*"
' Execute the file search, and check to see if the file(s) are present.
If file_search.Execute > 0 Then
' Redimension the array to the number of files found.
' Loop through all found file names and fill the array.
For i = 1 To file_search.foundfiles.Count
file_array(i) = file_search.foundfiles(i)
' Loop through the array and fill the list box
If file_search.foundfiles.Count >= 1 Then
Me.List0.RowSource = file_array(1)
For i = 2 To file_search.foundfiles.Count
Me.List0.RowSource = Me.List0.RowSource & ";" & file_array(i)
Me.List0.RowSource = "Documents not found for user: " + username
What this all boils down to is filtering the filenames. This is probably something really easy to do, but I'm new to Access
and that is why I am having difficulty.
Thanks to anyone willing to help.