RowsourceType Property (User-Defined Function)

  • Thread starter Thread starter Isbjornen
  • Start date Start date
I

Isbjornen

Hello,

I need some help with the RowsourceType Property (User-
Defined Function) which is described in Access 2000 help
files.

I'm writing a dbase that is very similar to the ListMDBs
example, with one difference. I want to use 2 columns
(the example uses 1 column), and the first column should
display a count of each file it finds. For example, if I
have 3 files, I want the listbox to display something like
this:

Col 1 Col 2
1 | MyFirstFile.MDB
2 | MySecondFile.MDB
3 | MyThirdFile.MDB

Currently I'm able to display 2 columns (through the
propertysheet), but I cannot fill the first column with
numbers (the program fills both columns with the same
value i.e.:
MyFirstFile.MDB | MyFirstFile.MDB etc.)

Any help is appreciated.

Thanx,
Isbjornen
 
Isbjornen said:
I need some help with the RowsourceType Property (User-
Defined Function) which is described in Access 2000 help
files.

I'm writing a dbase that is very similar to the ListMDBs
example, with one difference. I want to use 2 columns
(the example uses 1 column), and the first column should
display a count of each file it finds. For example, if I
have 3 files, I want the listbox to display something like
this:

Col 1 Col 2
1 | MyFirstFile.MDB
2 | MySecondFile.MDB
3 | MyThirdFile.MDB

Currently I'm able to display 2 columns (through the
propertysheet), but I cannot fill the first column with
numbers (the program fills both columns with the same
value i.e.:
MyFirstFile.MDB | MyFirstFile.MDB etc.)
Click to expand...

It's a little difficult to determine what's wrong with your
function without seeing the code, but I'll guess that you
not using the Col argument properly.
 
It's a little difficult to determine what's wrong with your
function without seeing the code, but I'll guess that you
not using the Col argument properly.
Click to expand...

This is the code i use, straight from the helpfiles in
Access (although my code is modified for my directories)
So, if I can get this example code to work, I can get my
own code to work.

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.

****Note: This part is modified for my directories
(which makes my code longer) ****
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.

***Note: Ive changed this part from 1 to 2****
ReturnVal = 2
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

I assume that I have to do something with Case
acLBGetValue to display the count in Column 1.

Isbjornen
 
(snip)
Case acLBGetValue ' Get data.
ReturnVal = dbs(row)
Click to expand...


acLBGetValue wants the value for the specified *row and column*. Your code
ignores the column, hence the result you are seeing.

Note also: ROW & COL are (I think) zero-based (eg. row=0 is the first row).
You would be better to dimension your arrays with an explicit lower bound,
instead of elying on the module option base.

HTH,
TC
 
Glad you've got it working!

But, that is really not what it needs to look like in the general case.

For the benefit of other readers: acLBGetValue wants you to return the value
for the specified row and column, as defined by the ROW and COL parameter
values. In general, to do this, you must use the values of ROW *and COL* to
perform a calculation, or index into a 2-dimensional array, or similar.
Setting returnval to 1+row when col=0, is not correct in every case, at all.

HTH,
TC
 
Back
Top