Recorset.Clone question, help

J

JC

I have made the following changes to my code. I am trying to update
recordset from a list box with the values the user clicks. It worked,
but I want to add another criteria to it. I would like to feed a text
box entry to filter the recordset. Please see below, the part "...I
cannot get to work" is the one I am wondering about. Can I add "AND"
logic to filter the recorset?


Private Sub lstStudentInfo_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![lstStudentInfo], 0))
&
-This is the part I cannot get to work
"And " & [adTermCode] = [Forms]![frmData]![txtTerm]


If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Thank you for your help,

Justin
 
G

Graham Mandeno

Hi Justin

First, you should use Me.RecordsetClone, not Me.Recordset.Clone. The form
has a clone of its recordset already conveniently created for you, so it's
very unusual to have to create another one.

The problem with the And clause is that (1) you do not have a space before
the "And" and (2) your fieldname ([adTermCode]) should be part of your
string (inside the quotes). The Str function is OK, but unnecessary. Try
this:

rs.FindFirst "[ID] = " & Nz(Me![lstStudentInfo], 0) _
& " And [adTermCode] = " & [Forms]![frmData]![txtTerm]

Also, you should be checking NoMatch, not EOF (assuming this is an MDB not
an ADP):

If Not rs.NoMatch Then ...

If this IS an ADP, then I believe the form's Recordset (and its clone) will
be an ADODB.Recordset, which does not support AND and OR in the FindFirst
method.
 
J

JC

Looks good Graham,

I will work that into my code.

Thank you,

Justin

Graham said:
Hi Justin

First, you should use Me.RecordsetClone, not Me.Recordset.Clone. The form
has a clone of its recordset already conveniently created for you, so it's
very unusual to have to create another one.

The problem with the And clause is that (1) you do not have a space before
the "And" and (2) your fieldname ([adTermCode]) should be part of your
string (inside the quotes). The Str function is OK, but unnecessary. Try
this:

rs.FindFirst "[ID] = " & Nz(Me![lstStudentInfo], 0) _
& " And [adTermCode] = " & [Forms]![frmData]![txtTerm]

Also, you should be checking NoMatch, not EOF (assuming this is an MDB not
an ADP):

If Not rs.NoMatch Then ...

If this IS an ADP, then I believe the form's Recordset (and its clone) will
be an ADODB.Recordset, which does not support AND and OR in the FindFirst
method.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

JC said:
I have made the following changes to my code. I am trying to update
recordset from a list box with the values the user clicks. It worked,
but I want to add another criteria to it. I would like to feed a text
box entry to filter the recordset. Please see below, the part "...I
cannot get to work" is the one I am wondering about. Can I add "AND"
logic to filter the recorset?


Private Sub lstStudentInfo_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![lstStudentInfo], 0))
&
-This is the part I cannot get to work
"And " & [adTermCode] = [Forms]![frmData]![txtTerm]


If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Thank you for your help,

Justin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top