Good Morning World:
I have written the following code: -
'Gives the user to delete and replace all records for a specified parameter
-
-
LockSQL = "SELECT * FROM tblStaticAllForecast WHERE" & _
-
" DivisionIDFK = " & Val(Me.cboDivision.Value)
-
-
Dim rst As DAO.Recordset
-
Set rst = CurrentDb.OpenRecordset(LockSQL)
-
recordexists = rst.RecordCount
-
-
MsgBox "The number of records you are about to delete is " & recordexists & "." & _
-
" Click the ok button to proceed", vbOKCancel, vbDefaultButton2
-
-
-
If Nz(recordexists, 0) > 0 Then
-
-
'code will delete the records that the user has selected..............
-
-
End If
-
End If
-
End Sub
-
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.
7 8110
On Line 4, why are you using the Val() function?
Regards,
Scott
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
I solve it: -
rst.MoveLast 'Move to last record
-
recordexists = rst.RecordCount
-
Thanks,
Keith.
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
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.
Good Morning World:
I have written the following code: -
'Gives the user to delete and replace all records for a specified parameter
-
-
LockSQL = "SELECT * FROM tblStaticAllForecast WHERE" & _
-
" DivisionIDFK = " & Val(Me.cboDivision.Value)
-
-
Dim rst As DAO.Recordset
-
Set rst = CurrentDb.OpenRecordset(LockSQL)
-
recordexists = rst.RecordCount
-
-
MsgBox "The number of records you are about to delete is " & recordexists & "." & _
-
" Click the ok button to proceed", vbOKCancel, vbDefaultButton2
-
-
-
If Nz(recordexists, 0) > 0 Then
-
-
'code will delete the records that the user has selected..............
-
-
End If
-
End If
-
End Sub
-
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: -
Dim rst As DAO.Recordset
-
-
Set rst = CurrentDb.OpenRecordset(LockSQL)
-
-
rst.MoveLast
-
rst.MoveFirst
-
-
'Now, will return the correct Record Cound
-
recordexists = rst.RecordCount
Thanks:
I did that and solved my error issue on a zero account: -
Dim rst As DAO.Recordset
-
Set rst = CurrentDb.OpenRecordset(LockSQL)
-
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, vbDefaultButton2) = vbOK Then
-
-
If Nz(recordexists, 0) > 0 Then
-
-
'code will delete the records that the user has selected.
-
Thanks to all.
Keith.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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="...
|
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...
|
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
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
|
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...
| |