By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,334 Members | 2,776 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,334 IT Pros & Developers. It's quick & easy.

For Each loop object declaration issues.

P: 17
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:


Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdUpdate_Click()
  2. Dim rcdTesting As DAO.Recordset
  3. Dim rcdReference As DAO.Recordset
  4. Dim rcdReference2 As DAO.Recordset
  5. Dim rcdReference3 As DAO.Recordset
  6. Dim I As Variant
  7. Dim Constant As Integer
  8. Dim Constant2 As Integer
  9. Dim Constant3 As Integer
  10.  
  11.  
  12.    Set rcdTesting = CurrentDb.OpenRecordset("tblTest2")
  13.    Set rcdReference = CurrentDb.OpenRecordset("tblReference")
  14.    Set rcdReference2 = CurrentDb.OpenRecordset("tblReference2")
  15.    Set rcdReference3 = CurrentDb.OpenRecordset("tblReference3")
  16.  
  17.  With rcdTesting
  18.  For Each I In rcdTesting
  19.     .Edit
  20.    If ![Outing] = rcdReference![Value] Then
  21.    Constant = rcdReference![Result]
  22.    End If
  23.    If ![Lap] = rcdReference2![Value] Then
  24.    Constant2 = rcdReference2![Result]
  25.    End If
  26.    If ![Time] = rcdReference3![Value] Then
  27.    Constant3 = rcdReference3![Result]
  28.    End If
  29.    ![Value].Value = Constant * Constant2 * Constant3
  30.    .Update
  31.    Next
  32.  End With
  33.  
  34. End Sub
Sep 1 '08 #1
Share this Question
Share on Google+
11 Replies


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

Expand|Select|Wrap|Line Numbers
  1. With rcdTesting
  2. For i=0 to .EOF
  3. .edit
  4. If ![Outing] = rcdReference![Value] Then
  5. Constant = rcdReference![Result]
  6. End If
  7. If ![Lap] = rcdReference2![Value] Then
  8. Constant2 = rcdReference2![Result]
  9. End If
  10. If ![Time] = rcdReference3![Value] Then
  11. Constant3 = rcdReference3![Result]
  12. End If
  13. ![Value].Value = Constant * Constant2 * Constant3
  14. .Update
  15. .MoveNext
  16. End With
  17. End Sub 
See if this works...
Sep 1 '08 #2

P: 17
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.

Expand|Select|Wrap|Line Numbers
  1. For I = 0 To .EOF
  2. With rcdTesting
  3.     .Edit
  4.    If ![Outing] = rcdReference![Value] Then
  5.    Constant = rcdReference![Result]
  6.    End If
  7.    If ![Lap] = rcdReference2![Value] Then
  8.    Constant2 = rcdReference2![Result]
  9.    End If
  10.    If ![Time] = rcdReference3![Value] Then
  11.    Constant3 = rcdReference3![Result]
  12.    End If
  13.    ![Value].Value = Constant * Constant2 * Constant3
  14.    .Update
  15.    .MoveNext
  16. End With
Any ideas?

Thanks for your help.
Sep 1 '08 #3

P: 77
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.
Sep 1 '08 #4

P: 77
Also, Please use it without swapping....

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

See if that works it out..
Sep 1 '08 #5

100+
P: 167
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:
Expand|Select|Wrap|Line Numbers
  1. rcdTesting.EOF
Sep 1 '08 #6

P: 17
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.
Sep 1 '08 #7

Expert Mod 2.5K+
P: 2,545
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

Expand|Select|Wrap|Line Numbers
  1. Do While not rcdTesting.EOF
  2.    <statements currently in your FOR loop>
  3.   rcdTesting.Movenext
  4. 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:

Expand|Select|Wrap|Line Numbers
  1. Dim lngRecCount as Long
  2. <... open your recordset etc>
  3. rcdTesting.movelast ' force a traverse of all records - sets record counter correctly
  4. rcdTesting.movefirst 
  5. lngRecCount = rcdTesting.Recordcount
  6. For I = 1 to lngRecCount
  7.    <...loop statements as before>
  8. Next I
-Stewart
Sep 1 '08 #8

Expert Mod 2.5K+
P: 2,545
...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
Sep 1 '08 #9

P: 17
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
Sep 1 '08 #10

P: 77
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
Sep 1 '08 #11

NeoPa
Expert Mod 15k+
P: 31,434
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 :)
Sep 1 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.