Do...Loop

[aziz rasul]

I have the following piece of code.

Set rstRecon = dbs.OpenRecordset("tblReconciliation")

record_count = DCount("ID", "tblReconciliation")
With rstRecon
.MoveFirst
x = 1

Do While rstRecon("Period") <> Me!Period And rstRecon("Year") <> Me!Year
.MoveNext
x = x + 1
If x > record_count Then
.AddNew
!Period = Me!Period
!Year = Me!Year
.Update
End If
Loop

What the code is supposed to do is to look at the values of Me!Period (typically 1, 2, 3, ... 12) and Me!Year (typically 2001-02, 2002-03, etc.) and check whether there is a record in tblReconciliation. If not it should create a new record.

The problem is that the Do While statement only looks at the

rstRecon("Period") <> Me!Period

and not the whole statement i.e.

rstRecon("Period") <> Me!Period And rstRecon("Year") <> Me!Year

How do I change the code so that it checks the above without having to use 2 Do Loop statements?

 

[AlanS]

I'm not sure why the Do While statement would only consider the first condition, but that might be corrected by enclosing both conditions in parentheses:

Do While (rstRecon("Period") <> Me!Period) And (rstRecon("Year") <> Me!Year)

However, you could dispense with the loop entirely by using the following:

If IsNull(DLookup("ID", "tblReconciliation", _
"(Period = '" & Me!Period & "') And (Year = '" _
& Me!Year & "')") Then
.AddNew
!Period = Me!Period
!Year = Me!Year
.Update
End If

 

[aziz rasul]

Thanks guys.

 

[aziz rasul]

Just to resurrect this again.

If I use the following code

Private Sub Command87_Click()

Dim dbs As DAO.Database, rstRecon As DAO.Recordset, record_count As Long, x As Long

Set dbs = CurrentDb()
Set rstRecon = dbs.OpenRecordset("tblReconciliation")

record_count = DCount("ID", "tblReconciliation")

With rstRecon
.MoveFirst
x = 1

Do While rstRecon("Period") <> Me!Period And rstRecon("YearRange") <> Me!YearRange ' or (rstRecon("Period") <> Me!Period Or rstRecon("YearRange") <> Me!YearRange)
.MoveNext
x = x + 1
If x > record_count Then
.AddNew
!Period = Me!Period
!YearRange = Me!YearRange
.Update
Exit Do
End If
Loop

End With

End Sub

it only looks at the rstRecon("Period") <> Me!Period in the Do While line and ignores the second condition i.e. rstRecon("YearRange") <> Me!YearRange.

However if I change the Do While line to

Do While rstRecon("Period") <> Me!Period Or rstRecon("YearRange") <> Me!YearRange

i.e. with an Or

it works!

Why should the And not work and the Or does.

I accept the fact that I can adopt the FindFirst approach which can solve the problem. However I'm trying to understand why the above problem should occur.

 

[AlanS]

You didn't specify exactly what you mean when you say that one way works and the other one doesn't (e.g., are cases that should be excluded being included, are cases that should be included being excluded, both, etc.?). However, it may just be that Or matches the correct logic for your application and And does not. Connecting your conditions with Or will allow the loop to continue as long as ANY of the individual conditions is true; using And will allow the loop to continue only so long as ALL of the conditions are true.

 

[aziz rasul]

The AND operation doesn't work while the OR does.

What I was trying to achieve was the AND operation, yet it was the OR that made it work! Does that make sense?

 

[AlanS]

Again, what does "doesn't work" mean? Is the loop terminating too soon (excluding cases that should be included), or does it not terminate soon enough (including cases that should be excluded), or what? I can't tell you any more without knowing what specific logic the loop is supposed to accomplish, and specifically how it is failing to do that.

 

[aziz rasul]

OK. On the form there are 2 combo boxes (Period, YearRange) which can take on the values given in the "tblReconciliation" table below.

What I am trying to, is to select a value from the Period and YearRange combo boxes e.g. Period = 1 and YearRange = 2003-04 and see whether a record in "tblReconciliation" exists with that combination.

When the code reads the Do While statement and sees that the Period value in the combo box exists in the "tblReconciliation" table, it exits the Do Loop i.e. when it finds the FIRST occurance of Period = 1 irrespective of what the YearRange value is on the form.

What I want the Do While statement to do is to continue looping the table until BOTH the Period AND the YearRange values on the form exist in a single record in "tblReconciliation".

Hence in our example of Period = 1, YearRange = 2003-04 on the form, the Do Loop exits when it hits the record in the table where ID = 2 instead of ID = 14.

As I said in an earlier post, I know that I can solve the problem using FindFirst or using 2 Do Loop's. I'm trying to understand why the above logic occurs the way it does i.e. if I use an AND in the Do While statement it doesn't work, but using an OR instead does!!!!

ID Period YearRange Ledger Total
1 12 2001-02 6138692
2 1 2002-03 4178788
3 2 2002-03 -8579793
4 3 2002-03 6070226
5 4 2002-03 0
6 5 2002-03 0
7 6 2002-03 0
8 7 2002-03 0
9 8 2002-03 0
10 9 2002-03 0
11 10 2002-03 0
12 11 2002-03 0
13 12 2002-03 0
14 1 2003-04 0
15 2 2003-04 0
16 3 2003-04 0

 

[AlanS]

OK, I see what you're doing. As I suspected, Or works while And does not because Or expresses the correct logic for your problem. You want to keep looping as long as EITHER the year or the period (or both) don't match - you don't want to stop if only one matches. If you change the Or to an And, you are in effect saying to loop only so long as BOTH don't match, which would stop the loop when the year matches even though the period does not (or vice versa).

Bottom line: Or gives you the correct logic for what you're trying to do, while And does not.

 

[aziz rasul]

I didn't realise that the logic worked in the way you described. However it seems to work that way. Many Thanks.