Problem with UDF to fill list box

[Ross]

Hi folks

I have a form that includes a list box that I want to populate with a list
of xml files.

I have set the Row Source Type property (using the properties window) to a
function called ListXMLSalesOrders (without using the = (equals) or the
function args).

The code for ListXMLSalesOrders is as below:

Function ListXMLSalesOrders(fld As Control, id As Variant, _
row As Variant, col As Variant, _
code As Variant) As Variant

Dim strFile As String
Static strFiles() As String
Static intEntries As Integer
Dim varReturnVal As Variant
Dim strPath As String
Dim intFileCount As Integer
'Dim fs As FileSearch

intFileCount = 0

strPath = "C:\Documents and Settings\Ross\My Documents\SOAC\Intro to
XML\Example XML App\Purch Order and Details\"

strFile = Dir("C:\Documents and Settings\Ross\My Documents\SOAC\Intro to
XML\Example XML App\Purch Order and Details\PO*.xml") 'get the first xml
file beginning with the letters PO

Do While strFile <> "" 'start the loop

intFileCount = intFileCount + 1
strFile = Dir

Loop

ReDim Preserve strFiles(0 To intFileCount - 1)

varReturnVal = Null

Select Case code

Case acLBInitialize

intEntries = 0
strFiles(intEntries) = Dir("C:\Documents and Settings\Ross\My
Documents\SOAC\Intro to XML\Example XML App\Purch Order and Details\PO*.xml")
Do Until intEntries = intFileCount

intEntries = intEntries + 1

If intEntries = intFileCount Then Exit Do

strFiles(intEntries) = Dir
Loop

varReturnVal = intEntries

Case acLBOpen

'generate unique ID for control
varReturnVal = Timer

Case acLBGetRowCount

varReturnVal = intEntries

Case acLBGetColumnCount

varReturnVal = 1

Case acLBGetColumnWidth

varReturnVal = -1

Case acLBGetValue

varReturnVal = strFiles(row)

Case acLBEnd

Erase strFiles

End Select

ListXMLSalesOrders = varReturnVal

End Function

Every time I load the form it appears as though the function keeps getting
called indefinitely (similar to an endless loop) - it gets to the last line
(End Function), exits then the function gets called again.

Stepping through the code it appears as though it is picking up the xml
files OK.

If anybody has any ideas I would really appreciate it.

Kind regards

Ross Petersen

 

[Allen Browne]

Access does call the function repeatedly, and (as the help says), in no
predefined sequence. As written, it will be impossibly slow.

The usual approach is to open a static array in the acLBInitialize case, and
load the array with the values. That gets called once, and so subsequent
calls can read the row and col from the array.

Here's an example:
http://allenbrowne.com/func-02.html