Strange behaviour with 2 recordsets

M

Matiyapak

Hi everybody,

I have a large MS Access Database in which I write regularly words that are
not found in dictionaries.

I wanted to cleanup this Database and get rid of double entries, so I tried
to write a VB program to achieve this (see the program code at the end of
this email).

But when I run this VB program, something strange happens.

The database has four fields: "English, Type, Trad, Remarks"

In the program I wrote 2 loops:

An outer loop (Do Until rstWholeTable.EOF = True) which loops trough all the
records.
An inner loop (Do Until rstDoubleTable.EOF = True) which starts at the
currend record +1 to avoid reading the current record by both loops.
Everytyme the inner loop is finished, I set the outer loop to equal the
inner loop to cance the EOF = TRUE.

When I disable the inner loop (Do Until rstDoubleTable.EOF = True) and run
the program, all the records are tested.
When I disable the outer loop (Do Until rstWholeTable.EOF = True) and run
the program, all the records are tested.
When I enable both loops and run the program, the outer loop is only
processed 2 times, and then EOF becomes TRUE and the program ends.

What is wrong with the program?

TIA,

Matiyapak K.

***********************************************

Sub Double()

Dim dbsDictionary As Database
Dim rstWholeTable As Recordset
Dim rstDoubleTable As Recordset
Dim strTestEng As String
Dim strTestEng1 As String

Set dbDictionary = OpenDatabase("Woorden.mdb")
Set rstWholeTable = dbDictionary.OpenRecordset("Dictionary")
Set rstDoubleTable = dbDictionary.OpenRecordset("Dictionary")

On Error Resume Next
With rstWholeTable
rstWholeTable.MoveLast
rstWholeTable.MoveFirst
End With

Do Until rstWholeTable.EOF = True
strTestEng = rstWholeTable.Fields("English")
strTestType = rstWholeTable.Fields("Type")
strTestTrad = rstWholeTable.Fields("Trad")
strTestRemarks = rstWholeTable("Remarks")

Do Until rstDoubleTable.EOF = True
With rstDoubleTable
.MoveNext
strTestEng1 = rstDoubleTable.Fields("English")
strTestType1 = rstDoubleTable.Fields("Type")
strTestTrad1 = rstDoubleTable.Fields("Trad")
strTestRemarks1 = rstDoubleTable("Remarks")

If strTestEng <> strTestEng1 Then GoTo NoMatch
If strTestType <> strTestType1 Then GoTo NoMatch
If strTestTrad <> strTestTrad1 Then GoTo NoMatch
If strTestRemarks <> strTestRemarks1 Then GoTo NoMatch

With rstDoubleTable
.Edit
.Fields("English").Value = ""
.Fields("Type").Value = ""
.Fields("Trad").Value = ""
.Fields("Remarks").Value = ""
.Update
End With
End With
NoMatch:
Loop
rstWholeTable.MoveNext
Set rstDoubleTable = rstWholeTable
Loop

rstWholeTable.Close
rstDoubleTable.Close
dbsDictionary.Close
Set dbDictionary = Nothing
Set rstWholeTable = Nothing
Set rstDoubleTable = Nothing
End Sub
 
G

Guest

Hi,

how do you validate that your inner loop starts with the record of the outer
loop + 1? I think it will be easier to help you, when you paste the relevant
source code here. Everything else seems to be to theoretical.
 
S

SteveS

Matiyapak wrote:
When I enable both loops and run the program, the outer loop is only
processed 2 times, and then EOF becomes TRUE and the program ends.

What is wrong with the program?

TIA,

Matiyapak K.
Click to expand...

<SNIPPED the code>


Here are some problems I noticed.

- You named the Sub with a reserved word (Double).
- You also used a reserved word (TYPE) as the name of a field.
- You are missing a "s" in two lines:

Set dbDictionary = OpenDatabase("Woorden.mdb")
Set dbDictionary = Nothing
^
should be:

Set dbsDictionary = OpenDatabase("Woorden.mdb")
Set dbsDictionary = Nothing

