For Each loop object declaration issues.

[zoeb]

Hi,

I am very new to Access and wondered if anyone could help with a syntax error I have with my VBA.

I have a table, and 3 fields which should update a 4th field, but referencing 3 other tables - each linked to one of the 3 fields. This section seems to work fine, however I want it to do it for every record in the table and currently it only does it for the first one.

I have tried using the For Each statement, but this is apparently invalid for "this type of object".

Any ideas of how I can declare things differently?

Thanks

Zoe

My code is as follows:

Private Sub cmdUpdate_Click()
Dim rcdTesting As DAO.Recordset
Dim rcdReference As DAO.Recordset
Dim rcdReference2 As DAO.Recordset
Dim rcdReference3 As DAO.Recordset
Dim I As Variant
Dim Constant As Integer
Dim Constant2 As Integer
Dim Constant3 As Integer
 
 
   Set rcdTesting = CurrentDb.OpenRecordset("tblTest2")
   Set rcdReference = CurrentDb.OpenRecordset("tblReference")
   Set rcdReference2 = CurrentDb.OpenRecordset("tblReference2")
   Set rcdReference3 = CurrentDb.OpenRecordset("tblReference3")
 
 With rcdTesting
 For Each I In rcdTesting
    .Edit
   If ![Outing] = rcdReference![Value] Then
   Constant = rcdReference![Result]
   End If
   If ![Lap] = rcdReference2![Value] Then
   Constant2 = rcdReference2![Result]
   End If
   If ![Time] = rcdReference3![Value] Then
   Constant3 = rcdReference3![Result]
   End If
   ![Value].Value = Constant * Constant2 * Constant3
   .Update
   Next
 End With
 
End Sub

[yaaara]

Instead of a For Each statement, try looping through the recordset using the EOF property..

With rcdTesting
 For i=0 to .EOF
.edit
If ![Outing] = rcdReference![Value] Then
Constant = rcdReference![Result]
End If
If ![Lap] = rcdReference2![Value] Then
Constant2 = rcdReference2![Result]
End If
If ![Time] = rcdReference3![Value] Then
Constant3 = rcdReference3![Result]
End If
![Value].Value = Constant * Constant2 * Constant3
.Update
 .MoveNext
End With
End Sub 

See if this works...

[zoeb]

Having the code as stated above resulted in a "end with, without with" compile error. So I've swapped it round (as shown below) and now I get "invalid or unqualified reference" highlighting the .EOF.

For I = 0 To .EOF
With rcdTesting
    .Edit
   If ![Outing] = rcdReference![Value] Then
   Constant = rcdReference![Result]
   End If
   If ![Lap] = rcdReference2![Value] Then
   Constant2 = rcdReference2![Result]
   End If
   If ![Time] = rcdReference3![Value] Then
   Constant3 = rcdReference3![Result]
   End If
   ![Value].Value = Constant * Constant2 * Constant3
   .Update
   .MoveNext
End With

Any ideas?

Thanks for your help.

[yaaara]

Oops I forgot the "Next" statement just before the End With...

See if that works it out..

Having the code as stated above resulted in a "end with, without with" compile error. So I've swapped it round (as shown below) and now I get "invalid or unqualified reference" highlighting the .EOF.

For I = 0 To .EOF
With rcdTesting
.Edit
If ![Outing] = rcdReference![Value] Then
Constant = rcdReference![Result]
End If
If ![Lap] = rcdReference2![Value] Then
Constant2 = rcdReference2![Result]
End If
If ![Time] = rcdReference3![Value] Then
Constant3 = rcdReference3![Result]
End If
![Value].Value = Constant * Constant2 * Constant3
.Update
.MoveNext
End With

Any ideas?

Thanks for your help.

[yaaara]

Also, Please use it without swapping....

Oops I forgot the "Next" statement just before the End With...

See if that works it out..

[hjozinovic]

