Only retrieving first row from getrow() function 2

[44nato44]

Hi

I have the code below which gets a recordset from a db and add it to an array with the function getrows(). My problem is that it only get the first row and not the entire table.

I have worked with asp and there it just uboun(arrayname), and then you have the max row of the array... should it not be as easy in MS Access ?

Code :

Dim db As DAO.Database
Dim tblRstApp As DAO.Recordset
Dim tblRstAppSupTbl As DAO.Recordset
Dim AppSubTbl as Variant
Dim i

Set db = CurrentDb()

Set tblRstAppSupTbl = db.OpenRecordset("TBL_AppSupTbl", dbOpenDynaset)


AppSubTbl = tblRstAppSupTbl.GetRows()

tblRstApp.FindFirst "ApproverID =" & varApproverID

Me.txtSubmitter.Value = tblRstApp("Submitter")

For i = 0 To UBound(AppSubTbl, 2)
If tblRstApp("DestTable") = AppSubTbl(1, i) Then
Me.txtDestTable = AppSubTbl(2, i)
Exit For
End If
Next

 

[missinglinq]

According to Access Help, the GetRows() method in DAO requires an argument for the number of rows to retrieve, so

AppSubTbl = tblRstAppSupTbl.GetRows()

should be

AppSubTbl = tblRstAppSupTbl.GetRows(numrows)

where numrows is the number of rows you want to retrieve.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[MazeWorX]

if you wanted to pass the total records in the table to the array you could do a tblRstAppSupTbl.RecordCount this will give you the number of rows to pass to the array with the getrows method

HTH << MaZeWorX >> Remember amateurs built the ark - professionals built the Titanic [flush]

 

[44nato44]

Thanks guys, that worked

 

[missinglinq]

Glad we could help!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[Fattire]

And just make sure that if you are pulling some big recordsets that you type in:

tblRstAppSupTbl.MoveLast

before using the RecordCount to get an accurate record count -
RecordCount in DAO will only count what it has pulled so far so that you can work the data that has been pulled while it pulls the rest of the data. Then you can move again through the recordset by putting in:

tblRstAppSupTbl.MoveFirst