Solved: How to solve MoveLast Method when Recordset is empty

[winxmun]

Hi,

I've a VBA code that uses MoveLast & I hit error after running the VBA. Then I realised that it is due to empty record. From the Access help file, it mentioned as follow:

"A call to either MoveFirst or MoveLast when the Recordset is empty (both BOF and EOF are True) generates an error."

Is there a solution for this?


My VBA code as follow:

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("NewBorr_SIBSAcNonBlank", dbOpenSnapshot)
rst.MoveLast
intRow = rst.RecordCount
rst.MoveFirst
initRowExcel = 3
x = 1
y = initRowExcel
avarRecords = rst.GetRows(intRow)
intRow = UBound(avarRecords, 2) + 1
intCol = UBound(avarRecords, 1) + 1
'-------------------open excel----------------------------------------
Set appXL = CreateObject("excel.application")
appXL.Visible = True
Set wbk = GetObject("D:\TFL_Weekly_Rpt.xls")
Set wbs1 = wbk.Worksheets(1)
wbk.Parent.Windows(1).Visible = True
For intRow = 0 To intRow - 1
For intCol = 0 To intCol - 1
wbs1.Cells(y, x).Value = avarRecords(intCol, intRow)
x = x + 1
Next intCol
x = 1
y = y + 1
Next intRow
rst.Close

[OBP]

If rst.recordcount <>0 then

[CreganTur]

[VBA]If Not (rst.BOF OR rst.EOF) Then[/VBA]
Personally, I prefer to use the built in BOF and EOF methods to ensure a recordset isn't empty, instead of checking the record count... but it's really 6 of 1, 1/2 dozen of another.

[stanl]

If rst.recordcount <>0 then

I believe recordcount will always be 0 if using a server-side cursor; so you should use that method with care; I find

If ! rst.eof

suffices.

[Norie]

What is that code actually meant to do?

As far as I can tell all it is doing is exporting to Excel.

If that is the case then why not use TransferSpreadsheet?

Then you shouldn't need to worry about using recordsets, BOF, EOF etc.

[winxmun]

Basically I will be running few queries & populate records into 2 different worksheet in the excel file. So can use TransferSrpeadsheet to export?

[Norie]

Short answer - yes.

Just export the queries.

[winxmun]

In one single macro? Can show me how? tks a lot!

[winxmun]

Managed to figure out the solution. tks!