Callback function not working

[Marc]

I have spent way tooo much time trying to make this function work.
I've searched the groups, and read ADH 97, but to no avail. I am
looking to populate a combo box with a two-column list of data, with
the first column being the bound column.

I have set the "Row Source Type" to FillTableOrQueryList, with 2
columns, with set widths.

Following is the code for my callback function. I know that the
"GetRows" function works due to some debug.print statements. However,
otherwise there are no errors, but the list box doesn't fill and the
debug.print statements in the code below never fire. Any help would be
much much appreciated.

The function is called with me.cborawcode.requery after another field
updates.

Function FillTableOrQueryList(ctl As Control, varID As Variant, lngRow
As Long, lngCol As Long, _
intCode As Integer) As Variant
'Callback function ot fill a multirow-multicolumn listbox

Dim varRetval As Variant
Static intRows As Integer
Dim intRcount As Integer
Static aData() As Variant
Const intCols = 2

'On Error GoTo Err_FillList

Select Case intCode

Case acLBInitialize
'Initialization code, obtain number of rows from recordset
intRows = GetRows(aData)

varRetval = (intRows > 0)
Debug.Print "Fillable" & varRetval

Case acLBOpen
'Return a Unique ID code, using the built-in Timer function
varRetval = Timer
Debug.Print "Open" & varRetval

Case acLBGetRowCount
'Return the number of rows
varRetval = intRows
Debug.Print "Rows" & varRetval

Case acLBGetColumnCount
'Return the number of columns
varRetval = intCols
Debug.Print "Columns" & varRetval

Case acLBGetColumnWidth
'Return the column widths. If you return -1 from the call,
Access will use the
'default width for the speicfic column, allowing you to use the
property sheet
'to supply the column widths
Select Case lngCol
Case 0:
varRetval = -1
Case 1:
varRetval = -1
End Select
Debug.Print "Width" & varRetval

Case acLBGetValue
'Return actual data, by returning an element of the array
filled in case acLBInitialize
varRetval = aData(lngRow, lngCol)
Debug.Print "Value " & varRetval

Case acLBEnd
' Clean up (release memory)
Erase aData
End Select

FillTableOrQueryList = varRetval

Exit_FillList:
Exit Function

Err_FillList:
MsgBox Err.Number & " " & Err.Description
Resume Exit_FillList

End Function

 

[Douglas J. Steele]

I don't understand what your

intRows = GetRows(aData)

statement is supposed to be doing.

According to the Help file,
Set varArray = recordset.GetRows (numrows)

The GetRows method syntax has the following parts.

Part Description
varArray A Variant that stores the returned data.
recordset An object variable that represents a Recordset object.
numrows A Variant that is equal to the number of rows to retrieve.

 

[Marc]

Douglas,

Thanks for the assist. This was from code I cribbed from another VIP
posting. GetRows is a private function that is supposed to fill aData
with the list values and return the total number of records to intRows.

Function GetRows(varData As Variant) As Integer
Dim intRcount As Integer
Dim intLoop As Integer

rstRawPurchFilter.MoveLast
rstRawPurchFilter.MoveFirst
intRcount = rstRawPurchFilter.RecordCount

'ReDim the array to hold rows and columns of data
ReDim aData(intRcount, 2)

'Fill in the array by running through the rows and columns
For intLoop = 0 To UBound(aData)
varData(intRcount, 0) = rstRawPurchFilter!rawcode
varData(intRcount, 1) = rstRawPurchFilter!rawname
rstRawPurchFilter.MoveNext
Next intLoop

GetRows = intRcount

End Function

 

[Albert D. Kallal]

For intLoop = 0 To UBound(aData)

varData(intRcount, 0) = rstRawPurchFilter!rawcode
varData(intRcount, 1) = rstRawPurchFilter!rawname
rstRawPurchFilter.MoveNext
Next intLoop

And where in the above do you set intRcount. It is never changed.....

Further, you do realize that the call back function is called BEFORE the
forms on-load, and EVEN BEFORE the forms on-open event. So, that really
means you have to load up the reocrdset BEFORE the form loads....

We seem to be writing a LOT of code here..and as mentioned, we find errors
in the above...

Why not use the SAME sql you used to fill he record set? How the heck did
rstRawPurchFilter get set?

Why not just stuff the SAME sql right into the listbox..

eg:

strSql = "select rawCode, rawName from tblWhatever where somefield =
whatever"

me.mylistbox.RowSource = strSql


The above is two lines of code. Why not use the above???

I mean, however you filled the recordset, why not just use the same sql to
fill the listbox? You can stuff the sql right into the control...and not
bother with the call back..and all of the supporting code such as creating
the reocrdset, looping etc...

 

[Marc]

Albert,

First, thanks for spotting the code mistakes. I broke out GetRows as
an experiment to try and fix the function.

As to the larger question of why not use SQL to requery the box. I've
successfully set this up, but because the recordset is a large unbound
visual fox pro table, it has a visible lag as it processes. I was
looking for a solution that would allow me to keep the recordset open
and simply refresh the filter for the records I wanted in particular.
Basically, it's an order transaction table that I filter for the items
on a particular purchase order. I am trying to restrict user entry
error in this case.

Is it possible then to open the recordset, use an initial list of
values to choose from and then continually requery/filter that
recordset as the purchase order number changes (I'm using the OnUpdate
event for that field)?

Thanks for your help in resolving this. It's vexing me.

marc

 

[Albert D. Kallal]

I've

