UDF in RowSourceType not working

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

Guest

Hi-
I'm trying to use a UDF for the RowSourceType of a Combo Box in Access2000
(Windows2k).

I'm finding that the UDF is not being called multiple times as advertised in
code samples I have. The result is the I only get the one call to the UDF,
which works on the Intiialize codde value only, and then close out....the
result being the box does not get filled, and I get a fatal error when trying
to open the box.

Any thoughts? Should I assign the RowSourceType in code? What am I
overlooking?
 
I'm guessing that by UDF you mean "User Defined Function" or, more commonly,
Callback Function?

Try compiling code and then compacting and repairing database.

If this doesn't fix the problem then please supply the function call as it
appears in the RowSourceType of the combo box and the function declaration
as it appears in code.

For example, the row source should be (no quotes, no argument list):
ComboBox.RowSourceType = MyRowSource

and the callback function would like
Public Function MyRowSource(ByRef rctlControl As Control, ByVal vlngID As
Long, ByVal vlngRow As Long, ByVal vlngColumn As Long, _
ByVal vintCode As Integer) As Variant
Dim varReturn As Variant
Select Case vintCode
Case acLBInitialize
varReturn = True
Case acLBOpen
varReturn = Timer
Case acLBGetRowCount
varReturn = getUBoundArray() + 1
....

Graeme.
 
Mr. Richardson-

In the RowSourceType property of the combo box's property sheet, I have-

ListMDBs

no quotes, no brackets, no equal sign...just that, and nothing in the
RowSource property.

The Code I am trying to run is as follows-

Function ListMDBs(fld As Control, ID As Variant, _
row As Variant, col As Variant, _
Code As Variant) As Variant

Static dbs(127) As String, Entries As Integer
Dim ReturnVal As Variant
ReturnVal = Null
Select Case Code
Case acLBInitialize ' Initialize.
Entries = 0
dbs(Entries) = Dir("*.MDB")
Do Until dbs(Entries) = "" Or Entries >= 127
Entries = Entries + 1
dbs(Entries) = Dir
Loop
ReturnVal = Entries
Case acLBOpen ' Open.
' Generate unique ID for control.
ReturnVal = Timer
Case acLBGetRowCount ' Get number of rows.
ReturnVal = Entries
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 = dbs(row)
Case acLBEnd ' End.
Erase dbs
End Select
ListMDBs = ReturnVal
End Function

Thank you for looking at this!

wolfie
 
Hi wolfie

The Case acLBInitialize should return true, if it returns false (for
whatever reason) then the callback is aborted.

Select Case Code
Case acLBInitialize ' Initialize.
Entries = 0
dbs(Entries) = Dir("*.MDB")
Do Until dbs(Entries) = "" Or Entries >= 127
Entries = Entries + 1
dbs(Entries) = Dir
Loop
ReturnVal = True
Case acLBOpen ' Open.
' Generate unique ID for control.
ReturnVal = Timer
Case acLBGetRowCount ' Get number of rows.
ReturnVal = Entries
Case acLBGetColumnCount ' Get number of columns.
....


HTH, Graeme.
 
Thanks Graeme...

Your tip got me further along.

I found I had to specify a more specific path than Dir("*.MDB") to get the
code to cycle thru the loop.

However, the combo box does not show the list when I try to open it. What
happens is it goes back into the SELECT CASE again when the combo arrow is
clicked, and I am suspecting the data has already gone out of scope so when
the new codes are presented, there is nothing left to show.

Any help?

wolfie
 
Hi Graeme-

I stepped thru the code, and what happens when I open the combo box with the
arrow is the list is sized to the number of rows that should appear, but no
data appears.

The code appears to work fine until it gets to code 6 (acLBGetValue). I
have 8 records that should appear. dbs(row) has a row value of 7, indicating
only the last record, and a mouse over of dbs while stepping thru indicates
the proper list item for that one entry.

Problem- List should show 8 entries, but does not show even one.

wolfie
 
HI, this code (below) works fine for me.
Check that file names are populated to the array by placing a debug.print
statement in the loop (shown).
It sounds like the files are being populated to the array, but there not
being shown. Try deleting the ReturnVal = -1 statement from Case
acLBGetColumnWidth. I normally leave this Case blank and set the column
width in the controls property. I suspect that this is the cause of not
seeing values. Check that the column width property of the combo box is not
set to 0 also.
This is probably not the cause of problems, but if the above doesn't work
then try deleting the Erase dbs statement in Case acLBEnd

Graeme.

Public Function ListMDBs(fld As Control, ID As Variant, _
row As Variant, col As Variant, _
Code As Variant) As Variant

Static dbs(127) As String, Entries As Integer
Dim ReturnVal As Variant
ReturnVal = Null
Select Case Code
Case acLBInitialize ' Initialize.
Entries = 0
dbs(Entries) = Dir("C:\Temp\*.MDB")
Do Until dbs(Entries) = "" Or Entries >= 127
Entries = Entries + 1
dbs(Entries) = Dir
Debug.Print dbs(Entries)
Loop
ReturnVal = True
Case acLBOpen ' Open.
' Generate unique ID for control.
ReturnVal = Timer
Case acLBGetRowCount ' Get number of rows.
ReturnVal = Entries
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 = dbs(row)
Case acLBEnd ' End.
'Erase dbs
End Select
ListMDBs = ReturnVal
End Function
 
Thank you Graeme!

I found that your code ran fine with the acGetColumnWidth included and the
Erase function commented out, but I am omitting acGetColumnWidth in my
production work, as I need to set these specific for multiple columns.

I have taken what I learned here and successfully applied it to using a
recordset derived from a SQL stored procedure to fill the array with
GetRows, adUseClient so I can get the RecordCount for acLBGetRowCount, and
MyArray(col,row) to pass on the return in acLBGetValue to fill the combo box,
which was
my goal all along. I have about 40 more combo boxes to convert to this
method, from that method which uses Table/Query and a SELECT statemnet in the
property sheet of the combo box.

A couple last questions....

Are there any memory considerations stemming from not
using the Erase function?

Is there any documentation I can look at to give me all the code value
meanings being passed into the function?

Thanks again!

wolfie
 
I found that your code ran fine with the acGetColumnWidth included and the
Erase function commented out, but I am omitting acGetColumnWidth in my
production work, as I need to set these specific for multiple columns.
Click to expand...
The width of columns can be set in the ColimnWidth property of the ComboBox
(e.g. 0cm,1cm,2.5cm). Either way is fine.
I have taken what I learned here and successfully applied it to using a
recordset derived from a SQL stored procedure to fill the array with
GetRows, adUseClient so I can get the RecordCount for acLBGetRowCount, and
MyArray(col,row) to pass on the return in acLBGetValue to fill the combo
box,
which was
my goal all along. I have about 40 more combo boxes to convert to this
method, from that method which uses Table/Query and a SELECT statemnet in
the
property sheet of the combo box.

A couple last questions....

Are there any memory considerations stemming from not
using the Erase function?
Click to expand...
Memory should be fine. You will notice a decrease in performance if you try
display many rows. I generally use UNION queries when I want to show
dirrerent things in a list: Jet is much faster than VBA.
Is there any documentation I can look at to give me all the code value
meanings being passed into the function?
Click to expand...
Pretty much only the help files. You could try searching the MS
Knowledgebase. Nice section MS Access Developer's Handbook.

Happy that I could help, Graeme.
 
Thanks again Graeme!

None of the combo boxes will show too many rows, so I should be ok there.

Over and out, and Have A Nice Day!!!

wolfie
 
Back
Top