unable to assign recordset result to variable in access 2003

[charis89]

I have a form with a button, when clicked, it performs the following

Private Sub btnAllList_Click()


Dim dbTemp As DAO.Database
Dim rstTemp As DAO.Recordset
Dim minNo, maxNo As Integer


Set dbTemp = CurrentDb


strSQL = "SELECT Min([id_no]) AS FirstNo, Max([id_no]) AS LastNo FROM T_RESULT;"


Set rstTemp = dbTemp.OpenRecordset(strSQL)


minNo = 0
maxNo = 0


If Not (rstTemp.BOF And rstTemp.EOF) Then
    If rstTemp.RecordCount <> 0 Then
        Do While Not rstTemp.EOF
            minNo = rstTemp![FirstNo]
            maxNo = rstTemp![LastNo]
            MsgBox "minNo: " & minNo & ", rstTemp![LastNo]: " & rstTemp![LastNo] & ", maxNo: " & maxNo
            Exit Do
        Loop
    End If
End If


rstTemp.Close


Exit_btnAllList_Click:
    Exit Sub


Err_btnAllList_Click:
    MsgBox Err.Description
    Resume Exit_btnAllList_Click
End Sub

What I tried to do so far is get the minimum and maximum id no from the table T_RESULT. Message box shows "minNo: 284, rstTemp![LastNo]: 290, maxNo: 0".
Not sure why maxNo is not getting 290.
Datatype of id no in T_RESULT is Number.

[June7]

Why are you looping recordset? That query will have no more than one record.

If Not rst.EOF Then
minNo = rstTemp![FirstNo]
maxNo = rstTemp![LastNo]
End If
MsgBox "FirstNo: " & minNo & ", LastNo: " & maxNo

minNo variable type is not specified, maxNo is set as Integer. What number type is id_no field? An autonumber (long integer)?

Every variable must have its own type specified or it will default to Variant. Variant is okay.

Dim minNo As Long, maxNo As Long

[charis89]

The number type for the field is long integer.
I changed it to long and it works now. Thanks!!