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