Do Until loop within a Do Until loop?

[fluffyozzy]

I'm sure this is really simple and I'm missing something silly. The following code works perfectly for what I want to do only if I specify the criteria in the sql statement.

Dim rst As DAO.Recordset
Dim rsta As DAO.Recordset
Dim data1, data2 As Integer
Dim db As Database
Dim sql As String
Dim sqla As String

sql = "SELECT * FROM tblBatch WHERE tblBatch.BatchNo = 17;"
sqla = "SELECT * from tblArchive WHERE tblArchive.BatchNo = 17;"

Set db = CurrentDb
Set rst = db.OpenRecordset(sql)
Set rsta = db.OpenRecordset(sqla)
data1 = rst("BatchNo")
data2 = rsta("BatchNo")

Do Until rst.EOF
If data1 = data2 Then
With rsta
.Edit
.Fields("BatchID") = rst!BatchID
.Fields("OrgID") = rst!OrgID
.Update
End With
End If
rsta.MoveNext
Loop

Both tables contain fields BatchID (PK in tblBatch and FK in tblArchive), BatchNo (this is a sequential number) and OrgID. The tblArchive has more than one corresponding items to each BatchID and at the moment, both the BatchID and OrgID are missing from the Archive table, however, records are identifiable by BatchNo (thankfully), so we know that we will be appending the right records.

What I want to do is to introduce another loop that moves through the tblBatch, find the corresponding items in the tblArchive, update the BatchID and OrgID records in tblArchive with the correct record, then move to the next record in tblBatch and loop. The above code works great if I specify the record to find but there are thousands of records so I'd like to automate the loop process. Could you please help?

I tried:

sql = "SELECT * FROM tblBatch;"
sqla = "SELECT * FROM tblArchive;"
Do Until rst.EOF
rst.MoveFirst
If data1 = data2 Then
Do Until rsta.EOF
With rsta
.Edit
.Fields("BatchID") = rst!BatchID
.Fields("OrgID") = rst!OrgID
.Update
End With
rsta.MoveNext
Loop
End If
rst.MoveNext
Loop

But this is just sending me into an endless loop! Played around quite a bit with it to figure out where loops should go and it's either not doing anything or looping constantly. I would be grateful for any help from you lovely gurus out there

Thank you in advance

 

[namliam]

Assuming you have a procedure containing:

Dim rst As DAO.Recordset
Dim rsta As DAO.Recordset
Dim data1, data2 As Integer
Dim db As Database
Dim sql As String
Dim sqla As String

sql = "SELECT * FROM tblBatch WHERE tblBatch.BatchNo = 17;"
sqla = "SELECT * from tblArchive WHERE tblArchive.BatchNo = 17;"

Set db = CurrentDb
Set rst = db.OpenRecordset(sql)
Set rsta = db.OpenRecordset(sqla)
data1 = rst("BatchNo")
data2 = rsta("BatchNo")

Do Until rst.EOF
If data1 = data2 Then
With rsta
.Edit
.Fields("BatchID") = rst!BatchID
.Fields("OrgID") = rst!OrgID
.Update
End With
End If
rsta.MoveNext
Loop

You can make this procedure accept the BatchID as input

Sub YourProcedure(YourBatchID as long)
...
sql = "SELECT * FROM tblBatch WHERE tblBatch.BatchNo = " & YourBatchID
...

Then make your "batch loop" call this procedure feeding it the unique batch IDs

 

[fluffyozzy]

Actually, this is going to be a one-off operation. Trying to fix tables exported from Excel for the existing records (of which there are thousands without correct values in the correct fields, sigh).

I do have a procedure set up to update the fields with the correct values for new records and they are working just fine, I just need to do this once to update the table, so all records are setup

 

[namliam]

Actually, this is going to be a one-off operation.

And that changes things, how?

 

[gemma-the-husky]

out of interest ... why can you not just use an update query to do this directly - without needing to iterate recordsets?

 

[fluffyozzy]

Thanks for the replies... much appreciated.

I thought I would need a second loop so that I wouldn't have to specify the ID number at the beginning, so that it would go through the entire table one-by-one, look for matching values on one field and update the others accordingly.

I will look into the update query gemma, perhaps an easier option for what I want to do. Re-learning as I go...

I've been away from Access too long (was not experienced anyway), being tasked to fix a database that's awfully messed up ... in a bit of a muddle at the minute... sorry