2 Do Until Loop help

[EBee]

How do can i loop twice?

rst1.movefirst
Do until rst1.eof

rst.movefirst
Do Until rst.eof
If. . .Then. .else
If. . . Then
If . . . Then

End If
End If
End If
rst.movenext
Loop

rst1.movenext
Loop

End Sub

I am trying to compare the first record in rst1 table to all the record rst table. I want to loop so that the first round it will start from the first record in rst1 table then compare that record to the first record in rst, then the second record in rst then third in rst. . until EOF. then go back to the second record in the rest1 table and do the same thing all over again. 'Em I on the right track?

thanks
erwin

 

[Aivars]

Hi, erwin!

You miss rst update commands in your code:

rst1.movefirst
Do until rst1.eof
rst.movefirst
Do Until rst.eof
If. . .Then. .else
If. . . Then
If . . . Then
'if is found needed record
rst.edit
rst!MyField1 = rst1!MyField1
rst!MyField1 = rst1!MyField1
rst!MyFieldX = rst1!MyFieldX
rst.update
'It's not needed that you continue moving cursor in this recordset because needed record is already found. Therefore:
exit do
End If
End If
End If
rst.movenext
Loop
rst1.movenext
Loop

Aivars

 

[EBee]

Option Compare Database
Option Explicit



Private Sub Newtable()

