I'm working on building a database in Access 2013, and I'm coming across an error in a subroutine which checks to see if there are duplicate entries in the field PartNumbers (and marks a True/False field as true if an entry is a duplicate). - Public Sub CheckDupes()
-
'Create variables; myNum and numCheck refer to the value in the 'PartNumbers' field
-
'cur- first- and lastRec refer to record ID
-
Dim RecSet As ADODB.Recordset
-
Dim myNum As String
-
Dim numCheck As String
-
Dim curRec As Long
-
Dim firstRec As Long
-
Dim lastRec As Long
-
Dim rst As Object
-
Dim cn As ADODB.Connection
-
'Set object variables; rst is only used to find the number of records
-
Set RecSet = New ADODB.Recordset
-
Set rst = Me.RecordsetClone
-
Set cn = New ADODB.Connection
-
'Open connection, open RecSet, set lastRec
-
cn.Open CurrentProject.Connection
-
Call RecSet.Open("tblIandP", cn, adOpenStatic, adLockOptimistic)
-
rst.MoveLast
-
lastRec = rst.RecordCount - 1
-
'Set starting values for first- curRec to avoid skipping loops
-
curRec = 3
-
firstRec = 1
-
'Until last record, get part num and ID
-
Do While firstRec < lastRec
-
myNum = RecSet.Fields.Item(1).Value
-
firstRec = RecSet.Fields.Item(0).Value
-
'Move to the last record so the next loop can search backwards
-
RecSet.MoveLast
-
'Set curRec to avoid skipping inner loop
-
curRec = RecSet.Fields.Item(0).Value
-
'Until firstRec, get part num and ID
-
Do While curRec > firstRec + 1
-
numCheck = RecSet.Fields.Item(1).Value
-
curRec = RecSet.Fields.Item(0).Value
-
'If part nums are same, change Duplicate? value to true
-
If numCheck = myNum Then
-
Call RecSet.Update("Duplicate?", True)
-
End If
-
'Move up the list
-
RecSet.MovePrevious
-
Loop
-
'Move to the next record to be checked
-
RecSet.MoveNext
-
Loop
-
End Sub
I'm getting Run-Time Error '3201': "Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record." This error occurs on line 25 after 2 loops (curRec = 4, firstRec = 2 - firstRec is about to be updated). The code works fine for the section it runs (records 2 and 3 are duplicates), but there are over 3.5k records. I'm not entirely sure why it's hitting EOF with the precautions I've taken to avoid skipping the inner loop. I've also tried replacing the ADODB Recordset with a regular one, and get the same results.
Thank you in advance for your help!
Hey RadioWriter,
Off the top of my head, you may want to .MoveLast before rst.RecordCount as I don't think RecordCount is 100% reliable until the recordset is navigated to the end of the Recordset.
Also, how do you know RecSet.Fields.Item(0).Value will be sequential when you are iterating through the records. If your recordset isn't ordered on this column I'm pretty sure it will break.
So I need to ask, is this Code something you inherited or something you are writing? Because if you are writing it, I think there is a less error prone way to go about this.
4 3471
Hey RadioWriter,
Off the top of my head, you may want to .MoveLast before rst.RecordCount as I don't think RecordCount is 100% reliable until the recordset is navigated to the end of the Recordset.
Also, how do you know RecSet.Fields.Item(0).Value will be sequential when you are iterating through the records. If your recordset isn't ordered on this column I'm pretty sure it will break.
So I need to ask, is this Code something you inherited or something you are writing? Because if you are writing it, I think there is a less error prone way to go about this.
Edit- Yes, this worked. Very sorry.
Thank you for the help!
Glad you got it going!
You may want to take the Question Mark (?) out of your Fieldnames as it a reserved character in SQL. It may cause you trouble down the road.
Also, another approach you may want to consider for this is to run a single update query against the Records to calculate and update your Duplicates Flag. It might be less complicated and probably quicker as you are executing one statement against the database instead of multiple updates.
I mocked this up, it's not the best approach as it's using a Dlookup, but even using a DLookup I have a feeling it will run faster than the looping approach. - Private Sub Command6_Click()
-
Dim sSQL As String
-
sSQL = "UPDATE DuplicatesTest SET "
-
sSQL = sSQL & " Duplicate = "
-
sSQL = sSQL & " (DCount(""ID"",""DuplicatesTest"",""[ID]>"" & [ID] & "" AND [PartNumber]='"" & [PartNumber] & ""'"")>0)"
-
CurrentDb.Execute sSQL
-
End Sub
Well, definitely faster. (Looping clocks in around 1.5 minutes to go down the list and then back up, but the above method takes only 10 seconds.)
Unfortunately, the return is necessary to flag all of the initial entries being checked against. Another field contains prices which need to be compared to find the current/correct one. Once they've been properly updated, the excess entries will be deleted and another method will be used to change the current price/add new entries such that no more duplicates can be created.
Again, thank you SO much for your help. It is very much appreciated.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: vasilijepetkovic |
last post by:
Hello All,
I have a problem with the program that should generate x number of txt
files (x is the number of records in the file datafile.txt).
Once I execute the program (see below) only one...
|
by: ern |
last post by:
I have a program that runs scripts. If the user types "script
myScript.dat" the program will grab commands from the text file, verify
correctness, and begin executing the script UNTIL...
I need...
|
by: Rico |
last post by:
Hi All,
Just wondering, in vb code, how to if the last record on a cascading form is
the current record?
Thanks!
|
by: Kenneth |
last post by:
Ok, so logically this code seems to make sense but for some reason it
reads the last record twice before hitting eof. Is ifstream.eof()
implementation dependent?
void LoadFile(ifstream &File,...
|
by: Bhujanga |
last post by:
I want to scroll through records on an open form and take certain actions based on various criteria. So I want to have a loop such as this:
Do While Not Last Record <----- ?
....actions....
...
|
by: clintw |
last post by:
Hi,
I need someone to please clarify the meaning of the following ASP lines. Having trouble following ASP's structure and not finding relevant explanations on web.
1. While ((Repeat1__numRows...
|
by: anfetienne |
last post by:
hi im back again.......i have a code to create strings and save it within a text file to pass variables to flash. im using the string format below.
...
|
by: LeighW |
last post by:
Hello,
I used Allen Browne's method of assigning default values from the last record which is especially helpful when adding a new record to filtered records. Using his method I had to create a...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |