Nested Loop Issue 1

[DrMingle]

Objective: I want to be able to assign initials to records in an alternating method until I run out of records.

For example, I have 451 records I would like to do the following:

1.TP
2.DM
3.PW
4.TP
5.DM
...
451.TP

Thanks in advance for any advice.

Sub SOSAssign()

Dim dbs As Database
Dim rst As Recordset
Dim Counter As Integer

' set up a updateable recordset of table
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM Table_Master", dbOpenDynaset)

' find number of records in recordset
Counter = rst.RecordCount

' loop until run out of records
Do Until Counter = 0
    
    With rst
    ' sets the SOS field to Thor Pio
        !SOS = TP
        ' moves to next record
        rst.MoveNext
            ' one less record to go
            Counter = Counter - 1
            
                With rst
                ' sets the SOS field to Dido Maximus
                !SOS = DM
                ' moves to next record
                rst.MoveNext
                ' one less record to go
                Counter = Counter - 1
                    
                    With rst
                    ' sets the SOS field to Party Willy
                    !SOS = PW
                    ' moves to next record
                    rst.MoveNext
                    ' one less record to go
                    Counter = Counter - 1
        
        ' loop until run out of records
Loop
End Sub

 

[vbajock]

What's with all the "with" statements in your code?

 

[DrMingle]

I am trying to help a non-technical user once the database is deployed.

Thank you for your question.

 

[randy700]

How about...

Dim strInit as String
strInit = "TP"
For Counter = rst.RecordCount To 0 Step -1
   rst.SOS = strInit
   Select Case strInit
      Case Is "TP"
         strInit = "DM"
      Case Is "DM"
         strInit = "PW"
      Case Is "PW"
         strInit = "TP"
   End Select
Next Counter

Note: NOT TESTED



Randy

 

[Andrzejek]

How about (code not tested)

...
Set rst = dbs.OpenRecordset("SELECT * FROM Table_Master ORDER BY ???", dbOpenDynaset)
...
With rst
    Do While NOT(.EOF)
       !SOS = "TP"
       .Update
       .MoveNext
       If .EOF Then Exit Do
       !SOS = "DM"
       .Update
       .MoveNext
       If .EOF Then Exit Do
       !SOS = "PW"
       .Update
       .MoveNext
       If .EOF Then Exit Do
    Loop
End With

Have fun.

---- Andy

 

[PHV]

What about this ?

Sub SOSAssign()
Dim dbs As Database
Dim rst As Recordset
Dim Counter As Integer
' set up a updateable recordset of table
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM Table_Master", dbOpenDynaset)
' loop until run out of records
With rst
  Do While Not .Eof
    Counter = Counter + 1
    .Edit
    Select Case (Counter Mod 3)
    Case 1
      !SOS = Counter & ".TP"
    Case 2
      !SOS = Counter & ".DM"
    Case 0
      !SOS = Counter & ".PW"
    End Select
    ' moves to next record
    .MoveNext
  Loop
End With
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

How about a query like the following which assumes table_Master has a numeric primary key field named ID.

UPDATE Table_Master SET Table_Master.SOS = Choose(DCount("*","Table_Master","ID <" & [ID]) Mod 3+1,"TP","DM","PW");

Duane
Hook'D on Access
MS Access MVP

 

[vbajock]

I'm waiting for the really long iif statement.....

 

[DrMingle]

Thanks guys...

All these answers where great and really challenged me to think how I had set things up.

 

[dhookom]

vbajock,
Choose() is the condensing of the really long IIf() statement.

IMO, IIf() should have a throttle/governor on it that limits nesting to no more than 2 which I think is 1 too many.

Duane
Hook'D on Access
MS Access MVP

 

[vbajock]

I was just kidding.