Recordcount on a ADO Recordset 1

[zevw]

I am trying to get a record count on a ADO recordset and it is not returning anything.

How do I write this?

Dim adoRS As ADODB.Recordset

Set adoRS = New ADODB.Recordset
With adoRS

    .Open "SELECT CaseNos.* From CaseNos WHERE (((CaseNos.Prog)=" & Val(Forms!CaseForm.OpenArgs) & ") AND ((CaseNos.Year)=" & Year(Forms!CaseForm.CaseDate) & "));"
[COLOR=red]MsgBox adoRS.RecordCount[/color]

End With

 

[Remou]

Perhaps:

Dim adoRS As ADODB.Recordset

cn = CurrentProject.Connection
Set adoRS = New ADODB.Recordset
With adoRS

    .Open "SELECT * From tblZ", cn, adOpenKeyset
adoRS.MoveLast

MsgBox adoRS.RecordCount

End With

 

[zevw]

Thanks for teaching me!

I guess for record counts I will have to use

.Open "SELECT * From tblZ", cn, adOpenKeyset

and for adding or editing records I need to use

.Open "SELECT * From tblZ", cn, adOpenKeyset, adOpenDynamic, adLockOptimistic

Thanks it works

 

[Remou]

You can only have one cursor. They each have different uses, so pick either adOpenKeyset or adOpenDynamic (or some other, as suits). It is generally best to use Access with DAO, unless you have a particular reason for using ADO.

 

[zevw]

Remou,

Where can I learn more about ADO and its functions. I need it here because I am using it for multi user locking see thread705-1357831.

Thanks again for all your help

 

[PHV]

Search your local drives for files named ado*.chm

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886