combo box filling question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form and on it are various controls. One of the controls is a
combobox that needs to populate with word documents found in a specified
folder. I am stumped on how to make this work so I seek assistance from the
newsgroup. If anyone knows how to do this or knows of an example I can
review, that would be great.
Thanks.
.... John
 
In
JohnE said:
I have a form and on it are various controls. One of the controls is
a combobox that needs to populate with word documents found in a
specified folder. I am stumped on how to make this work so I seek
assistance from the newsgroup. If anyone knows how to do this or
knows of an example I can review, that would be great.
Click to expand...

I've done this using a custom list-filling function for the combo/list
box. Such a function must conform to a specific format, which is
documented in the help file (but unfortunately, it's very hard to find
the topic in all versions since Access 97). Here's an example of such a
function that gets the folder path and file pattern (e.g., "*.doc") from
controls on the form:

'------ start of code ------
Function FillFileList( _
fld As Control, _
ID As Variant, _
row As Variant, _
col As Variant, _
Code As Variant) _
As Variant

Static avarFiles(1000) As Variant
Static intEntries As Integer
Dim ReturnVal As Variant

ReturnVal = Null
Select Case Code
Case acLBInitialize ' Initialize.
intEntries = 0
avarFiles(intEntries) = _
Dir(Me.txtFolder & "\" & Nz(Me.txtPattern, "*.doc"))
Do Until avarFiles(intEntries) = vbNullString _
Or intEntries >= 1000
intEntries = intEntries + 1
avarFiles(intEntries) = Dir
Loop
If intEntries > 1 Then
QSArray avarFiles, LBound(avarFiles), intEntries - 1
End If
ReturnVal = intEntries
Case acLBOpen ' Open.
' Generate unique ID for control.
ReturnVal = Timer
Case acLBGetRowCount ' Get number of rows.
ReturnVal = intEntries
Case acLBGetColumnCount ' Get number of columns.
ReturnVal = 1
Case acLBGetColumnWidth ' Column width.
' -1 forces use of default width.
ReturnVal = -1
Case acLBGetValue ' Get data.
ReturnVal = avarFiles(row)
Case acLBEnd ' End.
Erase avarFiles
End Select

FillFileList = ReturnVal

End Function
'------ end of code ------

Note that this example maxes out at 1000 files.

To use this function, paste it into your form's module, and modify the
references to Me.txtFolder and Me.txtPattern in the code so as to pick
up the folder path and pattern from wherever you prefer. Then set the
combo box's RowSourceType property to

FillFileList

Leave the RowSource property blank.
 
Dirk,

This generates an error on mine:
'FillFileList' may not be a valid setting for the RowSourceType property, or
there was a compile error in the function.

You said paste it into the form's module. I hard-coded the folder path and
filter. Any idea where I went wrong?
 
In
Robert_L_Ross said:
Dirk,

This generates an error on mine:
'FillFileList' may not be a valid setting for the RowSourceType
property, or there was a compile error in the function.

You said paste it into the form's module. I hard-coded the folder
path and filter. Any idea where I went wrong?
Click to expand...

If you compile the project (Debug -> Compile), are any errors flagged?
 
Dirk,

You know, while I was staring blankly at the screen today, I realized I
could get the same thing done in code on the page itself instead of having a
module out there.

I used the form's timer event to load the combo box with valid files:

Private Sub Form_Timer()

Dim TheFolder As String
Dim FileFilterMinimum As String
Dim FileList As String
Dim ThisFile As String

TheFolder = "G:\FapsMigrationProd\FAPSLMR1"
FileFilterMinimum = Me.FILE 'set to "FAPSNDL200704010800.TXT" on the form


ThisFile = Dir(TheFolder & "\*.TXT")
Do While Len(ThisFile) > 0
If ThisFile > FileFilterMinimum Then
FileList = strQuote & FileList & ThisFile & ";" & strQuote
End If
ThisFile = Dir() 'get next filename
Loop

'get rid of last ;
FileList = Left(FileList, Len(FileList) - 1)

Me.FILECOMBO.RowSource = FileList
'reset the timer so it doesn't keep reloading ever 100 ms
Me.TimerInterval = 0

End Sub
 
Back
Top