eof or bof not working

[vicsaccess]

I have the following code that is setting some command and text buttons based on a recordset. works great until it comes across a record with only one record, then it fails.

    Set db = CurrentDb
    x = 1
    strprod = ("SELECT [tag#],coilid from coilT where dailyprodid=" & strpro)
    Debug.Print (strprod)
    Set rs = CurrentDb.OpenRecordset(strprod, dbOpenDynaset)
    With rs
    If Not .EOF And Not .BOF Then
        .MoveLast
        .MoveFirst
        While (Not .EOF)
        strcmd = "cmdcoil" & x
        strtxt = "txt" & x
        strtxtc = "txtc" & x
        Me.Controls(strtxt) = rs![tag#]
        Me.Controls(strcmd).Caption = rs![tag#]
        Me.Controls(strtxtc) = rs!CoilID
        .MoveNext
        Wend
    Debug.Print (x)
    End If
    .Close
    End With

I have tried several versions of record checks but can not get it to recognize records with only one record. For a normal record the .EOF & .BOF are returning false but with one record i am getting true. record count was also no help, apparently since this is a SQL instead of table it does not return a correct recordcount. anyone have any ideals?

[davegri]

[CODE] Set db = CurrentDb x = 1
strprod = ("SELECT [tag#],coilid from coilT where dailyprodid=" & strpro)
Debug.Print (strprod)
Set rs = CurrentDb.OpenRecordset(strprod, dbOpenDynaset)
With rs
debug.print "file contains " & rs.count & " records."
' If Not .EOF And Not .BOF Then
.MoveLast
.MoveFirst
While (Not .EOF)
strcmd = "cmdcoil" & x
strtxt = "txt" & x
strtxtc = "txtc" & x
Me.Controls(strtxt) = rs![tag#]
Me.Controls(strcmd).Caption = rs![tag#]
Me.Controls(strtxtc) = rs!CoilID
.MoveNext
Wend
Debug.Print (x)
' End If
.Close
end with

How about just commenting out the if and end if. Notice the new debug.print rs.count

Not sure what you mean about records with only one record or what a normal record is.

[vicsaccess]

Without the if statement i get an error 3021 for no record found. Sorry, not sure where it was now that i read the article. I've been working on trying to find a answer to this for a few hours and read a lot of different things. The basics of what the article said was that a record count method was dependable for record sets based on tabled, but if they was based on a query they was not dependable. makes sense because my record count the first time thru the loop is always 0 for 0-1 record and 1 for more than 1 record. once it gets down into the assigning variables the record count changes to the actual count.