Getting the correct count from a DAO recordset

[kcdoell]

Good Morning World:

I have written the following code:

[code=vb]
'Gives the user to delete and replace all records for a specified parameter

LockSQL = "SELECT * FROM tblStaticAllFor ecast WHERE" & _
" DivisionIDFK = " & Val(Me.cboDivis ion.Value)

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenR ecordset(LockSQ L)
recordexists = rst.RecordCount

MsgBox "The number of records you are about to delete is " & recordexists & "." & _
" Click the ok button to proceed", vbOKCancel, vbDefaultButton 2


If Nz(recordexists , 0) > 0 Then

'code will delete the records that the user has selected....... .......

End If
End If
End Sub
[/code]

Before I proceed with a code to delete the selected records from my "LockSQL" I wanted to make sure the count was correct. I know that I have 14 records in my tblStaticAllFor ecast that are all tagged with division set to value 2. So I should of recieved a count of 14 when I set the parameter in my form and ran my code, instead I got a count in my message box for 1. Does anybody know why that would happened? I ran a separate query via Access looking at the same parameters on the form and it got the correct count (14). Is somehow the count different when using a DAO recordset or is something wrong in my code??

Thanks for any thoughts,

Keith.

[Scott Price]

On Line 4, why are you using the Val() function?

Regards,
Scott

[kcdoell]

I am a Newbie at VBA, so that is something I got out of a book I have been reading:

Microsoft Access VBA Programming by Michael Vine

[kcdoell]

I solve it:

[Code=vb]
rst.MoveLast 'Move to last record
recordexists = rst.RecordCount
[/code]

Thanks,

Keith.

[dbpros]

I always do a "rs.movelas t" followed by an "rs.movefir st" before executing the rs.recordcount.

It always gives the correct number of records in the recordset.

good luck

http://www.db-pros.com

[kcdoell]

Thanks, I just received an error message "No current record. (Error 3021)" when the record count is zero. How would I handle that scenario??

Keith.

[ADezii]

Good Morning World:

I have written the following code:

[code=vb]
'Gives the user to delete and replace all records for a specified parameter

LockSQL = "SELECT * FROM tblStaticAllFor ecast WHERE" & _
" DivisionIDFK = " & Val(Me.cboDivis ion.Value)

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenR ecordset(LockSQ L)
recordexists = rst.RecordCount

MsgBox "The number of records you are about to delete is " & recordexists & "." & _
" Click the ok button to proceed", vbOKCancel, vbDefaultButton 2


If Nz(recordexists , 0) > 0 Then

'code will delete the records that the user has selected....... .......

End If
End If
End Sub
[/code]

Before I proceed with a code to delete the selected records from my "LockSQL" I wanted to make sure the count was correct. I know that I have 14 records in my tblStaticAllFor ecast that are all tagged with division set to value 2. So I should of recieved a count of 14 when I set the parameter in my form and ran my code, instead I got a count in my message box for 1. Does anybody know why that would happened? I ran a separate query via Access looking at the same parameters on the form and it got the correct count (14). Is somehow the count different when using a DAO recordset or is something wrong in my code??

Thanks for any thoughts,

Keith.

Many times, in order to get an accurate count of Records in a Recordset, you have to 'Traverse' the Recordset as in:
[CODE=vb]
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenR ecordset(LockSQ L)

rst.MoveLast
rst.MoveFirst

'Now, will return the correct Record Cound
recordexists = rst.RecordCount[/CODE]

[kcdoell]

Thanks:

I did that and solved my error issue on a zero account:

[code=vb]
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenR ecordset(LockSQ L)
recordexists = rst.RecordCount

'If no records are found
If recordexists = 0 Then
MsgBox "There are no records to delete."
Else

rst.MoveLast 'Move to last record
rst.MoveFirst 'Move to First record

If MsgBox("The number of records you are about to delete is " & recordexists & "." & _
" Click the ok button to proceed", vbOKCancel, vbDefaultButton 2) = vbOK Then

If Nz(recordexists , 0) > 0 Then

'code will delete the records that the user has selected.
[/code]

Thanks to all.

Keith.