Not really a problem, but when you use WITH...END WITH, you don't need to use
the object name inside the statement.

From Help: "The With statement allows you to perform a series of statements on
a specified object without requalifying the name of the object."

Instead of this:
With rstWholeTable
rstWholeTable.MoveLast
rstWholeTable.MoveFirst
End With
Click to expand...

use this:

With rstWholeTable
.MoveLast
.MoveFirst
End With



From what I have read in the NGs, when you open a recordset based on a table
(or a Sql Select statement), you don't know what order the records are in. So,
opening two recordsets based on the same table could be in different orders.

When you execute this:
Set rstDoubleTable = rstWholeTable
Click to expand...

I think you are trying to set the current records (bookmarks) the same, but I
don't think you can do it that way. You make the objects identical, but not
necessarily the current records.




Here is a different approach.

I would open a recordset, looping thru all the records.

For each record where the field "English" is not null/empty, open a recordset.
IF the recordset record count is greater than 1, set the fields to "" in all
but one record.


Below is the (**UNTESTED**) code. One question...couldn't you just compare the
"English" fields to determine if there are duplicate records? (not all four fields)

Here is the code... try this on a backup ... you have been warned ;)
(watch for line wrap....)

'***** beg code ***************
'couldn't use Sub Double()

Sub RemoveDuplicateWords()

Dim dbsDictionary As Database
Dim rstWhole As Recordset
Dim rstDouble As Recordset

Dim strSQL As String
Dim strSelect As String
Dim strstrWhere As String

Dim strTestEng As String
Dim strTestType As String
Dim strTestTrad As String
Dim strTestRemarks As String

Set dbsDictionary = OpenDatabase("Woorden.mdb")
Set rstWhole = dbDictionary.OpenRecordset("Dictionary")

On Error Resume Next

'first part of the SQL
strSelect = "Select English, Type, Trad, Remarks from Dictionary"

With rstWhole
.MoveLast
.MoveFirst
End With

Do Until rstWhole.EOF
'appending an empty string removes nulls
strTestEng = rstWhole.Fields("English") & ""
strTestType = rstWhole.Fields("Type") & ""
strTestTrad = rstWhole.Fields("Trad") & ""
strTestRemarks = rstWhole.Fields("Remarks") & ""

'only check field that is not empty
If Len(Trim(strTestEng)) > 0 Then
'second part of SQL
strWhere = " Where [English]= '" & strTestEng & _
"' And [Type] = '" & strTestType & _
"' And [Trad] = '" & strTestTrad & _
"' And [Remarks] = '" & strTestRemarks & "'"

strSQL = strSelect & strWhere
Set rstDouble = dbDictionary.OpenRecordset(strSQL)
With rstDouble
.MoveLast
If .RecordCount > 1 Then
.MoveFirst
' clear the fields for (record count - 1) records
For i = 1 To .RecordCount - 1
.Edit
.Fields("English") = ""
.Fields("Type") = ""
.Fields("Trad") = ""
.Fields("Remarks") = ""
.Update
.MoveNext
Next i
End If
.Close
End With
End If
rstWhole.MoveNext
Loop


' here is where you could delete records where the field "English" =""
' use dbsDictionary.Execute "Delete From rstWhole Where [English] = ''",
dbFailOnError

rstWhole.Close
dbsDictionary.Close
Set dbDictionary = Nothing
Set rstWhole = Nothing
Set rstDouble = Nothing

End Sub
'***** end code ***************

HTH
 
G

Guest

Hi,

Steve is right, I should always read questions till the end. ;-)
--
Regards

Oliver Seiffert


SteveS said:
Matiyapak wrote:
When I enable both loops and run the program, the outer loop is only
processed 2 times, and then EOF becomes TRUE and the program ends.

What is wrong with the program?

TIA,

Matiyapak K.
Click to expand...

<SNIPPED the code>


Here are some problems I noticed.

