runtime error 3021

RecordCount does not always return the count because it depends on the type of recordset you open.
From the Help File:

Use the RecordCount property to find out how many records in a Recordset or TableDef object have been accessed. The RecordCount property doesn't indicate how many records are contained in a dynaset-, snapshot-, or forward-only–type Recordset object until all records have been accessed. Once the last record has been accessed, the RecordCount property indicates the total number of undeleted records in the Recordset or TableDef object. To force the last record to be accessed, use the MoveLast method on the Recordset object. You can also use an SQL Count function to determine the approximate number of records your query will return.

Note   Using the MoveLast method to populate a newly opened Recordset negatively impacts performance. Unless it is necessary to have an accurate RecordCount as soon as you open a Recordset, it's better to wait until you populate the Recordset with other portions of code before checking the RecordCount property.

As your application deletes records in a dynaset-type Recordset object, the value of the RecordCount property decreases. However, records deleted by other users aren't reflected by the RecordCount property until the current record is positioned to a deleted record. If you execute a transaction that affects the RecordCount property setting and you subsequently roll back the transaction, the RecordCount property won't reflect the actual number of remaining records.

The RecordCount property of a snapshot- or forward-only–type Recordset object isn't affected by changes in the underlying tables.

A Recordset or TableDef object with no records has a RecordCount property setting of 0.

When you work with linked TableDef objects, the RecordCount property setting is always –1.

Using the Requery method on a Recordset object resets the RecordCount property just as if the query were re-executed.

Hope this helps -
Laurie

Hi claracruz. Try changing your code from the rec.open line to this:

rec.Open strName, con, adOpenStatic, adLockReadOnly

with rec
if .eof and .bof then
  'do something or nothing
else
  .movefirst
  txtBranchName.Text = rec.Fields("Branch Name")
end if

end with

Hope that works for you :)

I have tried everything, it still doesn't work, anymore suggestion please.

by the way, there are 30 branch codes and so bracnh names. and the error occurs when the branch code changes from one to another. however, when i use the lost_focus event with same codes, the program works fine

Are you using a datagrid or datacontrol? or just accessing the recordset directly?
Is the user changing the record in any way?
Laurie

ok, ifigured out i was using a code that did not exist in my database, this was causing the error. however the syntax error(see above) still occurs at the end of file. help!!!

where is the cursor located?
what type of locking are you using?
Perhaps there is the problem.
The best results I've found using ADO is with cursorlocation=aduseclient and using adlockoptimistic if you want to update or addnew recordset  or adlockreadlonly if U just want to read the recordset


Hope this help U

I noticed that you are opening the connection over and over, perhaps it would be better practice to open the connection onformload and close it onformunload.
Try this:

Private Sub txtBranchNo_change()
    Dim con As ADODB.Connection
    Dim rec As ADODB.Recordset
    Dim strName As String
    Set con = New ADODB.Connection
    Set rec = New ADODB.Recordset
    Dim ctr as Integer
  strName "SELECT COUNT(*) FROM PHARMACYBRANCHCODES Where PHARMACYBRANCHCODES.[Branch No] =" & txtBranchNo.Text & ""
    con.Open "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:\fyfy\hguyg.mdb"
    rec.Open strName, con
    ctr =  rec(0)
    rec.Close
  If ctr > 0 Then
     strName = "Select PHARMACYBRANCHCODES.[Branch No],PHARMACYBRANCHCODES.[Branch Name] From PHARMACYBRANCHCODES Where PHARMACYBRANCHCODES.[Branch No] =" & txtBranchNo.Text & ""
       rec.Open strName, con
      rec.MoveFirst
     txtBranchName.Text = rec.Fields("Branch Name")
     rec.Close
    Else
     MsgBox "Zero Records"
   End if
   con.Close
End Sub

you have run out cursur,Just check the recordset to see if you are at eof

if rs.eof then

    msgbox 'Cannot position beyond end of recordset

!!',vbokonly,'Positioning error'

    ' You could then reposition back to first record

    rs.movefirst

    '  or move back a record

    rs.moveprevious

end if