Access Loop Combo

[firasqureshi]

I am new in Access and stuck on loop issue. I have two combo which are getting values from a temp table. Temp table has only one column. This table gets updated values everytime. Temp table contains value from 1 - 10. These values are used as defining range to select toggle button from 1 - 10.

Scenario: if start combo selct 9 and then on got foucs of last combo data appears only 8 & 7 because 6 does not exist in the table and here i want to get out of loop.

Table Values: 10,9,8,7,4,3,2, 1
Start Combo Value (lets say) 9
then end combo should show from 8 till 7 and does not show remaining values from the table. basically loop should break if sequence break be reading from table.

Below code is working fine in brnging whole list from table but i want to loop out as soon as sequence break based on the table recordset.

Private Sub CmbRouteEnd_GotFocus()
Dim lListIndex As Long
Dim iCounter, iCheck As Integer
    
    With Me.CmbRouteEnd
'        .SetFocus
        For lListIndex = .ListCount - 1 To 0 Step -1
            .RemoveItem (lListIndex)
        Next lListIndex
'        .SelText = ""
    End With

On Error GoTo UserForm_Initialize_Err
    Dim dbs As Database
    Dim rst As Recordset
    
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM temp ORDER BY temp.ID Desc;", dbOpenSnapshot, adopenstatic)
    iCounter = cmbRouteStart
    
    iCheck = cmbRouteStart - 1
    rst.MoveFirst
    With Me.CmbRouteEnd
        
        Do
            If CInt(rst![ID]) = iCheck Then
                    If CInt(rst![ID]) < iCounter Then
                        .AddItem rst![ID]
                    ElseIf CInt(rst![ID]) > iCounter Then
                        .AddItem rst![ID]
                    End If
            End If
            iCheck = iCheck - 1
            rst.MoveNext
        Loop Until rst.EOF
     End With
UserForm_Initialize_Exit:
    On Error Resume Next
    rst.Close
    'cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
UserForm_Initialize_Err:
    MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
    Resume UserForm_Initialize_Exit
End Sub

[jimatqsi]

1. You must use code tags when you post lines of code. Highlight your code and click the little # icon.
2. Some comments in your code would be very helpful. Is that first loop supposed to clear out your list?
3. Your scenario description is very confusing. I'm not sure what you want.
4. Why not change

If CInt(rst![ID]) < iCounter Then
.AddItem rst![ID]
ElseIf CInt(rst![ID]) > iCounter Then
.AddItem rst![ID]
End If

to

' if iCounter and the ID are not the same then add the item
If CInt(rst![ID]) <> iCounter Then
.AddItem rst![ID]
End If

5. What if the recordset is empty? Is that a possibility?

It's possible it will help you to know that you can do a "exit do" command like this

' get out of the loop if x = y
if x = y then exit do

which will break you out of a do loop. But I'm so unclear about what you're trying to do that I cannot guess at the exact code to give you. Maybe what I did give you is enough to help.

Jim

[firasqureshi]

Hi Jim, Thanks for the response. Your comments have given me another direction of thinking. I have succeeded half part of the loop.

First questions to your answers.

First part of the code is to clear out the list in combo and second part is to refill the list based on the values acquiring through loop. I have done below changes to the code and finds it working if loop is going down. I mean from the selection value till 1. But from the max value which is 10 till the selected value, i need to control it.

Recent Code:

Do
            If CInt(rst![ID]) < iCounter Then
                If CInt(rst![ID]) = iDown Then
                 .AddItem rst![ID]     'less from what selected
                 iDown = iDown - 1
                 Else
                    'Exit Do
                End If
                 
            ElseIf CInt(rst![ID]) > iCounter Then
                If CInt(rst![ID]) <> iUp Then
                 .AddItem rst![ID]      'from 10 till selected value
                 iUp = iUp + 1              
                 End If
            End If
            
            'iDown = iDown - 1
            rst.MoveNext
        Loop Until rst.EOF

Lets suppose temp table values are: 10,9,6,5,4,3,2, 1
Values not available in temp table: 8,7
Loop Build: Anti Clockwise or Counter clockwise
Select value from the start Combo: 5
Desired results: 4,3,2,1,10,9
Success: if select 5 then 4,3,2,1 are coming from the first part of if condition.
Failure: When loop goes to elseif part. it should add 10 & 9 only as 8 & 7 do not exist in values and since reverse sequence break at 8 so it should ignore 6 as well.
I hope this time i have explained well. Please assist

[NeoPa]

From your originally posted code ...
Try changing line #26 to :

If CInt(rst![ID]) < iCheck Then Exit Do

Remove line #32 and remove an indent from the lines between.