Recordset can not move to other records, need some help!

[chaekung]

I have problem when open the record set as below code :

Private Sub Text4_LostFocus()
Dim dbs As Database
Dim rs As Recordset
Dim sql As String
On Error Resume Next
Set dbs = CurrentDb()
sql = "select * from tbl_InsertPicture where pname = '" & Text4 & "'"
Set rs = dbs.OpenRecordset(sql)
rs.MoveLast
rs.MoveFirst
Text8 = rs.RecordCount
Text6 = rs!card
rs.Close
End Sub


Data in Table tbl_InsertPicture
ID card fname lname
1 31001 A X1
2 31002 B X2
3 31005 C X3
4 31009 D X4
5 31100 B X5
6 31111 C X6

Text4 serch the name "C", in form show text6 = 31005 and text8 = 2 that but when I click next on the navigator button form still show text6 = 31005 but acctually text6 have to show 31111, I don't understand why the record are not able to move to other records.

Please help me, what wrong on my code and how to solve this problem.

Thank you very much.

[NeoPa]

Try setting a breakpoint at the beginning of your code and tracing through it (use F8 to step or Shift-F8 to step over lines, regardless of procedure calls within a line).

See if the code is actually being executed.
See what the settings are in the code when you step through.

[MMcCarthy]

You are not actually moving through the records either in the recordset or in the form. My instinct says there is a better way of doing what you are trying to do. But, to follow your design you will need the following:

 
Private Sub Text4_LostFocus()
Dim dbs As Database
Dim rs As Recordset
Dim sql As String
Dim i As Integer
On Error Resume Next
 
  Set dbs = CurrentDb()
  sql = "select * from tbl_InsertPicture where pname = '" & Text4 & "'"
  Set rs = dbs.OpenRecordset(sql)
  rs.MoveLast
  rs.MoveFirst
  Me.Text8 = rs.RecordCount
 
  For i=1 To rs.RecordCount
    Me.Text6 = rs!card
    rs.MoveNext ' will move to the next record in the recordset
    ' this will move to the next record in the form
    DoCmd.GoToRecord acDataForm, "YourFormName", acNext
  Next i
 
  rs.Close
  Set rs = Nothing ' always reset the recordset
  Set db = Nothing ' always reset the database
 
End Sub
 
 

[nico5038]

When you have a bound form to the table it's better to use the filter to select a subset of rows.
This works e.g. behind a [Filter] button besides the [txtValue] field like:

' Test field is filled
if len(nz(me.txtValue))> 0 then
' set the fiter by supplying the fieldname and (here with surrounding single quotes as it's a text field) the value to filter for
me.filter = "Code='" & Me.txtValue & "'"
' activate the filter
me.filteron = true
else
' no value, so deactivate the filter
me.filteron = false
endif

Getting the idea ?

Nic;o)

[PEB]

It seems that u use unbound form...

Once u use a bound form the number of records are in the left bottom
the curreent recorsd is also mentionned and Your respective card is displayed in the bound field for it!

So assign in the form properties RecordSource your table names

In your text 4 set the field Card as control Source property

And navigate with the boutons in the bottom left...

if you want to do your own navigation you can do it using wizards!