- You named the Sub with a reserved word (Double).
- You also used a reserved word (TYPE) as the name of a field.
- You are missing a "s" in two lines:

Set dbDictionary = OpenDatabase("Woorden.mdb")
Set dbDictionary = Nothing
^
should be:

Set dbsDictionary = OpenDatabase("Woorden.mdb")
Set dbsDictionary = Nothing

Not really a problem, but when you use WITH...END WITH, you don't need to use
the object name inside the statement.

From Help: "The With statement allows you to perform a series of statements on
a specified object without requalifying the name of the object."

Instead of this:
With rstWholeTable
rstWholeTable.MoveLast
rstWholeTable.MoveFirst
End With
Click to expand...

use this:

With rstWholeTable
.MoveLast
.MoveFirst
End With



From what I have read in the NGs, when you open a recordset based on a table
(or a Sql Select statement), you don't know what order the records are in. So,
opening two recordsets based on the same table could be in different orders.

When you execute this:
Set rstDoubleTable = rstWholeTable
Click to expand...

I think you are trying to set the current records (bookmarks) the same, but I
don't think you can do it that way. You make the objects identical, but not
necessarily the current records.




Here is a different approach.

I would open a recordset, looping thru all the records.

For each record where the field "English" is not null/empty, open a recordset.
IF the recordset record count is greater than 1, set the fields to "" in all
but one record.


Below is the (**UNTESTED**) code. One question...couldn't you just compare the
"English" fields to determine if there are duplicate records? (not all four fields)

Here is the code... try this on a backup ... you have been warned ;)
(watch for line wrap....)

'***** beg code ***************
'couldn't use Sub Double()

Sub RemoveDuplicateWords()

Dim dbsDictionary As Database
Dim rstWhole As Recordset
Dim rstDouble As Recordset

Dim strSQL As String
Dim strSelect As String
Dim strstrWhere As String

Dim strTestEng As String
Dim strTestType As String
Dim strTestTrad As String
Dim strTestRemarks As String

Set dbsDictionary = OpenDatabase("Woorden.mdb")
Set rstWhole = dbDictionary.OpenRecordset("Dictionary")

On Error Resume Next

'first part of the SQL
strSelect = "Select English, Type, Trad, Remarks from Dictionary"

With rstWhole
.MoveLast
.MoveFirst
End With

Do Until rstWhole.EOF
'appending an empty string removes nulls
strTestEng = rstWhole.Fields("English") & ""
strTestType = rstWhole.Fields("Type") & ""
strTestTrad = rstWhole.Fields("Trad") & ""
strTestRemarks = rstWhole.Fields("Remarks") & ""

'only check field that is not empty
If Len(Trim(strTestEng)) > 0 Then
'second part of SQL
strWhere = " Where [English]= '" & strTestEng & _
"' And [Type] = '" & strTestType & _
"' And [Trad] = '" & strTestTrad & _
"' And [Remarks] = '" & strTestRemarks & "'"

strSQL = strSelect & strWhere
Set rstDouble = dbDictionary.OpenRecordset(strSQL)
With rstDouble
.MoveLast
If .RecordCount > 1 Then
.MoveFirst
' clear the fields for (record count - 1) records
For i = 1 To .RecordCount - 1
.Edit
.Fields("English") = ""
.Fields("Type") = ""
.Fields("Trad") = ""
.Fields("Remarks") = ""
.Update
.MoveNext
Next i
End If
.Close
End With
End If
rstWhole.MoveNext
Loop


' here is where you could delete records where the field "English" =""
' use dbsDictionary.Execute "Delete From rstWhole Where [English] = ''",
dbFailOnError

rstWhole.Close
dbsDictionary.Close
Set dbDictionary = Nothing
Set rstWhole = Nothing
Set rstDouble = Nothing

End Sub
'***** end code ***************

HTH
Click to expand...
 
M

Matiyapak

SteveS said:
Matiyapak wrote:


<SNIPPED the code>


Here are some problems I noticed.

