Variable not passing

[kmclane]

I have a form that pulls directory and file info so users can open pdf files. I have a list box that gives the sub directories, month year data i.e. "aug03, dec04". Once a user clicks on a choice, they click on a combo box that should list the files. I had this working by hand adding each directory into a table by hand, but I am trying to automate it. I have successfully added code to read the directories:

Private Sub List18_Enter()
Dim myDir1 As String, Str1 As String
List18.RowSourceType = "Value List"
myDir1 = Dir(path & "*.*", vbDirectory)
Str1 = ""
Do While Not (myDir1 = "")
If myDir1 <> "." And myDir1 <> ".." Then
If (GetAttr(path & myDir1) And vbDirectory) = vbDirectory Then
Str1 = Str1 & myDir1 & ";"
End If
End If
myDir1 = Dir
Loop
List18.RowSource = Str1
End Sub

The problem I am having is that now the combobox doesn't update. this is the new code for it:

Private Sub Combo14_Enter()
Dim Myfile As String, StSql As String
Combo14.RowSourceType = "Value List"
Myfile = Dir(path & Me.List18.Value & "\*.pdf")
StSql = Myfile & ";"
Do While Not (Myfile = "")

Myfile = Dir

StSql = StSql & Myfile & ";"
Loop
Me.Combo14.RowSource = StSql

End Sub

I don't get any data now, and I do not understand why. Any suggestions would be appreciated.
Thanks, Ken.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[docmeizie]

Where is your SELECT statement for the sql located? I am not the greatest SQL coder but I don't see a SELECT statement to initiate an SQL query. Or are you trying to connect to a table? Need a little bit more clarity on what exactly your are trying to do and your coding....

If I take a peek in your Windows, to fix a problem, does that make me a &quot;Peeping Tom&quot;? Hmmmmmmmmmmv

 

[kmclane]

There is no SQL involved, all I'm doing is using the DIR function to get a list of filenames to populate a combobox. These are converted to hyperlinks in the afterupdate event of the combobox. I had all my subdir names in a table, but I had to add the new ones every month. Now I get a dynamic list of subdirs, but I am not getting anything in the file list.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[docmeizie]

Hmmm. I think you may have a better chance using a collection to get all your files in one place, then tie the combobox to that collection. Give me some time and I can show you what I am talking about with a code set.

If I take a peek in your Windows, to fix a problem, does that make me a &quot;Peeping Tom&quot;? Hmmmmmmmmmmv

 

[kmclane]

As far as I can tell, my problem is that the value is never getting passed from the listbox. I have checked in the debug window and it has a value of either 1 or 2, and it should be something else.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[kmclane]

never mind, I got it to work. Somehow I changed the bound column to 0, need to be 1. I must have done it when changing from a table to nothing. I tried to delete the reference to a column, it wouldn't let me and I put the wrong number back in.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg