473,324 Members | 2,166 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,324 software developers and data experts.

How to delete all records found in a DAO recordset

kcdoell
230 100+
Hello:

I am trying to write a code that will delete all records found in my DAO recordset Below is the code I have so far:

Expand|Select|Wrap|Line Numbers
  1. 'Procdure to give the user the ability to delete all records
  2. 'for a predefined recordset from the tblStaticAllForecast table
  3.  
  4. LockSQL = "SELECT * FROM tblStaticAllForecast WHERE" & _
  5.         " DivisionIDFK = " & Val(Me.cboDivision.Value) & _
  6.         " And WrkRegIDFK = " & Val(Me.cboWrkReg.Value) & _
  7.         " And CreditRegIDFK = " & Val(Me.cboCreditReg.Value) & _
  8.         " And YearID = " & Val(Me.CboYear.Value) & _
  9.         " And MonthID = " & Val(Me.CboMonth.Value) & _
  10.         " And FWeek = " & Val(Me.cboWeek.Value)
  11.  
  12. Dim rst As DAO.Recordset
  13. Set rst = CurrentDb.OpenRecordset(LockSQL)
  14. recordexists = rst.RecordCount
  15.  
  16. 'If no records are found
  17.     If recordexists = 0 Then
  18.         MsgBox "There are no records to delete."
  19.             Else
  20.  
  21.     rst.MoveLast        'Move to last record
  22.     rst.MoveFirst        'Move to First record
  23.  
  24.         If MsgBox("The number of records you are about to delete is " & recordexists & "." & _
  25.         " Click the ok button to proceed", vbOKCancel, vbDefaultButton2) = vbOK Then
  26.  
  27. 'code will delete the records that the user has selected.
  28.  
  29.  
  30.    End If
  31.       End If
  32.  
  33. End Sub
  34.  
  35.  
Is there a quick code that would do this?

Keith.
Mar 27 '08 #1
5 14256
PianoMan64
374 Expert 256MB
Hello:

I am trying to write a code that will delete all records found in my DAO recordset Below is the code I have so far:

Expand|Select|Wrap|Line Numbers
  1. 'Procdure to give the user the ability to delete all records
  2. 'for a predefined recordset from the tblStaticAllForecast table
  3.  
  4. LockSQL = "SELECT * FROM tblStaticAllForecast WHERE" & _
  5.         " DivisionIDFK = " & Val(Me.cboDivision.Value) & _
  6.         " And WrkRegIDFK = " & Val(Me.cboWrkReg.Value) & _
  7.         " And CreditRegIDFK = " & Val(Me.cboCreditReg.Value) & _
  8.         " And YearID = " & Val(Me.CboYear.Value) & _
  9.         " And MonthID = " & Val(Me.CboMonth.Value) & _
  10.         " And FWeek = " & Val(Me.cboWeek.Value)
  11.  
  12. Dim rst As DAO.Recordset
  13. Set rst = CurrentDb.OpenRecordset(LockSQL)
  14. recordexists = rst.RecordCount
  15.  
  16. 'If no records are found
  17.     If recordexists = 0 Then
  18.         MsgBox "There are no records to delete."
  19.             Else
  20.  
  21.     rst.MoveLast        'Move to last record
  22.     rst.MoveFirst        'Move to First record
  23.  
  24.         If MsgBox("The number of records you are about to delete is " & recordexists & "." & _
  25.         " Click the ok button to proceed", vbOKCancel, vbDefaultButton2) = vbOK Then
  26.  
  27. 'code will delete the records that the user has selected.
  28.  
  29.  
  30.    End If
  31.       End If
  32.  
  33. End Sub
  34.  
  35.  
Is there a quick code that would do this?

Keith.
Are you simply wanting to delete all Records within the criteria of what you're deleting?

If so, once you established the connection, you can simply add a SQL Statement that will delete all the records that have been selected from you're conditional statement.

example

Expand|Select|Wrap|Line Numbers
  1.  
  2. DoCmd.RunSQL ("DELETE * FROM tblStaticAllForecast WHERE" & _
  3.         " DivisionIDFK = " & Val(Me.cboDivision.Value) & _
  4.         " And WrkRegIDFK = " & Val(Me.cboWrkReg.Value) & _
  5.         " And CreditRegIDFK = " & Val(Me.cboCreditReg.Value) & _
  6.         " And YearID = " & Val(Me.CboYear.Value) & _
  7.         " And MonthID = " & Val(Me.CboMonth.Value) & _
  8.         " And FWeek = " & Val(Me.cboWeek.Value))
  9.  
Mar 29 '08 #2
ADezii
8,834 Expert 8TB
Are you simply wanting to delete all Records within the criteria of what you're deleting?

If so, once you established the connection, you can simply add a SQL Statement that will delete all the records that have been selected from you're conditional statement.

example