Having the code as stated above resulted in a "end with, without with" compile error. So I've swapped it round (as shown below) and now I get "invalid or unqualified reference" highlighting the .EOF.

For I = 0 To .EOF
With rcdTesting
.Edit
If ![Outing] = rcdReference![Value] Then
Constant = rcdReference![Result]
End If
If ![Lap] = rcdReference2![Value] Then
Constant2 = rcdReference2![Result]
End If
If ![Time] = rcdReference3![Value] Then
Constant3 = rcdReference3![Result]
End If
![Value].Value = Constant * Constant2 * Constant3
.Update
.MoveNext
End With

Any ideas?

Thanks for your help.

You used .EOF before With statement.
Try:

rcdTesting.EOF

[zoeb]

Thanks for all your help everyone.

If I have the Next I, before the End With statement, it says "For without Next" and if I have the Next I after the End With statement, it says "With, without End With!".

Just wondered if there was another way of doing in - I've got a query somewhere else which calculates the maximum ID (which will always be the total number of records in the table). Would it be possible to replace the For I=0 to rcdTesting.EOF to For I=1 to TotalNoRecords (for example)?

I had a go at this but I didn't know how to select record 1, perform operation on record one, and then with the next iteration of the for loop go on to record 2.

Is there a syntax for example which:

I = 2

![Value].Value(I) = Constant * Constant2 * Constant3

where the (I) selects the record corresponding to ID 2?

Sorry I'm sued to MATLAB which would automatically step through records so this is all new to me :-)

Thanks for all your help.

Zoe

P.S. When I don't get either of the errors stated at the top, the for loop ends after one iteration.

[Stewart Ross]

Hi. You are using the wrong kind of loop here; rcdTesting is a recordset, and For ... Each applies to object collections, not recordsets. Although you can use a FOR loop you would need to access the recordcount of the recordset to get the stop value - not difficult, but unnecessary.

The norm is to use

Do While not rcdTesting.EOF
   <statements currently in your FOR loop>
  rcdTesting.Movenext
Loop

The End of File property of the recordset is a boolean property (true/false, represented as -1 or 0). It cannot be used as the FOR-loop index stop value with any meaningful result.

For completeness, to use a FOR loop instead of the Do loop - which I don't recommend - you would need the following statements:

Dim lngRecCount as Long
<... open your recordset etc>
rcdTesting.movelast ' force a traverse of all records - sets record counter correctly
rcdTesting.movefirst 
lngRecCount = rcdTesting.Recordcount
For I = 1 to lngRecCount
   <...loop statements as before>
Next I

-Stewart

[Stewart Ross]

...and to add to my previous reply you will need more than one of these loops, as you are updating a different recordset than the one you are looping through.

You will find examples of multiple recordset processing elsewhere on this site (as in this howto article on Access VBA DAO recordset loop using two recordsets) and I suggest you consider what you are trying to achieve here. There may well be better methods using SQL updates (no code at all), but without knowing your application I can't advise further on this.

-Stewart

[zoeb]

That works brilliant and certainly solves that problem. Thank you!

Having just seen your extra answer I shall have a read as you were right I was having issues looping through additional recordests.

Thanks again, you've been great.

Zoe

[yaaara]

Thanks for the corrections above Steve... and I agree to you that multiple loops will have to be used as there are no iterations in the other multiple references that are being made and apparantly, the same values will be referred to again and again... I guess a different approach is required as suggested by you in the reply above :-)

...and to add to my previous reply you will need more than one of these loops, as you are updating a different recordset than the one you are looping through.

You will find examples of multiple recordset processing elsewhere on this site (as in this howto article on Access VBA DAO recordset loop using two recordsets) and I suggest you consider what you are trying to achieve here. There may well be better methods using SQL updates (no code at all), but without knowing your application I can't advise further on this.

-Stewart

[NeoPa]

It's nice to see I'm not the only one that can learn by getting it wrong sometimes.

Trying to help is a laudable aim in itself :)