Application keeps crashing

[joeythelips]

Hi,

I have a command button on a form that should return numbers that are missing from a sequence.
However when i press the button, it stalls and does not return the missing numbers.
The field filename usually appears in the form "Ongoing File N0.194"

If anyone can see any obvious problems perhaps they could give me a shout.
This probelm has only started since i put 90 records in the table.
When there was only 2 there was no problem

This is my code
Private Sub Command11_Click()

Dim db As Database
Dim rsmyset As Recordset
Dim MyNum As Integer
Dim MyMiss As String
Dim MyX As Integer

Set db = CurrentDb()
Set rsmyset = db.OpenRecordset("SELECT Right([reference1.Filename],3) AS [File No] FROM Reference1 ORDER BY Right([Reference1.Filename],3); ", dbOpenSnapshot)
rsmyset.MoveLast
MyNum = rsmyset("file no&quot
rsmyset.MoveFirst
MyMiss = "0"
Do
For MyX = 180 To MyNum
If MyX = rsmyset("file no&quot Then
rsmyset.MoveNext
Else
MyMiss = IIf(MyMiss = "0", MyX, MyMiss & ", " & MyX)
End If
Next MyX
Loop Until rsmyset.EOF
rsmyset.Close

Set rsmyset = Nothing
Set db = Nothing
msgbox "Alert: We are missing the following Readings Files: " & MyMiss



End Sub

 

[Chance1234]

try

Do Until rsmyset.EOF
For MyX = 180 To MyNum
If MyX = rsmyset("file no&quot Then
rsmyset.MoveNext
Else
MyMiss = IIf(MyMiss = "0", MyX, MyMiss & ", " & MyX)
End If
Next MyX

Loop

 

[Aivars]

Your recorsed is ordered by string expression. So ordering of them do following:
1,11,12...,2,20,21,22...,9,91...

Make changes in this string

Set rsmyset = db.OpenRecordset("SELECT Right([reference1.Filename],3) AS [File No] FROM Reference1 ORDER BY val(Right([Reference1.Filename],3)); ", dbOpenSnapshot)

Aivars