Testing for BOF & EOF?

[Dillinger4]

Hi all! I keep getting a runtime error when i click
the cmdPrevious button but for some reason when the
Next Button is clicked there is no runtime error generated?
Why is that. And when the last record is reached no msgbox pops up. Why is that? Thanks


Private Sub cmdNext_Click()
Set db = OpenDatabase("C:\ToolBox\CompanyInventory97.mdb")
Set rs = db.OpenRecordset("ProductDecrption")
If rs.EOF Then
MsgBox "This is the end of the records"
Else
rs.MoveNext
txtProductID.Text = rs![Product ID]
txtName.Text = rs!Name
txtColor.Text = rs!Color
End If
rs.Close
End Sub


Private Sub cmdPrevious_Click()
Set db = OpenDatabase("C:\ToolBox\CompanyInventory97.mdb")
Set rs = db.OpenRecordset("ProductDecrption")
If rs.BOF Then
msbox "This is BOF"
Else
rs.MovePrevious
txtProductID.Text = rs![Product ID]
txtName.Text = rs!Name
txtColor.Text = rs!Color

rs.Close
End Sub

[davidrobin]

Without testing the code I would suggest you check your if ....endif control structure. You don't have an END IF line in your cmdPrevious_click procedure.

The other thing I would bear in mind is the type of cursor you are using. i.e. forward only, etc.

[sanon]

I don't think your program will work properly because your logic is wrong in the first place. According to your code, you will get the same data at the same position all the time because every time you click either next or previous button, you connect to the database and get the data. The solution is you have to remove those 2 lines that connect and retrive data from the database to some place else, such as Form_Load().

Private Sub Form_Load()
   Set db = OpenDatabase("C:\ToolBox\CompanyInventory97.mdb") 
   Set rs = db.OpenRecordset("ProductDecrption") 
End Sub

Private Sub cmdNext_Click() 
   If rs.EOF Then 
      MsgBox "This is the end of the records" 
   Else 
      rs.MoveNext 
      txtProductID.Text = rs![Product ID] 
      txtName.Text = rs!Name 
      txtColor.Text = rs!Color 
   End If 
End Sub 

Private Sub cmdPrevious_Click() 
   If rs.BOF Then 
      MsgBox "This is BOF" 
   Else 
      rs.MovePrevious 
      txtProductID.Text = rs![Product ID] 
      txtName.Text = rs!Name 
      txtColor.Text = rs!Color 
   End If
End Sub

[Dillinger4]

Thanks for your help Sanon. Yeah i realized right
after i entered that post that my logic was wrong.
I dont know why i was thinking i had to open
up the recordset multiple times. I changed my code
and every thing works but i had the exact code you
had and for some reason it doesnt want to test for
BOF or EOF. SO what i used is not very good but
it's for a small school project so it's no big deal.

Dim recposition As Integer

Private Sub cmdNext_Click()
If recposition = rs.RecordCount Then
MsgBox "EOF has been reached. There are no more records to be read"
Else
rs.MoveNext
recposition = recposition + 1
txtProductID.Text = rs![Product ID]
txtName.Text = rs!Name
txtColor.Text = rs!Color
cmdPrevious.Enabled = True
End If
End Sub

I shouldnt have to use a counter but that's the
only way for now to get it to work. Thanks again for
you help!

[sanon]

Try this

Set rs = db.OpenRecordset("ProductDecrption",dbOpenSnapshot)

I'm not sure abuot DAO, but, basically, you have to specify the type of recordset when open it. However, if you can use ADO instead of DAO, I can tell you for sure that the following statement will work as you wanted..

rs.Open strSQL, objConn, adOpenStatic, adLockReadOnly