Follow-up to Filling List Box Dynamically

[David]

Hi Guys,

Thanks for your help so far. To remind you what we were talking about a week
or two ago, I have a listbox that I want to summarize "vectors" in my form.
For example, if a change is made in my form that causes a value to change in
an underlying *temporary* table, I want to see this reflected in the listbox.

Initially I tried to do this by using a semicolon delimited list that I
generated programatically. However, this would only refresh when the form
loaded, and I was not able to change it during run-time. This is, apparently,
a weakness of this method.

A kind gentleman in this group suggested I try using a custom function,
which I did. I am pasting an abridged version below for discussion purposes:

Function ShowKnotSummary(fld As Control, id As Variant, _
Row As Variant, col As Variant, _
code As Variant) As Variant
Dim intOffset As Integer
Dim varRetVal As Variant
Static strSQL_lstSecurities As String
Static strKnotSummary() As String
Select Case code

Case acLBInitialize
strSQL_lstSecurities = <DELETED>
Call CheckNumSecInBuckSumm(strSQL_lstSecurities, #12/1/2005#, _
& strKnotSummary)
'The above simply populates the string array with my values based on
'the SQL statement and the effective date.
varRetVal = True
Case acLBOpen
' Use the Timer function to generate a unique value.
varRetVal = Timer
Case acLBGetRowCount
' Create space for the appropriate # of rows of data.
varRetVal = UBound(strKnotSummary, 1) + 1
Case acLBGetColumnCount
' Create three columns of data.
varRetVal = 3
Case acLBGetColumnWidth
' Use the control's default column width.
varRetVal = -1
Case acLBGetValue
varRetVal = strKnotSummary(Row, col)
Case acLBGetFormat
Case acLBEnd
End Select
ShowKnotSummary = varRetVal
End Function

This works fine, again, on the first run. But because the variables are
static, and because the SQL statement only fires on the loading of the
form...it does not update with each change to the underlying temp table on
which the SQL statement draws the summary data. Instead, it displays the same
(indeed static) data. I cannot figure out a way around this without calling
the SQL statement every time I request a data point...which is very slow.

Can anyone provide any guidance/assistance? Much obliged if so.

Also, I wonder if anyone can point me to any reference data on the custom
format that you can return in such listbox functions? I can't seem to find
anything via google or on MSN...perhaps I am looking for the wrong keywords.

Thanks in advance for any assistance.

David

 

[David]

Is there a forum you think I would be wiser to post this message in?

I'm truly stuck and don't know where to turn. Any help, even a point in the
right direction, would be most appreciated.

Thanks again!

David

 

[Douglas J. Steele]

Rather than having strKnotSummary defined as Static within the routine,
declare it as a module-level variable (i.e.: declare it at the beginning of
the form's module, outside of any subs or functions) so that its values can
be accessed from anywhere in the module. Have some way of updating the array
when your data changes.