Updating list boxes via combo boxes (1 Viewer)

[JC10001]

Hi there.

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

Me.combo_box_user.SetFocus

username = Me.combo_box_user.Column(0)
userabbrev = Me.combo_box_user.Column(1)

End Sub



Private Sub Form_Open(Cancel As Integer)

Dim file_array()
Dim file_search As Object
Dim i As Integer

Me.combo_box_user.SetFocus

'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.
ReDim file_array(file_search.foundfiles.Count)

' 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)
Next i
End If

' 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)
Next i
Else
Me.List0.RowSource = "Documents not found for user: " + username
End If

End Sub


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.

 

[dcx693]

The code to open the files is in the Form's open event, which only runs when the form itself is opened. You need to attach it to the click event of the command button you are using.

These lines:
username = Me.combo_box_user.Column(0)
userabbrev = Me.combo_box_user.Column(1)
should also be in the code attached to the command button.

Why do you need this line of code:
Me.combo_box_user.SetFocus

 

[JC10001]

*Slaps hand on forehead*

I moved the code from the Form_Open event to the combo_box_Change event and everything works fine now. Thanks a lot for your help.