By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,655 Members | 1,860 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,655 IT Pros & Developers. It's quick & easy.

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

P: 3
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)
Text8 = rs.RecordCount
Text6 = rs!card
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.
Nov 16 '06 #1
Share this Question
Share on Google+
4 Replies

Expert Mod 15k+
P: 31,487
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.
Nov 16 '06 #2

Expert Mod 10K+
P: 14,534
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:

Expand|Select|Wrap|Line Numbers
  2. Private Sub Text4_LostFocus()
  3. Dim dbs As Database
  4. Dim rs As Recordset
  5. Dim sql As String
  6. Dim i As Integer
  7. On Error Resume Next
  9.   Set dbs = CurrentDb()
  10.   sql = "select * from tbl_InsertPicture where pname = '" & Text4 & "'"
  11.   Set rs = dbs.OpenRecordset(sql)
  12.   rs.MoveLast
  13.   rs.MoveFirst
  14.   Me.Text8 = rs.RecordCount
  16.   For i=1 To rs.RecordCount
  17.     Me.Text6 = rs!card
  18.     rs.MoveNext ' will move to the next record in the recordset
  19.     ' this will move to the next record in the form
  20.     DoCmd.GoToRecord acDataForm, "YourFormName", acNext
  21.   Next i
  23.   rs.Close
  24.   Set rs = Nothing ' always reset the recordset
  25.   Set db = Nothing ' always reset the database
  27. End Sub
Nov 16 '06 #3

Expert 2.5K+
P: 3,072
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
' no value, so deactivate the filter
me.filteron = false

Getting the idea ?

Nov 16 '06 #4

Expert 100+
P: 1,418
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!
Nov 18 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.