Help with Loop

[jamesdpmullan]

Hi,

Im trying to create a loop that goes through each record in a continuous form which then stops when a checkbox value = 0. At this point another checkbox value = -1 in the header.

If all records have a value of -1 then the loop stops at the end of the recordset.

This is the code i have put in but believe it be completely incorrect.

Attached image also.

thanks for your help

With Me.RecordsetClone
.MoveFirst
Do Until LineCompleteDIMS.Value = 0

If LineCompleteDIMS.Value = 0 Then

Check56.Value = -1

End If

.MoveNext


Loop
End With

 

[spikepl]

When posting code, use the code brackets. Go Advanced ->select code ->press #

You are looping through the recordset clone, but you are getting values from the form's recordset, whose pointer deos not move (that is exactly the point about looping through the clone - you can move the record pointer in the clone and not affect the original, until you choose to do so).

With Me.RecordsetClone
Do [B]While NOT .EOF '[/B]or else you are out of the loop wehn condition is true, and never get the next line 
    If  [COLOR=Red][B]![/B][/COLOR]LineCompleteDIMS= 0 Then
        Me.Check56.Value = -1
        Exit Do
    End If
    .MoveNext
Loop
End With

 

[jamesdpmullan]

Hi

Thanks - i tried the code you supplied which does go through the loop but when there is a value of 0 in one of the linecompletedims check box, check56 value does not change to true (-1). I tried changing the me.chec56.value = -1 to a msgbox but this does not appear either.

Any thoughts? thanks for your help

 

[spikepl]

Dunno. Learn how to debug code - follow the recipe here: http://www.access-programmers.co.uk/forums/showthread.php?t=149429 (section Debugging tips)

 

[jamesdpmullan]

ok, so nearly there - check56 is true when linecompletedims = 0 loops through ok.

only thing is if all linecompletedims = -1 then once it gets to end of recordset i get an error message saying 'No current record'

  With Me.RecordsetClone
    .MoveFirst
    Do While Not .EOF
    If LineCompleteDIMS.Value = 0 Then
        Check56.Value = -1
      
      Exit Do
      Else
            .MoveNext
      End If

   '   .MoveNext

      Me.Bookmark = .Bookmark
    Loop
  End With

 

[stopher]

Where are you putting this code to make it fire?

Besides, if I understand correctly, you want a checkbox in the header to be checked if one (or more) checkboxes in the detail are unchecked. You can do this by setting the formula in your header checkbox to this:

=Sum(1+[LineCompleteDIMS])

It works like this...

The 1+[LineCompleteDIMS] gives a value of 0 for true and 1 for false. So if you sum all these up in your detail, summing all trues will give zero but if there are any false the the sum will be a positive value.

The y/n value treats zero as false and any other value as true (not just -1). So any positive value (i.e. where you have an unchecked value) will give you a true in your header checkbox.

hth
Chris

 

[jamesdpmullan]

Chris,

Your a genius, thanks.

I cant believe i was trying to setup a loop when i could put a bit of code in the checkboxes control source!

thanks

James