using query as recordset.

[Homegrownandy]

Im exporting a query to excel, I'm having some issues when I try to validate that some data exists.

I was getting "object doesn't support this property or method" on the last line in the code below.

I've changed nothing (I think!) and now the error has changed to Method or data member not found.

Set dbs = CurrentDb
Set rstSAGE = dbs.OpenRecordset("sage import 4")
If rstSage.Recordset.RecordCount = 0 Then

(this is only part of the code, I'm just showing where the error occurs and the associated code) I'm hoping its something simple that I've missed.

[Minty]

The use of the recordset word is wrong, as your rstSAGE is a recordset.
Also it's generally better to check for EOF and BOF as that would indicate no records - something like

Set dbs = CurrentDb
Set rstSAGE = dbs.OpenRecordset("sage import 4")
If rstSAGE.EOF or rstSAGE.BOF Then ...

This is because the recordset count is unreliable unless you go to the last record before checking.

[CJ_London]

have your tried?

rstSage.RecordCount

be aware that the recordcount will return 0 until the eof is reached so to be safe you should use

rstSage.MoveLast

before you count

if this is to check whether or not you have records, it is quicker to use

If not rstSage.EOF Then

because you are checking a status rather than counting records you do not need to use movelast

[Homegrownandy]

This works fine. Thanks both. I'm having new issues.. Might be back after some troubleshooting. haha.

Thanks again.

[ssanfu]

Spaces in object names are ill advised-->> "sage import 4".

If you must separate words, use the underscore: "sage_import_4".
I would suggest CamelBack Format: "SageImport4".

Good luck with your troubleshooting.

[Micron]

FWIW, I disagree with some of the approaches/statements.
If rs.EOF Or rs.BOF is not correct. For a one record count recordset, both would be True. Also True if the pointer falls on either the first or last record of the set. Either way, the usual approach is to not execute more code for a recordset that contains no records. The correct approach would then be

If Not (rs.BOF And rs.EOF) Then........alternatively If rs.RecordCount > 0
Don't check for = 0 since (I've read) some sql type databases can return -1 when no records are in a set

RecordCount will be 1 when the rs is opened, not 0 - as long as the rs contains at least 1 record. If there are 100, the count will be 1 before MoveLast, not 0.

I always MoveFirst because there's no guarantee that after determining you have more than 0 records, that you will start at what you think is the beginning.
I prefer to base a recordset on a query when order is important, so as to control order via the query, thus pretty much ensure I'm on what I think is the beginning of the recordset.

If Not (rs.BOF And rs.EOF) Then should eliminate the need to MoveLast unless a count is needed