Dim db As Database
Dim rst As Recordset
Dim rstOpen As Recordset
Dim rstFirst As Recordset
Dim rstExcept As Recordset
Dim rstSecond As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("auxil1&quot
Set rstOpen = db.OpenRecordset("Auxil_Logic_Open", dbOpenDynaset)
Set rstFirst = db.OpenRecordset("table6&quot
Set rstExcept = db.OpenRecordset("tableExcept&quot
Set rstSecond = db.OpenRecordset("table789&quot


rstOpen.MoveFirst
Do Until rstOpen.EOF

rst.MoveFirst

Do Until rst.EOF


If rst!Number = rstOpen!Number Then
If rst!Tx_Date = rstOpen!Tx_Date Then
If (DateDiff(&quot;s&quot;, rst!Tx_Time, rstOpen!Tx_Time) < &quot;60&quot Then
If rst!Trans_Number = &quot;6&quot; Then

With rstFirst
.AddNew
!Number = rst!Number
!Denom = rst!Denom
!Location = rst!Location
!Emp_Name = rst!Emp_Name
!Tx_Date = rst!Tx_Date
!Tx_Time = rst!Tx_Time
!Trans_Number = rst!Trans_Number
!Trans_Desc = rst!Trans_Desc
.Update
End With

With rstFirst
.AddNew
!Number = rstOpen!Number
!Denom = rstOpen!Denom
!Location = rstOpen!Location
!Emp_Name = rstOpen!Emp_Name
!Tx_Date = rstOpen!Tx_Date
!Tx_Time = rstOpen!Tx_Time
!Trans_Number = rstOpen!Trans_Number
!Trans_Desc = rstOpen!Trans_Desc
.Update
End With

ElseIf rst!Trans_Number > 6 Then

With rstSecond
.AddNew
!Number = rst!Number
!Denom = rst!Denom
!Location = rst!Location
!Emp_Name = rst!Emp_Name
!Tx_Date = rst!Tx_Date
!Tx_Time = rst!Tx_Time
!Trans_Number = rst!Trans_Number
!Trans_Desc = rst!Trans_Desc
.Update
End With

With rstSecond
.AddNew
!Number = rstOpen!Number
!Denom = rstOpen!Denom
!Location = rstOpen!Location
!Emp_Name = rstOpen!Emp_Name
!Tx_Date = rstOpen!Tx_Date
!Tx_Time = rstOpen!Tx_Time
!Trans_Number = rstOpen!Trans_Number
!Trans_Desc = rstOpen!Trans_Desc
.Update
End With


End If
Else
With rstExcept
.AddNew
!Number = rstOpen!Number
!Denom = rstOpen!Denom
!Location = rstOpen!Location
!Emp_Name = rstOpen!Emp_Name
!Tx_Date = rstOpen!Tx_Date
!Tx_Time = rstOpen!Tx_Time
!Trans_Number = rstOpen!Trans_Number
!Trans_Desc = rstOpen!Trans_Desc
.Update
End With
Exit Do
End If

End If

End If


rst.MoveNext


Loop
rstOpen.MoveNext
Loop

End Sub

Here is my code, could you check what i am doing wrong, it keeps duplicating the same record to the table

thanks
erwin

 

[Aivars]

Are you sure that field Number of table6 have not Primary key:

With rstFirst
.AddNew
!Number = rst!Number

............
............

With rstFirst
.AddNew
!Number = rstOpen!Number

as well as field Number of table table789:

With rstSecond
.AddNew
!Number = rst!Number

............
............

With rstSecond
.AddNew
!Number = rstOpen!Number

******************************
If code above isn't wrong then I would change your code following:

Private Sub Newtable()

Dim db As Database
Dim rst As Recordset
Dim rstOpen As Recordset
Dim rstFirst As Recordset
Dim rstExcept As Recordset
Dim rstSecond As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset(&quot;auxil1&quot
Set rstOpen = db.OpenRecordset(&quot;Auxil_Logic_Open&quot;, dbOpenDynaset)
Set rstFirst = db.OpenRecordset(&quot;table6&quot
Set rstExcept = db.OpenRecordset(&quot;tableExcept&quot
Set rstSecond = db.OpenRecordset(&quot;table789&quot

Do While Not rstOpen.EOF

rst.MoveFirst

rst.FindFirst &quot;Number=&quot; & rstOpen!Number 'Is it numeric type field?
If Not rst.NoMatch Then
If rst!Tx_Date = rstOpen!Tx_Date Then
If (DateDiff(&quot;s&quot;, rst!Tx_Time, rstOpen!Tx_Time) < &quot;60&quot Then
If rst!Trans_Number = &quot;6&quot; Then
With rstFirst
.AddNew
!Number = rst!Number
!Denom = rst!Denom
!Location = rst!Location
!Emp_Name = rst!Emp_Name
!Tx_Date = rst!Tx_Date
!Tx_Time = rst!Tx_Time
!Trans_Number = rst!Trans_Number
!Trans_Desc = rst!Trans_Desc
.Update

.AddNew
!Number = rstOpen!Number
!Denom = rstOpen!Denom
!Location = rstOpen!Location
!Emp_Name = rstOpen!Emp_Name
!Tx_Date = rstOpen!Tx_Date
!Tx_Time = rstOpen!Tx_Time
!Trans_Number = rstOpen!Trans_Number
!Trans_Desc = rstOpen!Trans_Desc
.Update
End With

ElseIf rst!Trans_Number > 6 Then

With rstSecond
.AddNew
!Number = rst!Number
!Denom = rst!Denom
!Location = rst!Location
!Emp_Name = rst!Emp_Name
!Tx_Date = rst!Tx_Date
!Tx_Time = rst!Tx_Time
!Trans_Number = rst!Trans_Number
!Trans_Desc = rst!Trans_Desc
.Update

.AddNew
!Number = rstOpen!Number
!Denom = rstOpen!Denom
!Location = rstOpen!Location
!Emp_Name = rstOpen!Emp_Name
!Tx_Date = rstOpen!Tx_Date
!Tx_Time = rstOpen!Tx_Time
!Trans_Number = rstOpen!Trans_Number
!Trans_Desc = rstOpen!Trans_Desc
.Update
End With
End If
Else
With rstExcept
.AddNew
!Number = rstOpen!Number
!Denom = rstOpen!Denom
!Location = rstOpen!Location
!Emp_Name = rstOpen!Emp_Name
!Tx_Date = rstOpen!Tx_Date
!Tx_Time = rstOpen!Tx_Time
!Trans_Number = rstOpen!Trans_Number
!Trans_Desc = rstOpen!Trans_Desc
.Update
End With
End If
End If
End If
rstOpen.MoveNext
Loop

End Sub

Aivars

 

[MichaelRed]

Seems like a really ugly way to avoid using a couple of update queries to me. MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Aivars]

You are right, Michael!

 

[EBee]

I can not add a primary key in all the tables because the records(&quot;Number&quot can duplicate. The thing is it starts duplicating the records as it loops around. I will try to do what you suggested.

What if i make the Tx_Time primary key in all the tables, which i did, whould this solve my problem.if so, how can i manage it in the module so it will update.

I don't see that the rst is looping, does this mean that the rst will always stay on the the first record all the time while the rstOpen move to the next record?.

 

[EBee]

rst.FindFirst &quot;Number=&quot; & rstOpen!Number 'Is it numeric type field?

YES it is a number field. I got a runtime error when executed. Error 3251 = &quot; Opereation is not supported fo this type object&quot;

is there something that i need to change in this line

 

[Aivars]

Do you use DAO?

 

[EBee]

no, Just Access97 module. Is it easier using DAO ?