Modules & VBA :: Loop Through Record Set And Delete Duplicates Items?
Oct 20, 2014
If there a way i can remove duplicates from an email list? I pull these emails from a recordset. But i email may appear more than once and it doesn't look good e.g
johndoe@mail.com;johndoe@mail.com;jo...hndoe@mail.com.
I'd like to remove the duplicated email, if its possible. Code below.
Code:
Set rs = CurrentDb.OpenRecordset("select * from query")
With objMailItem
If rs.RecordCount > 0 Then
rs.MoveFirst
Do
If Not IsNull(rs![email]) Then
vRecipientList = vRecipientList & rs![email] & ";"
rs.MoveNext
Else
rs.MoveNext
End If
Loop Until rs.EOF
.To = vRecipientList
View Replies
ADVERTISEMENT
May 13, 2015
I am trying to loop through a table and count the duplicate records only once. Please see the attached file.
1) In the test table, I want to loop through all pt_accts.
2) In the countable column, if an acct repeats, I essentially just want to put "1" in one of the records. For the non-repeating accts, putting in a 1 is sufficient. Basically, I just don't want to count the duplicate ones multiple times.
In the example, acct "aaa" repeats, but there is just one "1" in the countable column. "eee" repeats as well, but there is just one "1" and the rest is 0.
Is this possible via VBA?
View 14 Replies
View Related
Apr 30, 2015
Is it possible to run a loop through each record in query, but refresh the query in between loops?
The query I am looping needs to be updated before moving to the next record.
t can't figure out how to get the query to refresh before looping.
View 3 Replies
View Related
Jan 9, 2014
I have succeeded with retrieving subTable data from a One to many relationship, however, I'm stuck in an infinite loop processing the first record of the subtable and not moving to the next one.
Dim db As DAO.Database
Dim rstLandData As DAO.Recordset
Set db = CurrentDb
Set rstLandData = db.OpenRecordset("qryLandData", dbOpenDynaset)
Dim strLandType As String
[Code] ....
View 7 Replies
View Related
May 8, 2014
I am attempting to use 2 fields from a query to supply the Top and Left Properties of a Collection of Rectangle Controls on my form. The purpose of this is to display the locations on a map of "Spots" in a haunted house. The query that I am using shows the spots that have been pre-tagged with the location of where they belong on the map (currently the query has only 24 tagged spots). On the actual form I have rectangle controls (control type acRectangle) with their visible property set to False by default, named box1 through box25 (there will be more eventually, as I am just working with this test group).
I started with the following code, yet it stops after (correctly) placing the first spot on the map (please see the attached jpg):
Code:
Private Sub Form_Open(Cancel As Integer)
Dim ctl As Control
Dim db As DAO.Database
Dim rst As DAO.Recordset
[Code]....
I'm sure I need to have 'Loop' in there somewhere, but I am not sure exactly where to place it, or if another line is also needed.
View 10 Replies
View Related
Mar 13, 2014
I found this code and have substituted parameters to suit my own needs however the loop is not working. Only the first record in my recordset (which is a test recordset of only 3 records) is being updated.
Also, for testing only, the edit or update being applied is trivial: Description = "WHITE RESIN". If i can get the loop to work I want to substitute higher functionality to the module.
Private Sub Update_Click()
Dim dbs As DAO.Database
Dim rsQuery As DAO.Recordset
Set dbs = CurrentDb
Set rsQuery = dbs.OpenRecordset("qryRmResin", dbOpenDynaset)
[Code] .....
View 14 Replies
View Related
Sep 11, 2013
I have a table with a multi-select listbox as one of the fields. I want to loop through the recordset (table) and changes the listbox selections for each record.
To go into a little more detail, the table (tblEmail) has a field (Label) that is a multi-select listbox. The listbox pulls from another table (tblLabel). I want to loop through records in tblEmail and edit/change the Label(s) for each email though VBA.
I've tried doing something like this:
Code:
With rst
.MoveFirst
Do Until rst.EOF
.Edit
!Label.Selected(0) = True
.Update
Loop
End With
However I get an error that says "Run-Time Error '438' Object doesn't support this property or method" ...
View 2 Replies
View Related
Jul 29, 2015
I have a part table summary with various quantities 1-1000 and want to create a new table where part number repeats with quantity of 1 corresponding back to the sum. If part 123456=20 then this would repeat 20 x and each record Qty=1
Start with summary
--================
Part Qty
111000 2
222000 3
End result all Qty=1
--================
Part Qty
111000 1
111000 1
222000 1
222000 1
222000 1
--================
I stared with a loop and was able to get an append query to work referring to the quantity value (3) for one record from tbl_temp to tbl_main, but not really sure how to advance through many records.
For n = 1 To [Forms]![MainScreen]![Text7]
DoCmd.OpenQuery "qry_Update_Qty"
'DoCmd.GoToRecord , , acNewRec
Next n
maybe a do while or some other approach?
View 3 Replies
View Related
Sep 20, 2013
I would like to do a loop but never done one before, basically i want to copy the current record by the number of times specified in a quantity field
So if the quantity field in the record says 5 then copy that record 5 times (I have managed to create the copy and paste code but dont know how to make it do it 5 times
Code:
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdPaste
View 2 Replies
View Related
Jun 28, 2013
When we browse through records in a subform we store the records in the database.When we want to delete a records for example the third record from the five records always the first records will be deleted. How can we delete the records where the cursor is at? When we are at the third record and press the delete button the third record from the list in the subform should be deleted.
Code:
Option Compare Database
Dim FocusBln As Boolean
Private Sub Identificeer()
Me.[Datum Aangemaakt].Visible = True
Me.[Datum Aangemaakt].SetFocus
If Me.[Datum Aangemaakt].Text = "" Then
[code]...
View 11 Replies
View Related
May 2, 2014
I've been looking everywhere to find a better way of finding duplicates in a table and then recording and adding another value in that record together.
Let me try to explain better.
example:
I have a table that has 2 columns "Name", "DOB". I would like to find all duplicate "DOB" and add all of the "Name"'s together.
Quote:
Name DOB
bob 19800201
Sam 19761211
Jim 19800201
The output I would like is to have Name = bob & Jim DOB = 19800201.
I've tried using the find duplicate wizard in access but I can't seem to group them together and just to find the duplicates it takes upwards of a minute.
View 1 Replies
View Related
Sep 23, 2005
I ran the Find Duplicates Query Wizard and it found multiple duplicates. Now how do I go about deleting these duplicates?
View 1 Replies
View Related
May 11, 2015
I have hundreds of duplicates in my database and i've tried using logic to delete them but there are still thousands left over. I know duplicates can be removed by using VBA, how to use VBA IDE.
View 8 Replies
View Related
May 8, 2014
I have a form that opens up and fills in all of the Orders Table when it opens.
I then have a subform that is used to fill in the order details.
Currently if they open the form and then close it, it creates a record in the order table. I want to be able to delete this record if no information has been filled into the subform?
View 7 Replies
View Related
May 16, 2007
Hi Guys,
I wanted to delete some duplicate records off of a table.
I went through the steps of copying the structure of a table to a new table then setting the Primary Key as what I wanted to be unduplicated. In my case, I have the Primary Key setup as the Account SSN and the Persons ID. This way it would be OK to have more than 1 SSN as long as I dont have more than 1 SSN and Person ID together.
I have 430 records on my table, when I run the find duplicate query, I see 22 lines (11 duplicates). I went through the table and confirmed that this is correct.
When I paste the original table to the PK table, it copies 392 records. Basically, it didn't copy 27 records it should have. I hunted down the 27 and they were not duplicates, they only had 1 SSN + Person ID.
So to make it short, the easy way to kill duplicates for some reason doesnt work for me.
Is there another way to do this? I was thinking of using the Find Duplicates Query and somehow turning it into a Delete Query, but I would only want it to Delete 1 of each it finds, not all of them.
Once again, thanks for the help.
View 2 Replies
View Related
Jun 5, 2006
I am a rookie! Have a table with names addresses and years. Want to wittle it down to one name and address per. Ran totals query. Did delete duplicates. When I hit view datasheet, it shows the right info to be deleted, but when I try to run the query, it comes back with "Could not delete from specified tables." When I hit the help button, it says Error 3086 and tells me about read only. I have checked permissions, and I have full permissions. Does anyone have any idea what I am doing wrong?
View 1 Replies
View Related
Sep 24, 2013
I'd like to override the default behaviour for deleting records in a form.Specifically, I want to build my own custom delete procedure so that when the user presses the Del button, my code fires to complete the deletion of the selected record(s). In order to do that, I'd set Allow Deletions = No for that form. I'd also want to code the KeyDown event for the Del key so that if record(s) are selected, my custom delete code fires, else the default behavior for the Del key happens.I'm primarily interested in how I might code the KeyDown event.
View 12 Replies
View Related
Jul 2, 2015
I have a form based on Table A. When a yes/no tick is clicked in the form, I want to delete a record from Table B. The tables are joined by ID numbers.
View 1 Replies
View Related
Oct 10, 2013
Table Name: Admin
Field Name: userid, admin (Y/N), Password, ConPw, PasswordReset (Y/n), Createdby
Trying to run a vba to find and delete records that was "createdby" the current user. Enviorn("username")
View 2 Replies
View Related
Jul 12, 2015
I am building a db for reservations for my limo company. I want to have a cmd button that verifies the user to make sure she wants to delete a run. This is what I have so far:
Private Sub cmdDeleteRun_Click()
Dim Response
Response = msgbox("Are you sure you want to delete this run?", vbYesNoCancel + vbCritical, "Really delete run?")
If Response = vbYes Then
End Sub
I don't know what I am missing for the cmd to actually delete it.
View 3 Replies
View Related
Apr 3, 2006
I think this is an easy one...
I have two tables - one is a Master. I want to delete the records in the Master if they are in the second table. Here's my SQL and I can't get it to work:
DELETE from TestMaster
INNER JOIN on JoinedDupList
ON TestMaster.ID=JoinedDupList.ID
WHERE JoinedDupList.ID = TestMaster.ID
I'm being told I need to define the table to delete from... I thought I defined it.
Thanks!
View 2 Replies
View Related
Feb 10, 2014
I need to delete duplicate rows (or create new table without them) but the duplicate is not the entire row of data.
Date Time LeagueMatchingId League HomeMatchingId HomeName AwayMatchingId AwayName
HomeScore AwayScore HomeRedCardCount AwayRedCardCount FTOU FTOverOdds FTUnderOdds
20121202 10/02/2014 20:34:02 17 FRANCE LIGUE 1 147 Lorient 154 Toulouse 0 0 0 0 1.5 0.88 -0.98
20121202 10/02/2014 20:34:02 17 FRANCE LIGUE 1 147 Lorient 154 Toulouse 0 0 0 0 1.5 0.88 -0.98
[Code] .....
In this example I am only interested in the first and last rows, since the ones in between have the same data in the last few columns. They are not true duplicates since the time stamp for each is different.
Currently thinking I need to compare each and every row to the prior row in VBA and delete if criteria match.
View 4 Replies
View Related
Jan 12, 2014
For school I have to make a application in access how to delete a selected record in the table in a subform by using a button. The subform is in the main form and the button is also in the main form
View 14 Replies
View Related
Jan 7, 2015
What is the correct syntax that would delete a file when the record is deleted. The file's path is listed in a record field, MailLocation. Every time I try this code, I receive an error!
I've tried the below, and number of iterations, including calling the killfile differently (me![MailLocation], me.MailLocation).
Code:
Private Sub Form_AfterDelConfirm(Status As Integer)
Dim KillFile As String
KillFile = me!MailLocation
Kill KillFile
End Sub
View 6 Replies
View Related
Jun 27, 2013
I have created a form and need to be able to delete employees from a table. I built a command button using the wizard and this is the current vb code-
Code:
Private Sub cmdDeleteEmployee_Click()
On Error GoTo Err_cmdDeleteEmployee_Click
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
Exit_cmdDeleteEmployee_Click:
Exit Sub
Err_cmdDeleteEmployee_Click:
MsgBox Err.Description
Resume Exit_cmdDeleteEmployee_Click
End Sub
But when I click on the button I get a message saying it would create duplicate data in the table.
View 13 Replies
View Related
Aug 13, 2013
I have a table that has multiple records (count >1). I used the find duplicate records and then made it a delete query, however, this resulted in deleting all the records that had count >1. I need to retain distinct record, and delete the extra records. Using select distinct.. I do not want to use VBA to achieve this, but at the same time be able to incorporate the steps in a module that would execute the queries in a sequential order and achieve the same results.
View 6 Replies
View Related
