RE: Using a function to get values into a combobox
Rod Plastow
"dhstein" wrote:
> I read a post that had a method for reading a directory of files into a
> combobox. The post indicates that you can use a function as the RowSource
> Type for the combobox control. This isn't working for me. The options for
> RowSource Type seem to be Query/Table, Value List or Field List. Am I
> reading this post wrong? Is there a way to get a function to propagate
> values in a combobox? Thanks for any help on this.
No, you're not reading it wrong. What I suggest is that you create a
temporary table - if your list of files is long - or a string variable of
file names separated by semicolons (;) if your list is a more manageable
length.
In the first case your Record Source is the table and your Record Source
Type is Query/Table.
In the second case your Record Source is the string and your Record Source
Type is Value List.
The more difficult operation is to retrieve the list of file names. I
assume you want to get them from a directory. The Scripting class has
methods to do this (Microsoft Scripting Runtime), otherwise you have to
resort to API calls.
Rod
dhstein
Thanks for your replies. I'll post the code below. But I get nothing in
the combo box.
'Use the function below as the Row Source Type for the combo box (just enter
'ListFiles in the Row Source Type of the combo box on the form) It will call
'this function to populate the combobox. Substitute your directory for the
'strGlobalPath variable:
Public Function ListFiles(F As Control, ID, Row, Col, Action)
Dim strGlobalPath As String
Dim strExtension As String
' Set strGlobalPath to your Dir
' Set strExtension to whatever filetype your looking for
strGlobalPath = "C:\" ' Use your directory here
strExtension = "*.txt"
Static MDBS(), FileCount
Dim FileName
Select Case Action
Case acLBInitialize
FileCount = 0
FileName = Dir(strGlobalPath & strExtension)
Do Until FileName = ""
FileCount = FileCount + 1
FileName = Dir
Loop
ReDim MDBS(FileCount)
FileCount = 0
MDBS(FileCount) = Dir(strGlobalPath & strExtension)
Do Until MDBS(FileCount) = ""
FileCount = FileCount + 1
MDBS(FileCount) = Dir
Loop
ListDatabaseFiles = FileCount
Case acLBOpen
ListDatabaseFiles = Timer
Case acLBGetRowCount
ListDatabaseFiles = FileCount
Case acLBGetColumnCount
ListDatabaseFiles = 1
Case acLBGetColumnWidth
ListDatabaseFiles = -1
Case acLBGetValue
ListDatabaseFiles = MDBS(Row)
Case acLBEnd
Erase MDBS
End Select
End Function
"Jack Cannon" wrote:
> Here is some information on using a function.
>
> http://msdn.microsoft.com/en-us/library/bb242937.aspx
> http://msdn.microsoft.com/en-us/library/bb240104.aspx
>
> You can load the individual items independently (RowSourceType, RowSource,
> etc.). I also use the Tag to conveniently keep track of what is loaded in
> the ComboBox.
>
> Jack Cannon
Rod Plastow
I'm not surprised you are having difficulty. :-)
The code example you quote has some immediate and obvious inconsistencies.
1. The function is called 'ListFiles' but is never referenced in the code.
Something called 'ListDatabaseFiles' is referenced and assigned various
return values. These names should be identical.
2. The function is obviously designed to be called repetitively with
different values in the argument 'Action.' The Select Case tests for
enumerated constants in the Access library. The first call should obviously
be using acLBInitialize. However the code is somewhat 'screwy' in that it
assumes values will be the same (static) between calls. Not so in my
understanding. To preserve the value of procedure level variables between
calls use the keyword 'Static' in the procedure declaration such as:
Public Static Function ...
3. Another way of preserving a variable value between calls is to declare
the variable at the module level (as opposed to the procedure level).
4. A call made with acLBOpen returns the number of seconds since midnight.
Huh? What use is that?
5. The procedure arguments F, ID and Col are never referenced. What is
their purpose? They are not even made optional.
6. OK, I'm a little harsh in the preceding point because obviously F is the
combo box. If it is intended that this procedure manipulates the Row Source
of the combo box then there is a lot of code missing. Further if this is the
case, why bother returning the Column Count and Column Width in separate
calls.
Rod
dhstein
Here's the version I'm using for testing. There's lots of hard coded
values here - I'm trying to get to the basics here and then I can make it
work correctly. I'm off to sleep now, but thanks for your help. If you do
try it - the message boxes throw things off and change the behavior.
'Use the function below as the Row Source Type for the combo box (just enter
'ListFiles in the Row Source Type of the combo box on the form) It will call
'this function to populate the combobox. Substitute your directory for the
'strGlobalPath variable:
Function ListFiles(F As Control, id, row, col, Action)
Dim strGlobalPath As String
Dim strExtension As String
Dim IntOffset As Integer
' Set strGlobalPath to your Dir
' Set strExtension to whatever filetype your looking for
'strExtension = "\*.xlsx"
strGlobalPath = "C:\" ' Use your directory here
strExtension = "*.qpx"
'MsgBox "got here"
Static MDBS(), FileCount
Dim FileName
'Dim MDBS(15) As String
'Dim FileCount As Integer
Dim XX(20) As String
FileCount = 0
'XX(FileCount) = Dir(strGlobalPath & strExtension)
Do Until FileCount > 4
'MDBS(FileCount) = Dir$(strGlobalPath & strExtension)
XX(FileCount) = Dir(strGlobalPath & strExtension)
'MsgBox MDBS(FileCount) & "1"
'Do Until MDBS(FileCount) = ""
'FileCount = FileCount + 1
'MDBS(FileCount) = Dir$
FileCount = FileCount + 1
XX(FileCount) = Dir
'MsgBox XX(FileCount)
'XX(FileCount + 1) = (FileCount + 1) * 5
Loop
'MsgBox XX(2)
'MDBS(1) = "First File"
'MDBS(2) = "Second File"
'MDBS(3) = "Third File"
'MDBS(4) = "Fourth File"
'FileCount = 0
'XX(FileCount + 1) = (FileCount + 1) * 5
'Do Until FileCount > 4
'FileCount = FileCount + 1
'FileName = Dir$
'Loop
'MsgBox FileCount
'ReDim MDBS(FileCount)
'FileCount = 0
'MDBS(FileCount) = Dir$(strGlobalPath & strExtension)
'MsgBox MDBS(FileCount) & "1"
'Do Until MDBS(FileCount) = ""
'FileCount = FileCount + 1
'MDBS(FileCount) = Dir$
'MsgBox MDBS(FileCount) & "2"
'Loop
'Select Case Action
'Case acLBInitialize
'FileCount = 0
'MsgBox strGlobalPath & strExtension
'FileName = Dir$(strGlobalPath & strExtension)
'MsgBox FileName
ReDim MDBS(4)
Select Case Action
Case acLBInitialize
'MsgBox "Got to Initialize"
ListFiles = True
'MsgBox "Got to Initialize"
Case acLBOpen
'MsgBox "Got to Open"
ListFiles = Timer
Case acLBGetRowCount
'MsgBox "Got to RowCount"
ListFiles = 7
Case acLBGetColumnCount
'MsgBox "Got to Column Count"
ListFiles = 4
Case acLBGetColumnWidth
ListFiles = -1
Case acLBGetValue
'MDBS(1) = XX(2)
'MDBS(2) = "Second File"
'MDBS(3) = "Third File"
'MDBS(4) = "Fourth File"
FileCount = 0
Do Until FileCount > 4
MDBS(FileCount) = XX(FileCount)
FileCount = FileCount + 1
Loop
'FileCount = 0
'MDBS(FileCount) = Dir$(strGlobalPath & strExtension)
'MsgBox MDBS(FileCount) & "1"
'Do Until FileCount > 4
'MDBS(FileCount) = Dir$
'MsgBox MDBS(FileCount) & "2"
'FileCount = FileCount + 1
'Loop
IntOffset = Abs((9 - Weekday(Now)) Mod 7)
ListFiles = Format(Now() + _
IntOffset + 7 * row, "mmmm d") & " AAAA " & MDBS(row) & " BBBB "
& XX(row)
'ListFiles = 2
'MsgBox "Got here in the acLBGetValue " & row
'Loop
Case acLBEnd
'Erase MDBS
End Select
End Function
"Rod Plastow" wrote:
> Ignore 2 above - I had one of those 'Duh' moments. Of course Static
> preserves the variable value while the code is running; that is while the
> module is running.
dhstein
Thanks for your response. This is code that I found in a post and I'm
going through it and finding some of the same problems you mention. But the
code is actually pretty good (once I've made some adjustments) The problem
I'm experiencing now is that the "DIR" function is supposed to return the
next matching file when you invoke it without parameters - and that's not
working. So right now I get a list box populated with the same file multiple
times. But I'll check out your "static' advice - and keep working on it and
I think I'll be able to figure it out at some point.
Rod Plastow
xlsx extension that hints at Office 2007). I have just tested repeated Dir
calls without arguments in the same environment and it works for me; I get
successive files returned as described in the documentation. I can’t think
why you are getting multiple occurrences of the first file name. Remember to
enumerate from 1 (0?) to filecount each time you call the procedure.
I haven’t reviewed your later code with all the MsgBox statements. BTW
learn how to debug within the VBA coding window; it’s more flexible and you
don’t have to remember to delete or comment-out all those MsgBox statements.
What I don’t particularly like about the original procedure is the fact that
some of the file name pattern is hard coded. Surely to be more flexible the
calling routine should pass the file name pattern to the procedure. I also
don’t like the fact that it creates and redimensions the array during
initialization. What if there are many thousands of matching files?
My other criticisms fall into the category of personal preferences and
style. I wouldn’t have one general purpose procedure catering for all
actions; rather I would have a module with many procedures, each devoted to a
specific action. Suppose the module was called ‘RetrieveFileNames’ and it
contained procedures named ‘Initialize,’ ‘Count,’ ‘Next,’ etc. The calling
syntax is easier to read: ‘RetrieveFileNames.Initialize(),’
‘RetrieveFileNames.Count(),’ ‘RetrieveFileNames.Next(),’ etc. Moreover you
can be more precise about data typing with separate procedures.
I don’t like the use of arrays in this context; I would tend to use a VBA
collection or, if the number of files is small, return a string of all file
names suitably denominated for the combo box. Using redimensioned arrays you
need to scan the directory twice. With collections or a formatted string you
only need to scan the directory once.
I would not attempt to pass an Access Control object to my procedure. Well
yes I would if I wrote an interface Class Module and coded implementation
Class Modules for every likely control. In this case they are combo box and
list box and the resultant code is probably the same – but it’s the principle
of the thing!
OK, that’s enough pontificating. What I will do here is to make necessary
changes (what I think are necessary changes) to the original procedure and
then give some sample calling procedures. Beware I have not tested this!
Public Function ListFiles(lngAction As Long, _
Optional strFileNameTemplate As Variant, _
Optional lngRow As Variant) As Variant
Static strDirectoryPattern As String
Static strFileName() As String
Static lngFileCount As Long
Dim strTempFileName As String
Dim i As Long
Select Case lngAction
Case acLBInitialize
If IsMissing(strFileNameTemplate) Then
'insert error handling here - missing string
ListFiles = 0
Exit Function
End If
strDirectoryPattern = Nz(CStr(strFileNameTemplate), " ")
lngFileCount = 0
strTempFileName = Dir(strDirectoryPattern)
If strTempFileName = "" Then Exit Function
Do Until strTempFileName = ""
lngFileCount = lngFileCount + 1
strTempFileName = Dir()
Loop
ReDim strFileName(lngFileCount)
strFileName(0) = Dir(strDirectoryPattern)
For i = 1 To lngFileCount
strFileName(i) = Dir()
'could test for "" here - never happens :-)
Next
ListFiles = lngFileCount
Case acLBGetRowCount
ListFiles = lngFileCount
Case acLBGetValue
If lngRow Is missing Then
'insert error handling
ListFiles = ""
Exit Function
End If
ListFiles = strFileName(Nz(CLng(lngRow), 0))
Case acLBEnd
Erase strFileName
End Select
End Function
Here are some sample calls.
lngFileCount = CLng(ListFiles(acLBInitialize,
“C:/Users/Snoopy/Documents/*.xlsx”))
lngFileCount = CLng(ListFiles(acLBGetRowCount))
varDummy = ListFiles(acLBEnd)
strRowSource = “”
For lngCounter = 0 to CLng(ListFiles(acLBGetRowCount))
strRowSource = strRowSource & CStr(ListFiles(acLBGetValue,,lngCounter)) &
“;”
next
Hope you had a good sleep.
Rod
dhstein
Thanks for your efforts and your code. I got the original version to
work - I think all my debugging efforts were getting in the way - I just had
to change the ListDirectoryFiles to ListFiles and it works fine. The purpose
of the original function was to work in a combobox and provide a drop down
list of files. Now that it works, it's a simple matter of setting the
directory and string to search for, as variables that can be entered by the
user before the combobox gets selected. In the interest of completeness,
here is the code that is working now. Again thanks for all your help - I
hope you're sleeping now.
David
Function ListFiles(F As Control, id, row, col, Action)
Dim strGlobalPath As String
Dim strExtension As String
' Set strGlobalPath to your Dir
' Set strExtension to whatever filetype you're looking for
strGlobalPath = "C:\" ' Use your directory here
strExtension = "*.txt"
Static MDBS(), FileCount
Dim FileName
Select Case Action
Case acLBInitialize
FileCount = 0
FileName = Dir$(strGlobalPath & strExtension)
Do Until FileName = ""
FileCount = FileCount + 1
FileName = Dir$
Loop
ReDim MDBS(FileCount)
FileCount = 0
MDBS(FileCount) = Dir$(strGlobalPath & strExtension)
Do Until MDBS(FileCount) = ""
FileCount = FileCount + 1
MDBS(FileCount) = Dir$
Loop
ListFiles = True
Case acLBOpen
ListFiles = Timer
Case acLBGetRowCount
ListFiles = FileCount
Case acLBGetColumnCount
ListFiles = 1
Case acLBGetColumnWidth
ListFiles = -1
Case acLBGetValue
ListFiles = MDBS(row)
Case acLBEnd
Erase MDBS
End Select
End Function