- You named the Sub with a reserved word (Double).
- You also used a reserved word (TYPE) as the name of a field.
- You are missing a "s" in two lines:
Set dbDictionary = OpenDatabase("Woorden.mdb")
Set dbDictionary = Nothing
^
should be:

Set dbsDictionary = OpenDatabase("Woorden.mdb")
Set dbsDictionary = Nothing

Not really a problem, but when you use WITH...END WITH, you don't need to
use the object name inside the statement.
Click to expand...


Do you think that that is the cause of the outer loop to quit after 2
passes?

I watched the behaviour of the recordsets in the immediate window, and for
some unknown reason, the "rstWholeTable" is set to "EOF=TRUE" after 2 loops
without having processed any records.
From Help: "The With statement allows you to perform a series of
statements on a specified object without requalifying the name of the
object."

Instead of this:

use this:

With rstWholeTable
.MoveLast
.MoveFirst
End With
Click to expand...

I know that, but I wanted to remove all doubts in what is the cause of this
problem.
From what I have read in the NGs, when you open a recordset based on a
table (or a Sql Select statement), you don't know what order the records
are in. So, opening two recordsets based on the same table could be in
different orders.
When you execute this:
Click to expand...

I use only 1 recordset, the recordset "rstWholetable".
To be sure that "rstdoubleTable" is the same each loop, I copy
"rstWholeTable" in "rstDoubeTable' every inner loop.
I think you are trying to set the current records (bookmarks) the same,
but I don't think you can do it that way. You make the objects identical,
but not necessarily the current records.
Click to expand...

Strange thing is that when I try to run each loop with the other disabled,
the recordsets are processed correcly.
Otherwise I dont see what the use of "recordset" is.
If a recordset is not a recordset, then abolish it altogether.
Here is a different approach.
I would open a recordset, looping thru all the records.

For each record where the field "English" is not null/empty, open a
recordset. IF the recordset record count is greater than 1, set the fields
to "" in all but one record.

Below is the (**UNTESTED**) code. One question...couldn't you just compare
the "English" fields to determine if there are duplicate records? (not all
four fields)
Click to expand...

No, simply because I have multiple similar entries in the field "English",
but in the field "Type" each entry can have a different purpose (verb, noun,
vt, vs, idiom, etc...), the field "Trad" can have different translations for
the English word based on field "type", and finaly the field remarks is
based on the 3 previous fields.

Only when the four fields ar exactlly the same, there is a double entry.
Here is the code... try this on a backup ... you have been warned ;)
Click to expand...

I have made a copy of the database to try the VB-Code on.
Only when it works with 100 % surety, I will run it on the original
database.
Nevertheless, I keep a backup of the original database just in case ......

I will try your code, after the correction on the double "strstr" in "Dim
strstrWhere As String" and give you a feedback.

Thanks,

Matiyapak

(watch for line wrap....)

'***** beg code ***************
'couldn't use Sub Double()

Sub RemoveDuplicateWords()

Dim dbsDictionary As Database
Dim rstWhole As Recordset
Dim rstDouble As Recordset

Dim strSQL As String
Dim strSelect As String
Dim strstrWhere As String

Dim strTestEng As String
Dim strTestType As String
Dim strTestTrad As String
Dim strTestRemarks As String

Set dbsDictionary = OpenDatabase("Woorden.mdb")
Set rstWhole = dbDictionary.OpenRecordset("Dictionary")

On Error Resume Next

'first part of the SQL
strSelect = "Select English, Type, Trad, Remarks from Dictionary"

With rstWhole
.MoveLast
.MoveFirst
End With

Do Until rstWhole.EOF
'appending an empty string removes nulls
strTestEng = rstWhole.Fields("English") & ""
strTestType = rstWhole.Fields("Type") & ""
strTestTrad = rstWhole.Fields("Trad") & ""
strTestRemarks = rstWhole.Fields("Remarks") & ""

