I Need Help with Loops!! (1 Viewer)

[MrBen101]

Hello there!
I'm probably being stupid here, but I'm really struggling with two nested For ... Next loops.
The DB I have is a waiting list simulator. It has an amount of people waiting in 53 weekly brackets (0 to 52), for each Specialty (19 of them). It takes the starting point from another DB on our system (linked table), then in the table tbl_Patients_To_Remove you can specify how many people will be removed from the waiting list and added to the waiting list for each of the weekly brackets and specialty.
This is then compared to the original starting list, and the patient amount calculated for each week and appended to the table tbl1stAttempt.
This was working absolutely fine without the specialty in there, but now it's messed my loop up.
I think I've figured out why it's gone wrong, just dont know how to rectify it!
Here's the code for the loops:

Private Sub Command3_Click()
 
Dim DB As Database
Dim RST As Recordset
Dim Pats
Dim Pats2
Dim NewPats
Dim R
Dim X
Dim SID

Set DB = CurrentDb
Set RST = DB.OpenRecordset("tbltemp")
SID = [Spec_ID]
X = [Weeks Waited]
 
For SID = 1 To 19 Step 1
    For X = 52 To 0 Step -1
    Pats = [Patients Waiting]
    Pats2 = 0
 
Start:
    If Pats >= 0 Then
        'CurrentDb.Execute "UPDATE tblTemp SET [Patients Waiting]=" & Pats & " WHERE [Weeks Waited] = " & Me.Weeks_Waitedtxt & " AND [Spec_ID] =" & Me.Spec_IDtxt
        
        
        DoCmd.GoToRecord , , acNext
        
    ElseIf Pats < 0 Then
Line_1: R = (Pats * -1)
        Pats = 0
        
        Debug.Print "Patients Waiting:  " & [Patients Waiting] & "  Weeks Waited:  " & [Weeks Waited] & " Spec Code:  " & [Spec_ID]
        
        
        DoCmd.GoToRecord , , acNext
    
        Pats = ([Patients Waiting] - R)
            
            If Pats >= 0 Then
                
                CurrentDb.Execute "UPDATE tblTemp SET [Patients Waiting]=" & Pats & " WHERE [Weeks Waited] = " & Me.Weeks_Waitedtxt & " AND [Spec_ID] =" & Me.Spec_IDtxt
                
        
                
            Else:
                            
                GoTo Line_1
                
            End If
            
    End If
    
   
    
    Next X
    
Next SID
    
'DoCmd.RunMacro "mcrmanipulatedata"
MsgBox "Data Manipulation Complete", vbInformation = vbOKOnly
DoCmd.Close acForm, "Form1"
End Sub

As far as I can tell the problem occurs because it is not looping in sequence, i.e, the loop is not going through all the specialties in the correct order, just as they appear in the list. The calculation for the patient amount is then skewed because of this.
Anyone got any ideas on how I can fix this? I'm at a dead end completely.
I can upload the DB if necessary.
Regards,
Ben

 

[boblarson]

As far as I can tell the problem occurs because it is not looping in sequence, i.e, the loop is not going through all the specialties in the correct order, just as they appear in the list.

Just an FYI for you - Data within tables are not stored in any particular order, and unless you specify an order (with an order by in a query) you are not guaranteed any particular order. So, set up a query with a sort and use that to loop through.

 

[DCrake]

Simple Software Solutions

Comments on your coding:

When declaring variables it is wise to define what type they are (Integer, Long, String,Etc)

When referfing to fields in a recordset prefix the field with the alias (Rst("FieldName"))


In your code you state that

X = [Weeks Waited]

Then in your loop you reset it.

For X = 52 to 0 Step -1

When using For ... Loop it automatically loops in a single step, therefore there is no need to use Step 1

Your nested If's are out of sync.

You start one If in the Start: sub and finish it in the Line_1: sub

Start:
If Pats >= 0 Then
'CurrentDb.Execute "UPDATE tblTemp SET [Patients Waiting]=" & Pats & " WHERE [Weeks Waited] = " & Me.Weeks_Waitedtxt & " AND [Spec_ID] =" & Me.Spec_IDtxt


DoCmd.GoToRecord , , acNext

ElseIf Pats < 0 Then
Line_1: R = (Pats * -1)
Pats = 0

Debug.Print "Patients Waiting: " & [Patients Waiting] & " Weeks Waited: " & [Weeks Waited] & " Spec Code: " & [Spec_ID]


DoCmd.GoToRecord , , acNext

Pats = ([Patients Waiting] - R)

If Pats >= 0 Then

CurrentDb.Execute "UPDATE tblTemp SET [Patients Waiting]=" & Pats & " WHERE [Weeks Waited] = " & Me.Weeks_Waitedtxt & " AND [Spec_ID] =" & Me.Spec_IDtxt



Else:

GoTo Line_1

End If

End If

CodeMaster:

 

[boblarson]

For X = 52 to 0 Step -1

When using For ... Loop it automatically loops in a single step, therefore there is no need to use Step 1

Actually, if you notice they are BACKWARDS stepping and in order to do that you MUST include the Step -1

 

[DCrake]

Simple Software Solutions

Now now Bob,

For SID = 1 To 19 Step 1
For X = 52 To 0 Step -1

The outer For is foward looping, the nested For is backwards. My comments only related to forward loops.

David

 

[Banana]

Actually, I'd think it a reasonable idea to explicitly state the steps if you are using nested loops with different steps, even ones that are using the default, if only for readability. If we preach explicit declaration of variables and its data types and disambiguation, why stop with optional arguments where they are used?

 

[DCrake]

Simple Software Solutions

The only time I ever use Step when performing loops would be in the following example

TmpStr = "1AAA2BBB3CCC4DDD5EEE"

For X = 1 to Len(TmpStr) Step 4
    Debug.Print Mid(TmpStr,X,4)
Next X

Results in

1AAA
2BBB
3CCC
4DDD
5EEE

This forces the loop to increment by 4 after each loop until the desired lenght is reached. This allows the user to split a string with any delimiters into single elements all of equal length, which could then be added to an array, for example.

David