RecordSet Error

[jameshall]

Hi All,

Its been a couple of years since I have done any VBA coding in Access 2K. I have a problem with trying to get the RecordCount out of a result set.

Here is the code. Its probably an easy solution but I just cant seem to solve it.

Dim StrSql1, StrSql2, StrSql3, StrSql4, OnOpen, StrSql As String

StrAccepted = "ACCEPTED"
StrOpen = "OPEN"
StrAwaiting = "AWAITING"

StrSql2 = "SELECT " & AppType & ".Status, " & AppType & ".[Date/time entered] From " & AppType & ""
StrSql3 = &quot; WHERE ((([&quot; & AppType & &quot;].[Status])=&quot;&quot;&quot; & StrAccepted & &quot;&quot;&quot; Or ([&quot; & AppType & &quot;].[Status])=&quot;&quot;&quot; & StrOpen & &quot;&quot;&quot; Or ([&quot; & AppType & &quot;].[Status])=&quot;&quot;&quot; & StrAwaiting & &quot;&quot;&quot AND (([&quot; & AppType & &quot;].[Date/time entered])>=#&quot; & MonDate & &quot;# And ([&quot; & AppType & &quot;].[Date/time entered])<=#&quot; & FriDate & &quot;#)) ;&quot;
StrSql = StrSql2 & StrSql3



CurrentDb.QueryDefs(&quot;GIT_Assigned&quot.SQL = StrSql

Dim rs1 As Recordset

rs1 = CurrentDb.OpenRecordset(&quot;GIT_Assigned&quot
Dim total As Integer
total = rs1.RecordCount
Debug.Print total

Any help/advice will be greatly appreciated

Many Thanks

James

 

[cmmrfrds]

On DAO recordsets the count is not available until you move to the end of the recordset - at least this was the case at one time.

rs1.MoveFirst
rs1.MoveLast
total = rs1.RecordCount

 

[Norris68]

If you are using DAO (which you are if you are referencing CurrentDB), you will need to fully populate the recordset.

If Not rs1.EOF Then
rs1.MoveLast
rs1.MoveFirst
End If
total = rs1.RecordCount

BTW:
Dim A, B, C As String
A and B will be Variants - only C will be a string.
You want -
Dim A As String, B As String, C As String

 

[jameshall]

Hi All,

Thank you for such a quick reply.

Now I get an error on this line

rs1 = CurrentDb.OpenRecordset(&quot;GIT_Assigned&quot

The error is &quot;Invalid use of property&quot;

Has anyone come across this before?

Regards

James

 

[Norris68]

What references have you got? CurrentDB() returns a DAO.Recordset. By default in Access 2000 you will be using a ADODB.Recordset. If so, use CodeProject.Connection.

Dim rs1 As ADODB.Recordset
Set rs1 = New ADODB.Recordset
Set rs1.ActiveConnection = CodeProject.Connection
rs1.Open StrSql
etc...

 

[jameshall]

Hi,

thanks for the help so far.

the code i am using now is...

Set rs1 = New ADODB.Recordset
Set rs1.ActiveConnection = CodeProject.Connection
rs1.Open &quot;GIT_Assigned&quot;
rs1.MoveFirst
rs1.MoveLast
total = rs1.RecordCount

I get the error &quot;Row set does not support fetching backwards&quot;

Am I missing the obvious?

Regards

James

 

[Norris68]

Ah! Now we are in ADO we don't need to do the MoveLast/MoveFirst - it will give an accurate RecordCount (under most circumstances) without it.

Looks like by default you will get a forward-only recordset. Before you Open the recordset, set it's CursorType property to either adOpenKeyset or adOpenStatic (depending on whether you want to update the data or not)

 

[cmmrfrds]

On ADO Access Provider recordsets, so as not to take the defaults and you want to move around in the recordset.

rs1.CursorLocation = adUseClient '- it is all client side anyway.
rs1.CursorType = adOpenStatic '- Access will force it to static anyway on a client side cursor that way there is no confusion.

With these settings the record count will be populated.