473,320 Members | 1,841 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Getting the correct count from a DAO recordset

kcdoell
230 100+
Good Morning World:

I have written the following code:

Expand|Select|Wrap|Line Numbers
  1. 'Gives the user to delete and replace all records for a specified parameter
  2.  
  3. LockSQL = "SELECT * FROM tblStaticAllForecast WHERE" & _
  4.         " DivisionIDFK = " & Val(Me.cboDivision.Value)
  5.  
  6. Dim rst As DAO.Recordset
  7. Set rst = CurrentDb.OpenRecordset(LockSQL)
  8. recordexists = rst.RecordCount
  9.  
  10.     MsgBox "The number of records you are about to delete is " & recordexists & "." & _
  11.     " Click the ok button to proceed", vbOKCancel, vbDefaultButton2
  12.  
  13.  
  14. If Nz(recordexists, 0) > 0 Then
  15.  
  16.    'code will delete the records that the user has selected..............
  17.  
  18.     End If
  19.   End If
  20. End Sub
  21.  
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 tblStaticAllForecast 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.
Mar 27 '08 #1
7 8110
Scott Price
1,384 Expert 1GB
On Line 4, why are you using the Val() function?

Regards,
Scott
Mar 27 '08 #2
kcdoell
230 100+
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
Mar 27 '08 #3
kcdoell
230 100+
I solve it:

Expand|Select|Wrap|Line Numbers
  1. rst.MoveLast        'Move to last record
  2. recordexists = rst.RecordCount
  3.  
Thanks,

Keith.
Mar 27 '08 #4
dbpros
15
I always do a "rs.movelast" followed by an "rs.movefirst" before executing the rs.recordcount.

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

good luck

http://www.db-pros.com
Mar 27 '08 #5
kcdoell
230 100+
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.
Mar 27 '08 #6
ADezii
8,834 Expert 8TB
Good Morning World:

I have written the following code:

Expand|Select|Wrap|Line Numbers
  1. 'Gives the user to delete and replace all records for a specified parameter
  2.  
  3. LockSQL = "SELECT * FROM tblStaticAllForecast WHERE" & _
  4.         " DivisionIDFK = " & Val(Me.cboDivision.Value)
  5.  
  6. Dim rst As DAO.Recordset
  7. Set rst = CurrentDb.OpenRecordset(LockSQL)
  8. recordexists = rst.RecordCount
  9.  
  10.     MsgBox "The number of records you are about to delete is " & recordexists & "." & _
  11.     " Click the ok button to proceed", vbOKCancel, vbDefaultButton2
  12.  
  13.  
  14. If Nz(recordexists, 0) > 0 Then
  15.  
  16.    'code will delete the records that the user has selected..............
  17.  
  18.     End If
  19.   End If
  20. End Sub
  21.  
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 tblStaticAllForecast 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:
Expand|Select|Wrap|Line Numbers
  1. Dim rst As DAO.Recordset
  2.  
  3. Set rst = CurrentDb.OpenRecordset(LockSQL)
  4.  
  5. rst.MoveLast
  6. rst.MoveFirst
  7.  
  8. 'Now, will return the correct Record Cound
  9. recordexists = rst.RecordCount
Mar 27 '08 #7
kcdoell
230 100+
Thanks:

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

Expand|Select|Wrap|Line Numbers
  1. Dim rst As DAO.Recordset
  2. Set rst = CurrentDb.OpenRecordset(LockSQL)
  3. recordexists = rst.RecordCount
  4.  
  5. 'If no records are found
  6.     If recordexists = 0 Then
  7.         MsgBox "There are no records to delete."
  8.             Else
  9.  
  10.     rst.MoveLast        'Move to last record
  11.     rst.MoveFirst        'Move to First record
  12.  
  13.         If MsgBox("The number of records you are about to delete is " & recordexists & "." & _
  14.         " Click the ok button to proceed", vbOKCancel, vbDefaultButton2) = vbOK Then
  15.  
  16.     If Nz(recordexists, 0) > 0 Then
  17.  
  18. 'code will delete the records that the user has selected.
  19.  
Thanks to all.

Keith.
Mar 27 '08 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

15
by: U N Me | last post by:
I have a continuous form that lists records from a query. One of the columns is an amount field, In the footer of the form I place a text box that displays the total (sum) of the amount. The...
5
by: Stefan | last post by:
Okay, I'm confused. The code below produces no errors and prints -1 for the RecordCount. I run it on a local Access DB. I ran the query in Access and it returns correctly one field with one row...
6
by: Nicolae Fieraru | last post by:
Hi All, I have a query, Select Count(BoolField) from tblMyTable, Where BoolField = true. If I run the query by itself, it returns the number of true records I want to use the result of that...
10
by: Deano | last post by:
Hi, I feel I have a good learning opportunity here. I have been asked to knock up a simple db for managing lettings of rooms and halls. I'm confident about the design but want to make it...
4
by: don.fleming | last post by:
Hi folks: Am doing a VBA SQL Select stmt with multiple rows found and not getting RecordCount > 1. I've verified there are multiple rows found by copying my SQL stmt from Debug window and pasting...
22
by: MP | last post by:
vb6,ado,mdb,win2k i pass the sql string to the .Execute method on the open connection to Table_Name(const) db table fwiw (the connection opened via class wrapper:) msConnString = "Data Source="...
5
by: noLoveLusT | last post by:
hi everyone i am very very new to the sql server (2 days actually and ) so far i learned creating SPs etc but couldnt workout how to get return value from my prodecure my sp as follows...
5
by: Manjiri | last post by:
Hello Everybody... Here i have the program which prints how many number of times the element appears in the array.... The code is as follows... #include<iostream.h> class Count
1
by: Mike Lester | last post by:
I have a need for a stored procedure to return a recordset AND an output parameter that contains the count of records in the recordset. I can get either but not both. (ie. if there is a select...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.