successfully set this up, but because the recordset is a large unbound
visual fox pro table, it has a visible lag as it processes.

Ok, above is fair reason. I just wanted to point out that you can stuff the
sql right into the listbox, and I OFTEN seen people that did not realize
this.

In your case, I think the call back is a good reason, and a good choice due
to performance issues.

I was
looking for a solution that would allow me to keep the recordset open
and simply refresh the filter for the records I wanted in particular.

Hum, that above sends us backwards. Since if the fox table is slow to query,
then it is going to be likely even slower to filter. However, if the table
is already open, and the recordset is loaded, then certainly some time will
be saved. You don't mention how large this table is, but really, I don't
think a combo box, or listbox should ever have more then about 100 entries
anyway....

Is it possible then to open the recordset, use an initial list of
values to choose from and then continually requery/filter that
recordset as the purchase order number changes (I'm using the OnUpdate
event for that field)?

Well, you can, but as mentioned, if we are using the call back to avoided
re-opening, and re-filtering the recrodset, then we kind have a problem here
as we just admitted that the data source here is already slow.

You don't mention how large this data set is. I suppose you could try as you
have (use a move last) to have the data loaded into memory. You then could
set a filter on the recordset. I would not set a filter for each keystroke,
as that is really quite slow.

I would consider using the after update event, as that only fires after the
user hits enter (or tab). This approach thus could likely just stuff the sql
into the listbox....

There is noting stopping you from trying to complete your code, but
depending on how large the reocrdset data is, you likely would be better off
to have the user type some text, and THEN fire the search...not for each
character.

You also don't mention if this application is to run only local on the pc,
or over a network. Again, if it is local, then you have CONSIDERABLE more
leeway here..and can likely continue to pursue your current course. If you
plan multi-user use of this, then performance + bandwidth issues will
dictate your designs, and you likely can't afford a requery + filter for
each keystroke (well, again, this depends on how large of a data set we are
taking about).

You could try just stuff the sql into the listbox. On the other hand, for
saving bandwidth, you might get some ideas with the following screen shots:

http://www.members.shaw.ca/AlbertKallal/Search/index.html

 

[Marc]

Albert,

First of all, thank you so much for the considered response to my
issue. I have made significant headway in my callback function since I
last posted, by grabbing another example of a callback function and
altering that code. I am posting it below so that you can see what's
going on.

With respect to your question of table size, and performance. This
will be a database sitting on a network server and will, at limited
times, be multi-user (that is it will be available, but it is not all
that likely that more than one person may be in it at any given time).
This table in particular that is being referenced will have between 40K
and 250K records, I may be able to limit by date, but it isn't clear if
this will be practical.

As to firing the list fill for requery, you are absolutely correct; it
is based on OnUpdate event for the text field for purchase order
numbers. As a new purchase order is selected, the combo box is set to
match the items on that purchase order.

The two issues I am now running into are that (even though I'm trying
to set the initializing value of the list to False if there is no
purchase order record) I get Error 91: "Object variable or With block
variable not Set" on loading the form. I did figure out how to clear
the filter and refilter on switching the purchase order number.

Thanks,
marc

New Function:

Function FillList(ctl As Control, varID As Variant, lngRow As Long, _
lngCol As Long, intCode As Integer) As Variant
' Fill a combo or list box with a list of
' tables or queries.

' These variables "hang around" between
' calls to this function.
Static sastrNames() As String
Static sintItems As Integer

Dim db As Database
Dim rstFillList As DAO.Recordset
Dim intCount As Integer
Dim varRetval As Variant
Dim intLoop As Integer
Dim intItems As Integer
Dim tdf As TableDef
Dim qdf As QueryDef

On Error GoTo Err_FillList

varRetval = Null

Select Case intCode
' Initialize
Case acLBInitialize
'Set db = CurrentDb()
' Figure out the greatest number of entries
' there might be.
'db.TableDefs.Refresh
'db.QueryDefs.Refresh
'intItems = db.TableDefs.Count + db.QueryDefs.Count

If Len(Me.txtpurno) = 0 Or Me.txtpurno = Null Then
varRetval = False
Else

rstRawPurch.Filter = ""
Set rstRawPurch = rstRawPurch.OpenRecordset()
rstRawPurch.Filter = "[purno] = '" & Me.txtpurno & "'"
Set rstFillList = rstRawPurch.OpenRecordset()
rstFillList.MoveLast
rstFillList.MoveFirst
intItems = rstFillList.RecordCount

If intItems = 0 Then
varRetval = False
Else
' Set up the array to hold names.
ReDim sastrNames(0 To intItems - 1, 2)
' Set up variable to hold the number of names.
sintItems = 0

Do
sastrNames(sintItems, 0) = rstFillList!rawcode
sastrNames(sintItems, 1) = rstFillList!rawname
rstFillList.MoveNext
sintItems = sintItems + 1
Loop Until rstFillList.EOF

' Tell Access that the list box is OK, so far.
varRetval = True
End If
End If

Case acLBOpen
' Get a unique ID number for control.
varRetval = Timer

Case acLBGetRowCount
' Get the number of rows.
varRetval = sintItems

Case acLBGetValue
' Get the actual data for the row.
varRetval = sastrNames(lngRow, lngCol)

Case acLBEnd
' Clean up (release memory)
Erase sastrNames

End Select

FillList = varRetval

Exit_FillList:
Exit Function

Err_FillList:
MsgBox Err.Number & " " & Err.Description
Resume Exit_FillList

End Function