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:

Expand|Select|Wrap|Line Numbers

 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..

Expand|Select|Wrap|Line Numbers

 
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.

Expand|Select|Wrap|Line Numbers

 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..

Quote:

Originally Posted by 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

Also, Please use it without swapping....

Quote:

Originally Posted by yaaara

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

See if that works it out..

hjozinovic

Quote:

Originally Posted by 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.

You used .EOF before With statement.
Try:

Expand|Select|Wrap|Line Numbers

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 Inverness

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

 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:

Expand|Select|Wrap|Line Numbers

 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 Inverness

...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 :-)

Quote:

Originally Posted by Stewart Ross Inverness

...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 :)

For Each loop object declaration issues.

re: For Each loop object declaration issues.

re: For Each loop object declaration issues.

re: For Each loop object declaration issues.

re: For Each loop object declaration issues.

re: For Each loop object declaration issues.

re: For Each loop object declaration issues.

re: For Each loop object declaration issues.

re: For Each loop object declaration issues.

re: For Each loop object declaration issues.

re: For Each loop object declaration issues.

re: For Each loop object declaration issues.

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about