Nest DO Loop issue.

[Steve_in_Kent]

Below is code to compare two tables in Access. I have two nested do loops. RS1 (outer loop), and RS2 innerloop.
The innerloop just checks a membership number, and raises a flag if they are new.
This all works great.. however, the outer loop.. RS1.. repeats endlessly.

ie: it will all work correctly, but when it gets to the EOF for rs1 it just loops back to the start of RS1 again !

looked at this for hours, and cannot figure out, how it gets there.

Code: Select all

Private Sub Command20_Click()

'Run .csv comparison of members.
' first part read in the CSV file, line by line.
Dim MyLoc As String
MyLoc = Me.Text12
Mycount = 0


'Me.Text23.Visible = False
'Me.Text25.Visible = False
'Me.Text26.Visible = False
'Me.Text27.Visible = False
'Me.Text28.Visible = False
'Me.Text29.Visible = False

DoCmd.TransferText TransferType:=acImportDelim, SpecificationName:="SemiColonImportSpec", TableName:="Members_Import", FileName:=MyLoc, HasFieldNames:=True


' Tables to compare > Members_Import, and Data

Dim db As DAO.Database
Set db = CurrentDb

Dim X1 As String
Dim X2 As String
Dim X3 As String
Dim X4 As String



Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Set rs1 = db.OpenRecordset("Members_Import")
Set rs2 = db.OpenRecordset("Data")



'------------------------------------------------------------------
' loop through the Members Table...

rs1.MoveFirst
Do Until rs1.EOF


MyFlag = 0
Me.Check40 = False
CreateUser = "No"
Mycount = Mycount + 1
Me.Label43.Caption = "Count: " & Mycount
Me.Repaint



If Not IsNull(rs1!Field6) Then
    X1 = rs1!Field6 'Membership Number text 23
End If
    
If Not IsNull(rs1!Field2) Then
    X2 = rs1!Field2 'Mr or Mrs text 25
End If

If Not IsNull(rs1!Field4) Then
    X3 = rs1!Field4 ' First Name text 26
End If

If Not IsNull(rs1!Field5) Then
    X4 = rs1!Field5 'Surname text 27
End If

If Not IsNull(rs1!Field38) Then
    X5 = rs1!Field38 'Email Address text 28
End If

If Not IsNull(rs1!Field40) Then
    X6 = rs1!Field40 ' ok to email flag text 29
End If


' X1 is the membership number from Members_Import
'--------------------------------------------------------------------------------------------------------------------
' Now we need to loop thru the Data table to see if they are there.
'---------------------------------------------------------- secondary loop -------------------
rs2.MoveFirst
Do Until rs2.EOF
Y1 = rs2!Member_Number
If Y1 = X1 Then
    MyFlag = 1
End If
rs2.MoveNext
Loop


'----------------------------------------------------------------------------------------------------------------------------
If MyFlag = 0 Then
' yay we found a new member..
'----------------------------------------------------------------------------------------
 ' we found someone that is not a member. so we need to grab the data from the members table
 ' and ask to add to the database.
 ' So X1 to X6 is all the data to create the New Member in the Data Table.
 ' we add to the rs2 dataset
 
Me.Text23 = X1
Me.Text25 = X2
Me.Text26 = X3
Me.Text27 = X4
Me.Text28 = X5
Me.Text29 = X6

Me.Label21.Visible = True
Me.Text23.Visible = True
Me.Text25.Visible = True
Me.Text26.Visible = True
Me.Text27.Visible = True
Me.Text28.Visible = True
Me.Text29.Visible = True
Me.Label30.Visible = True
Me.Label31.Visible = True
Me.Label32.Visible = True
Me.Label33.Visible = True
Me.Label34.Visible = True
Me.Label35.Visible = True
Me.Label36.Visible = True
Me.Image38.Visible = True
Me.Image39.Visible = True
 
' the buttons are showing.. so now we need to wait until a decision is made
' run in a loop waiting for check 40 to change state signifying that either button has been clicked.
'---------------------------------------------------------------------------------
Do
    DoEvents
Loop Until Me.Check40 = True

'clear screen
Me.Label21.Visible = False
Me.Text23.Visible = False
Me.Text25.Visible = False
Me.Text26.Visible = False
Me.Text27.Visible = False
Me.Text28.Visible = False
Me.Text29.Visible = False
Me.Label30.Visible = False
Me.Label31.Visible = False
Me.Label32.Visible = False
Me.Label33.Visible = False
Me.Label34.Visible = False
Me.Label35.Visible = False
Me.Label36.Visible = False
Me.Image38.Visible = False
Me.Image39.Visible = False

If CreateUser = "Yes" Then
' create a new user in the Data table, from X1 to X6
    Stop
End If

End If ' Myflag =0  then
'----------------------------------------------------------------------------------------------------------------------------

rs1.MoveNext
Loop  'this is the rs1 loop

rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing



End Sub

[HansV]

Why do you have a Stop statement in the code?

[Steve_in_Kent]

The stop part is just, for when the user clicks a yes picture to add a new member.. so at that point, i need to add code to add
the X1-X6 string data, to the rs2 data table

This all works great, its just the outer loop keeps going.. ie: it will find 5 new members in the table i'm importing from a .csv.. but then it's almost like a rs1.Movefirst is happening again. because it loops back to the start of the outer loop.. i added a counter.. which will go to infinity atm.

What should happen, is when the outer loop is finished.. it should just exit the sub.. Finish.. the Rs1.EOF doesn't seem to be working ?

[HansV]

I'm afraid I'd have to see the database (and the imported table)...

[Gasman]

Have you even tried walking the code with F8 and a few breakpoints?

If you indented your code that would help you to find errors like this?

You could try commenting or removing rs1.MoveFirst?, it is not needed anyway. When you open a recordset, you are on the first record, or BOF/EOF if the file is empty.

I cannot see a problem with the code either, so I would be walking it with just a few records and breakpoints.

You do not apepar to be using Option Explicit either as Mycount is not declared?

[Steve_in_Kent]

Ok., after many hours.. found the problem kind of by accident.

the whole problem.. was
DoCmd.TransferText TransferType:=acImportDelim, SpecificationName:="SemiColonImportSpec", TableName:="Members_Import", FileName:=MyLoc, HasFieldNames:=True

The code was fine.
What it was doing, while i was testing, was Appending the .csv file again and again and again.. so it looked to my like it was in an infinite loop.. but it wasn't !

so i just need to delete the table, before i import it..

[Gasman]

You could just delete the records?