'only check field that is not empty
If Len(Trim(strTestEng)) > 0 Then
'second part of SQL
strWhere = " Where [English]= '" & strTestEng & _
"' And [Type] = '" & strTestType & _
"' And [Trad] = '" & strTestTrad & _
"' And [Remarks] = '" & strTestRemarks & "'"

strSQL = strSelect & strWhere
Set rstDouble = dbDictionary.OpenRecordset(strSQL)
With rstDouble
.MoveLast
If .RecordCount > 1 Then
.MoveFirst
' clear the fields for (record count - 1) records
For i = 1 To .RecordCount - 1
.Edit
.Fields("English") = ""
.Fields("Type") = ""
.Fields("Trad") = ""
.Fields("Remarks") = ""
.Update
.MoveNext
Next i
End If
.Close
End With
End If
rstWhole.MoveNext
Loop


' here is where you could delete records where the field "English" =""
' use dbsDictionary.Execute "Delete From rstWhole Where [English] =
''", dbFailOnError

rstWhole.Close
dbsDictionary.Close
Set dbDictionary = Nothing
Set rstWhole = Nothing
Set rstDouble = Nothing

End Sub
'***** end code ***************

HTH
Click to expand...
 
S

SteveS

I use only 1 recordset, the recordset "rstWholetable".
To be sure that "rstdoubleTable" is the same each loop, I copy
"rstWholeTable" in "rstDoubeTable' every inner loop.
Click to expand...


I think this is the cause of the problem.
Strange thing is that when I try to run each loop with the other disabled,
the recordsets are processed correcly.
Otherwise I dont see what the use of "recordset" is.
If a recordset is not a recordset, then abolish it altogether.
Click to expand...

If there are two unordered recordsets opened (on the same table), some is
processing done, then at some point, the inner loop is set equal to the outer
loop, where would you expect the current record pointer to be?

Beats me.... but the inner loop current record pointer is not being set to the
outer current record pointer; The WHOLE inner recordset is being set equal to
the outer recordset.

No, simply because I have multiple similar entries in the field "English",
but in the field "Type" each entry can have a different purpose (verb, noun,
vt, vs, idiom, etc...), the field "Trad" can have different translations for
the English word based on field "type", and finaly the field remarks is
based on the 3 previous fields.

Only when the four fields ar exactlly the same, there is a double entry.
Click to expand...

Aha, makes sense now. I would still change "TYPE" to "WordType" to eliminate
any chance of it being any problem.

I will try your code, after the correction on the double "strstr" in "Dim
strstrWhere As String" and give you a feedback.
Click to expand...

The evils of copy and paste... I thought I caught all of those :)
Good catch.
 
M

Matiyapak

SteveS said:
I think this is the cause of the problem.
Click to expand...

In the immediate wiindow, the code seems to work well, only 2 loops.
If there are two unordered recordsets opened (on the same table), some is
processing done, then at some point, the inner loop is set equal to the
outer loop, where would you expect the current record pointer to be?
Click to expand...

At the same place as the pointer of the outer loop + 1.
Beats me.... but the inner loop current record pointer is not being set
to the outer current record pointer; The WHOLE inner recordset is being
set equal to the outer recordset.
Click to expand...

Thats the intention.

I could leave the code "Set WholeTable=DoubleTable" out if there was a way
to reset the "EOF" after each loop.
The main problem in my code is that after processing the inner loop one
time, the "EOF" is set to "TRUE" and the program skips the code as the
varriable "While not EOF" is "TRUE".
Aha, makes sense now. I would still change "TYPE" to "WordType" to
eliminate any chance of it being any problem.
Click to expand...

Consider that already done, together with the elimination of all the other
possible errors due to the use of names that are reserved to VB.
The evils of copy and paste... I thought I caught all of those :)
Good catch.
Click to expand...

-);

Have a nice weekend Steve,

Matiyapak K.
 
M

Matiyapak

Steve,

I tried your program code today.

The program did generate an error in the declaration:
Set dbsDictionary = OpenDatabase("Woorden.mdb")
Set rstWhole = dbDictionary.OpenRecordset("Dictionary")
Click to expand...