Expand|Select|Wrap|Line Numbers
  1.  
  2. DoCmd.RunSQL ("DELETE * FROM tblStaticAllForecast WHERE" & _
  3.         " DivisionIDFK = " & Val(Me.cboDivision.Value) & _
  4.         " And WrkRegIDFK = " & Val(Me.cboWrkReg.Value) & _
  5.         " And CreditRegIDFK = " & Val(Me.cboCreditReg.Value) & _
  6.         " And YearID = " & Val(Me.CboYear.Value) & _
  7.         " And MonthID = " & Val(Me.CboMonth.Value) & _
  8.         " And FWeek = " & Val(Me.cboWeek.Value))
  9.  
Hello Pianoman, there is actually a much easier method:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings False
  2.   DoCmd.RunSQL Replace(LockSQL, "Select", "Delete")
  3. DoCmd.SetWarnings True
Mar 29 '08 #3
kcdoell
230 100+
I was away for a couple days and did not have access to the forum. I guess there is more than one way to skin a cat... Because I was in "Loop" mode, I came up with this solution:

Expand|Select|Wrap|Line Numbers
  1. 'Delete the records that the user has selected.
  2.  
  3.     With rst
  4.  
  5.         .Delete
  6.  
  7.     End With
  8.  
  9. 'Check to make sure that at least one record exists in the recordsert
  10.  
  11. If (rst.RecordCount > 0) Then
  12.  
  13.     rst.MoveFirst ' Start deletion from first record
  14.  
  15. 'Delete one record at a time using a do while loop
  16.  
  17.          Do While Not rst.EOF
  18.             rst.Delete
  19.             rst.MoveNext
  20.          Loop
  21.     End If
  22.  
  23.     MsgBox "Records have been deleted.", vbInformation, "Message"
  24.  
  25. 'Close the recordset
  26.      End If
  27.         End If
  28.             End If
  29. End Sub
  30.  
Thanks for the ideas and help. By the way, I like the cleaner look of the other ideas.....

Keith.
Apr 1 '08 #4
kcdoell
230 100+
Hello Pianoman, there is actually a much easier method:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings False
  2.   DoCmd.RunSQL Replace(LockSQL, "Select", "Delete")
  3. DoCmd.SetWarnings True
ADezii:

How does your method work. Is your code just simply swapping out the word "Select" for "Delete" in my SQL?

The reason I ask is that I am thinking of using it on something else I need to do. That is to say where the records have to append to a table; I could use the same method by swapping out the word "Select" for "Insert" in my SQL?

What do you think?

Keith.
Apr 1 '08 #5
ADezii
8,834 Expert 8TB
ADezii:

How does your method work. Is your code just simply swapping out the word "Select" for "Delete" in my SQL?

The reason I ask is that I am thinking of using it on something else I need to do. That is to say where the records have to append to a table; I could use the same method by swapping out the word "Select" for "Insert" in my SQL?

What do you think?

Keith.
How does your method work. Is your code just simply swapping out the word "Select" for "Delete" in my SQL?
You hit the nail on the head, since the SQL is exactly the same except for these 2 Keywords, Replace() simply substitutes Delete for Select in the Statement.
Apr 1 '08 #6

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

Similar topics

8
by: Steve | last post by:
I have several pairs of synchronized subforms in an application. I have a Delete button for each pair that uses the following code or similar to delete a record in the second subform: ...
5
by: hpi | last post by:
Hello, I have a table : Batch It contains fields batchnummer : Number (Long Integer) datum : Date/Time status : Number (Long Integer) nr_records : Number (Long Integer)
13
by: Jan | last post by:
Hi I have a database that I use to keep track of the sales promotions that we send to companies. I normally send a mailing based on a subset of the companies in the database (found using the...
6
by: paulwilliamsonremove | last post by:
Hi, I have two queries: "qryHistoryPersonIDs" that just contains the "personID" numeric field, and "qryDonations" that just contains the "personID" field, and a date field ("dDonationDate"). ...
5
by: tony010409020622 | last post by:
I just spent 4 months taking a dotnet class where i learned very little. One of the things I did not learn is this: What are the dotnet equivilents of commands such as: Adodc1.Recordset.AddNew...
3
by: igendreau | last post by:
I'm trying to clean up a database of mine, and I need to convert some old DAO code over to ADO. When I was using DAO, I had no problem running this script and deleting the record using rs.Delete. ...
4
by: felicia | last post by:
Hi All, Below is my code to delete records: adodcAllEntries.Recordset.MoveFirst Do While (adodcAllEntries.Recordset.EOF = False) If adodcAllEntries.Recordset.Fields(0) = selected_id Then...
7
by: AccessHunter | last post by:
I am using the following code to find and delete records in a table. The logic will go through each record and if a duplicate row is found will delete it. I ran this code and it worked the first...
2
kcdoell
by: kcdoell | last post by:
Hello I have a code where I want to delete the records that are found in my DAO recordset. I took a stab at this for the first time and got it to work but it is only delete one record at a time. If...
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...
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: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
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: 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...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.