"Do until .EOF" doesn't update all the records

[Guest]

Hi,

The following code is working fine, however when one record doesn't meet the
condition, the database skips this record without updating it. I tried to put
"Else : .MovePrevious", so it retest the same record again, however the
database goes into endless loop. How can I make the database to include this
record in the loop again, so it gets updated.

Thank you
Muneer

Dim dbs As Database
Dim rst As DAO.Recordset
Dim TableTemp As Integer
Dim varX As Integer

Set rst = CurrentDb.OpenRecordset("Select * from Main WHERE ThursTable Is
Null and RegistrationNo Is Not Null and Cancelled is Null")
Set dbs = CurrentDb

With rst

Do Until .EOF

TableTemp = Int((72 - 1 + 1) * Rnd + 1)
varX = Nz(DLookup("[NoOfSeats]", "ThursdayTable", "[TableNo] =" &
TableTemp), 0)

If varX >= ![Registrants] Then

.Edit
![ThursTable] = TableTemp
varX = varX - ![Registrants]
.Update
dbs.Execute "UPDATE ThursdayTable SET NoOfSeats = " & varX & "
WHERE TableNo = " & TableTemp & ";"

End If
.MoveNext
Loop

End With
Set rst = Nothing
Set dbs = Nothing

 

[Guest]

Try changing:
With rst
to
with rst
..movelast
..movefirst

That might do the trick

 

[Guest]

Thank you Hafeez.. I'll try that

Try changing:
With rst
to
with rst
.movelast
.movefirst

That might do the trick

Hi,

The following code is working fine, however when one record doesn't meet the
condition, the database skips this record without updating it. I tried to put
"Else : .MovePrevious", so it retest the same record again, however the
database goes into endless loop. How can I make the database to include this
record in the loop again, so it gets updated.

Thank you
Muneer

Dim dbs As Database
Dim rst As DAO.Recordset
Dim TableTemp As Integer
Dim varX As Integer

Set rst = CurrentDb.OpenRecordset("Select * from Main WHERE ThursTable Is
Null and RegistrationNo Is Not Null and Cancelled is Null")
Set dbs = CurrentDb

With rst

Do Until .EOF

TableTemp = Int((72 - 1 + 1) * Rnd + 1)
varX = Nz(DLookup("[NoOfSeats]", "ThursdayTable", "[TableNo] =" &
TableTemp), 0)

If varX >= ![Registrants] Then

.Edit
![ThursTable] = TableTemp
varX = varX - ![Registrants]
.Update
dbs.Execute "UPDATE ThursdayTable SET NoOfSeats = " & varX & "
WHERE TableNo = " & TableTemp & ";"

End If
.MoveNext
Loop

End With
Set rst = Nothing
Set dbs = Nothing

 

[Larry Linson]

The following code is working fine, however

Always worries me when someone starts out by saying something is working
fine and then follows with "but" or "however" which invariably means it is
NOT working fine.

when one record doesn't meet the
condition,

Doesn't meet _what_ condition?

the database skips this record
without updating it. I tried to put
"Else : .MovePrevious", so it retest the
same record again, however the
database goes into endless loop.

If that record hasn't been updated, why would you expect it to meet the
condition that it did not previously meet simply by retesting it? If it
doesn't have to meet the condition, why is the condition being tested.

How
can I make the database to include this
record in the loop again, so it gets updated.

If I understood what you meant before, I could respond to this.

I will offer a suggestion: using With statements in long examples just makes
them not-so-easy to read, though it might have saved you a few keystrokes
when you wrote the code.

It puzzles me why I have seen at least two posts today which execute SQL
Updates on the same table they seem to be processing as a Recordset.

You don't update Records in your Recordset with SQL Updates, you update them
by using the .Edit method, changing Field value(s), and then using the
..Update method. If you are going to update the Table by executing an SQL
statement, it just doesn't make a lot of sense to have a Recordset Open on
that same Table at the same Time.

Finally, if you would start off by explaining in simple words what it is
that you are trying to accomplish, instead of expecting us to extract that
information from obviously not-working code, you'd have a lot better chance
of getting useful suggestions.

Larry Linson
Microsoft Access MVP

 

[Guest]

Always worries me when someone starts out by saying something is working
fine and then follows with "but" or "however" which invariably means it is
NOT working fine.

What I meant is the code is not working the way I wanted. It works fine though

Doesn't meet _what_ condition?

If varX >= ![Registrants] Then ....

If that record hasn't been updated, why would you expect it to meet the
condition that it did not previously meet simply by retesting it? If it
doesn't have to meet the condition, why is the condition being tested.

Because when the code loops back, varX is different this time. So I want to
check another record from "ThursdayTable" and see if it meets the condition

You don't update Records in your Recordset with SQL Updates, you update them
by using the .Edit method, changing Field value(s), and then using the
..Update method. If you are going to update the Table by executing an SQL
statement, it just doesn't make a lot of sense to have a Recordset Open on
that same Table at the same Time.

Checking and updating the other table is a part of the loop. I check a Table
number (for example 65) against the "ThursdayTable" If Table 65 has less
seats than the "Registrants" (number of registered people), the current
person from the "Main" table wouldn't get assigned to a Table. Therefore, I
want to check another table, so I could assign this person to a table.

Finally, if you would start off by explaining in simple words what it is
that you are trying to accomplish, instead of expecting us to extract that
information from obviously not-working code, you'd have a lot better chance
of getting useful suggestions.

I'm sorry if I wasn't clear. I thought instead of boaring you with the
details, I should ask a simple question, because my problem was to find a way
to re-test the record again without skipping it. Anyways, I found a solution
to my code.

Thank you for your reply
Muneer