Complex combo box

[StephanieHarris]

Hello,

I need to populate a combo box with unused pager numbers. My pager numbers can range from 1000 - 2098 and then 2400 - 2799. I have code that works but it is really slow. Please help me speed this up if possible...

Dim x As Boolean
    x = False
    Set rs = cn.Execute("Select Distinct PagerNumber From PagerInventory Where SiteCode = '" & dbCurrentSite & "' Order By PagerNumber ASC")
    Dim l As Integer
    For l = 1000 To 2098
        With rs
            rs.MoveFirst
            Do Until .EOF
                If rs(0) = l Then
                    x = True
                    Exit Do
                End If
                rs.MoveNext
            Loop
            
        End With
        If x = False Then
            cmbPagerNumber.AddItem l
        End If
        x = False
    Next l
    
    x = False
    Dim m As Integer
    For m = 2400 To 2799
        With rs
            rs.MoveFirst
            Do Until .EOF
                If rs(0) = m Then
                    x = True
                    Exit Do
                End If
                rs.MoveNext
            Loop
            
        End With
        If x = False Then
            cmbPagerNumber.AddItem m
        End If
        x = False
    Next m

Thanks,

Stephanie

[cavlab]

You could try placing the cn.execute function lower in the code and include an additional parameter in the SQL statement to do the check...

for l = 1000 to 2098
set rs = cn.execute("Select Distinct PagerNumber From PagerInventory Where (SiteCode = '" & dbCurrentSite & "' AND **whatever rs(0) is** = " & l & ")")
if rs.eof then
cmbPagerNumber.AddItem l
end if
next

Hopefully that will speed things up a bit.

Hope this helps.

Rod @ CAVLab

[StephanieHarris]

Thank you! That did indeed speed it up.

Stephanie