Loop with unknown number of elelments

[cdunston]

Please help! I am very new to VBA and would like to loop records in my table with the same ID number and set a value for all the grouped records to yes when I find a -1 in a any of the records. If a -1 is not found I would like to set the value to no. In either case I would then need to move on to the next group of records and repeat until the EOF.

I have looked at different types of loop syntax however, I am not sure which of those I should use and if a loop exist that will not require me to do any nesting because that seems complicated for a novice like myself.

Thanks!

 

[Vbark]

Here you go try trhis...

Public Sub Rsloop()

Dim rs          As DAO.Recordset
Dim strSQL      As String

'You can Limit your ID Number in the Recordset
strSQL = "SELECT * FROM [YourTable] WHERE [ID] = 1234"
Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset)

If Not rs.BOF Then
    rs.MoveFirst
    Do Until rs.EOF
        
        If Nz(rs!YourField) = -1 Then
            rs.Edit
            rs!WhicheverField = True 'If a yes/no or boolean field you need to set it to true rather than 'no
            rs.Update
            Else
            rs.Edit
            rs!WhicheverField = False
            rs.Update
            End If
            
        
        
        rs.MoveNext
        Loop
    End If




End Sub

 

[Bat17]

Hi
Welcome to the forum
First point, as you are trying to set a value depending on other records in the same table this rings alarm bells about the table design. have a search on 'normalization' for details.

my logic
1) As your field (field3) can only be 'yes' or 'no' then start by setting them all to 'no'
2) find just the unique id's (field1) that need updating
3) Loop through this list updating field3 to 'yes'

Dim rs As DAO.Recordset
Dim strSQL As String

DoCmd.SetWarnings False ' suppress the message when you update a record
'Make all records 'No'
DoCmd.RunSQL "UPDATE Table2 SET Table2.Field3 = No;"
'Select all groups with a '-1'
strSQL = "SELECT DISTINCT Field1 FROM Table2 WHERE Field2=-1;"
Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset)
' loop through and update 'yes'
If (rs.BOF = False And rs.EOF = False) Then
    rs.MoveFirst
    Do Until rs.EOF
       DoCmd.RunSQL "UPDATE Table2 SET Field3 = Yes WHERE Field1= " & rs!field1
       rs.MoveNext
    Loop
Else
    ' no records found to update
End If
DoCmd.SetWarnings True

ps. depending on your version of Access you may need to set a reference to Microsoft DAO object Libary, go Tools>refences in any module and scroll down.

HTH

Peter