Changed the string "dbDictionary" to "dbsDictionary".

Run again.
A second error was generated at "dbFailOnError".
Disabled this statement by putting an apostrophe in front of the line.

Run again.
No errors thiis time.

Activated the line <dbsDictionary.Execute "Delete From rstWhole Where
[English] = ''">.
The code was run without errors, but no records were deleted, although there
were double entries in the database.

Tried a different approach, and this code works.
It is not state of the are VB programming, but it works..
Everybody who need a VB code to check for doubles can freely adapt the
program to his needs..

See below for the code and some explanation.

Thanks for the help.

Matiyapak K.

***************************************************

In the Database, I added a column with "AutoNumber".
So, I have 5 fields: "Teller, English, WordType, Trad, Remarks".
Then I inserted the code like this:

Sub Repeated()

' Declarations.

Dim dbsDictionary As Database
Dim rstWhole As Recordset
Dim rstDouble As Recordset

Set dbsDictionary = OpenDatabase("Woorden.mdb")
Set rstWhole = dbsDictionary.OpenRecordset("Dictionary")
Set rstDouble = dbsDictionary.OpenRecordset("Dictionary")

' If an error is encountered, go no with the next statement.

On Error Resume Next

' Populate the table.

With rstWhole
.MoveLast
.MoveFirst
End With

' Repeat until the whole table has been processed.
' Take all the fields from one recordset ("Whole").

Do Until rstWhole.EOF = True
strTestTeller = rstWhole.Fields("Teller")
strTestEng = rstWhole.Fields("English")
strTestWordType = rstWhole.Fields("WordType")
strTestTrad = rstWhole.Fields("Trad")
strTestRemarks = rstWhole.Fields("Remarks")

' Open a second recordset.

Set rstDouble = dbsDictionary.OpenRecordset("Dictionary")

' Repeat until the whole table has been processed.
' Take all the fields from the second recordset ("Double").

Do Until rstDouble.EOF = True
strTestTeller1 = rstDouble.Fields("Teller")
strTestEng1 = rstDouble.Fields("English")
strTestWordType1 = rstDouble.Fields("WordType")
strTestTrad1 = rstDouble.Fields("Trad")
strTestRemarks1 = rstDouble.Fields("Remarks")

' Look if the record from the first recordset is the same as the record from
the secoond recordset.
' If "YES" skip the record as we need at least one entry.

With rstDouble
If strTestTeller = strTestTeller1 Then
GoTo Verder
End If

' Look if the fields of the current record ar ALL exactly the same as the
fields of the first recordset

If strTestEng <> strTestEng1 Then
GoTo Verder
End If
If strTestWordType <> strTestWordType1 Then
GoTo Verder
End If
If strTestTrad <> strTestTrad1 Then
GoTo Verder
End If
If strTestRemarks <> strTestRemarks1 Then
GoTo Verder
End If

' The fields are exactly the same, so fill the current record with empty
strings

.Edit
.Fields("English").Value = ""
.Fields("WordType").Value = ""
.Fields("Thrad").Value = ""
.Fields("Remarks").Value = ""
.Update
End With
Verder:

' Move to the next recordset and repeat the inner loop

rstDouble.MoveNext
Loop

' Close the inner loop and repeat the outher loop

rstDouble.Close
Set rstDouble = Nothing
rstWhole.MoveNext
Loop

' Close everything and go to sleep.

rstWhole.Close
rstDouble.Close
dbsDictionary.Close
Set dbsDictionary = Nothing
Set rstWhole = Nothing
Set rstDouble = Nothing
End Sub
 
S

SteveS

Matiyapak said:
Steve,

I tried your program code today.

The program did generate an error in the declaration:
Click to expand...

I *did* say it was air code.... :)



Glad your find double entries code works now.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Recordset variable with SQL 2
help with strange behaviour of macro 1
Copying a sequence of records with a loop - Help 5
cnn.execute problem with dates 2

Top