Why I am not able to get all records by excluding the Numrows argument in getrows()? (1 Viewer)

prabha_friend · Jul 31, 2014

Why I am not able to get all records by excluding the Numrows argument in the getrows method?

Sub Test2()
Dim myrset As Recordset
Set myrset = CurrentDb.OpenRecordset("SELECT * FROM Holidays;")
myrset.MoveLast
myrset.MoveFirst
MsgBox Excel.Application.WorksheetFunction.Networkdays(#8/1/2014#, #8/31/2014#, myrset.GetRows(myrset.RecordCount))
MsgBox Excel.Application.WorksheetFunction.Networkdays(#8/1/2014#, #8/31/2014#, myrset.GetRows())
End Sub

Second MessageBox is giving a wrong value.

Is it a mandatory one? Or Do I have to do some ritual like (Movelast) before that? Thank you.

(Holidays table is just having the values in the array only ie. #08/15/2014# and #08/29/2014#)

vbaInet · Jul 31, 2014

We've already explained this to you a few times in your other thread. The recordset doesn't get fully populated until you go to the last record. Not only that, we gave you two ways of getting all records, one by moving last and the other which is getting a count.

Please refer to your other thread where you will find your answer.
http://www.access-programmers.co.uk/forums/showthread.php?t=267429&page=2

vbaInet · Jul 31, 2014

I'll expand a bit.

If you don't want to enter the parameter in GetRows() you need to MoveLast/First.
If you don't want to waste resources doing a MoveLast/First, get the record count from a query and use this as the parameter for GetRows().

Both these methods were shown to you in your previous thread.

prabha_friend · Jul 31, 2014

vbaInet, Please read my first post in this thread. I have included the movelast and movefirst but still I am getting a difference in getrows(numrows) and getrows().

vbaInet · Jul 31, 2014

Let me clarify.

If you have 10 records and you GetRows() 5 records the first time, where's your cursor? It stays at the 5th record. If you GetRows() again without a parameter, where's your cursor? Remember it is at the 5th record so it will return rows 5 to 10.
So MoveUp to get the beginning of your recordset.

prabha_friend · Aug 1, 2014

vbaInet said:
Let me clarify.

If you have 10 records and you GetRows() 5 records the first time, where's your cursor? It stays at the 5th record. If you GetRows() again without a parameter, where's your cursor? Remember it is at the 5th record so it will return rows 5 to 10.
So MoveUp to get the beginning of your recordset.

Good Morning vbaInet, My Question is simple: How to get all records at a time using the getrows method? (without mentioning the numrows argument)

Please see the following code,

Sub Test2()
Dim myrset As Recordset
Set myrset = CurrentDb.OpenRecordset("SELECT * FROM Holidays;")
myrset.MoveLast
myrset.MoveFirst
MsgBox Excel.Application.WorksheetFunction.Networkdays(#8/1/2014#, #8/31/2014#, myrset.GetRows())
End Sub

Here I have the MoveFirst before accessing the myrset.GetRows() but still its giving wrong value only (i.e 20)

As you have mentioned about cursor and about retrieving only first 5 records, Now I am having a doubt that whether we can only retrieve 5 records at a time when we are not giving the numrows parameter?

vbaInet · Aug 1, 2014

Morning prabha,

Hmmm... ok. For me, even though I want to retrieve all record, I will always explicitly give it a parameter so I've not experienced this problem before. It's best to be explicit.
Let's try a couple of things:

Code:

Dim myrset As Recordset
[COLOR="blue"]Dim myArray as Variant[/COLOR]

Set myrset = CurrentDb.OpenRecordset("SELECT * FROM Holidays;", [COLOR="blue"]dbOpenSnapshot[/COLOR])

with myrset
    .MoveLast
    .MoveFirst
    [COLOR="Blue"]myArray = .GetRows()[/COLOR]
end with

MsgBox Excel.Application.WorksheetFunction.Networkdays(#8/1/2014#, #8/31/2014#, [COLOR="blue"]myArray[/COLOR])

All the new relevant parts in blue.

prabha_friend · Aug 1, 2014

Hi,
It's giving 20 but 19 is the right answer.
Total Working days - 21
No of Holidays - 2
Expected Result - 19

vbaInet · Aug 1, 2014

You mean the NetworkDays function is the problem?

prabha_friend · Aug 1, 2014

No.
For i = 0 To UBound(myArray)
MsgBox myArray(0, i)
Next i
Delivers only one message - The First Holiday (15th August 2014) - The Indian Independence Day

vbaInet · Aug 1, 2014

I'm sure you'd definitely want that day included

It's only returning the first row. I would have thought that without a parameter it would return all rows. I know that ADO does this.

You'll have to give it a parameter and it don't necessarily need to be a count of the records in your table. If you give it any number that's greater than the number of records in the table, it will only return the number of records in the table. I.e.:

Code:

Const TOT_ROWS As Long = [COLOR="blue"][B]400[/B][/COLOR]

myArray = myrset.GetRows(TOT_ROWS)

I'm guessing there aren't more than 400 records in your table. Increase the threshold to whichever number you please.

Why I am not able to get all records by excluding the Numrows argument in getrows()? (1 Viewer)

prabha_friend

Prabhakaran Karuppaih

vbaInet

AWF VIP

vbaInet

AWF VIP

prabha_friend

Prabhakaran Karuppaih

vbaInet

AWF VIP

prabha_friend

Prabhakaran Karuppaih

vbaInet

AWF VIP

prabha_friend

Prabhakaran Karuppaih

vbaInet

AWF VIP

prabha_friend

Prabhakaran Karuppaih

vbaInet

AWF VIP

Similar threads

Users who are viewing this thread