getting a recordcount.

[drewdaman]

hi everyone...
i have a record set where i store information from a query. somehow tho, s.recordcount is always 0! i saw this post:

"faeryfyrre (Programmer) Aug 21, 2003
For a start .recordCount does not give a valid total until you've progressed to the end of the RecordSet.
You have to use .MoveLast before using .recordcount to get an accurate count.
ie
.movelast
if .RecordCount > 0 then"

but if i do this it gives me an error

any ideas? my code is the follwing:

Private Sub updateBCAutC(temp As String)
Dim MaxBCAutC As Integer
MaxBCAutC = 6
Dim current As String
current = Now
Dim s As ADODB.Recordset
Set s = New ADODB.Recordset
s.Open "Select * from [BarCode] where [BarCode]='" & temp & "'", _
CodeProject.Connection, adOpenStatic
s.MoveLast
'MsgBox (s.RecordCount)
If s.RecordCount < MaxBCAutC Then
DoCmd.RunSQL &quot;INSERT INTO BarCode (BarCode, LastUpdated) VALUES ('&quot; & temp & &quot;', '&quot; & current & &quot;')&quot;
'DoCmd.RunSQL &quot;INSERT INTO BarCode (BarCode, LastUpdated) VALUES ('&quot; & temp & &quot;', ' #Now# ')&quot;
'DoCmd.RunSQL &quot;select Min(LastUpdated) from BarCode&quot;
Else
DoCmd.RunSQL &quot;INSERT INTO BarCode (BarCode, LastUpdated) VALUES ('&quot; & temp & &quot;', '&quot; & current & &quot;')&quot;
DoCmd.RunSQL &quot;Delete from BarCode where LastUpdated=(SELECT Min(LastUpdated) AS LastUpdate FROM BarCode)&quot;
End If
End Sub

The problem is that the recordcount is always zero! Thanks everyone

drew

 

[GComyn]

have you tested the sql string:

"Select * from [BarCode] where [BarCode]='" & temp & "'"

??

If this never returns anything...

what I would do would be to put in an strSQL string variable, put the above string into it, then print it to the debug screen.

Then copy the resulting string and put it as the sql of a new query, then see what you get... the temp variable may be what is causing it to return nothing.

After you have tested that, let us know what you get....

GComyn

 

[drewdaman]

ps....
sorry... should have clarified that a bit more.. the problem is that the recordcount is always zero if i remove the s.movelast line. if i have that line the error i get is

&quot;Either EOF or BOF is True, or the current record has been deleted. Requested operation requires a current record.&quot;

 

[Norris68]

Why not count your records using SQL instead e.g.

s.Open &quot;SELECT COUNT(BarCode) AS BCCount FROM BarCode&quot;
If s(&quot;BCCount&quot.Value < MaxBCAutC Then
...

Don't forget to close your recordset after use either.

I'm not sure about naming a field the same as the table though - could cause confusion. Personally, I'd call the table 'tblBarCode'.

 

[Norris68]

Your error means that you are not returning any records. You should not attempt to MoveLast when you are at EOF.
You could try:
If Not s.EOF Then s.MoveLast

 

[drewdaman]

thanks guys... got it working

thanks for all the help again