List box FindFirst, FindNext methods help?

[JC]

I have a list box loaded with Student Name, Social Security. When the
user click on a specific record, the following code is assigned to the
lstStudent1_AfterUpdate procedure.

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[StudentName] = '" & Me![lstStudent] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark


End Sub

This works great, but not when you have duplicate Student Name and
Social Security records in the table. At this point, the recorset uses
FindFirst and returns the first instance in the record-set. It never
moves to the next one.

How do I get my list box to move to the next record in the table when
you click on the next matching record in the list box?

Thank you to anyone that can assist,

Justin

 

[Wayne-I-M]

Hi JC

You should use the ID field of the record to search on. Set this as the
first column of your list box and set it's width to 0. This way you will
only see the names but the actual search is done on a unique number (assuming
that you are using autonumber as the primary field).

Private Sub lstStudent1_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[TableIDField] = '" & Me![FormIDField] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Ensure you have the ID field on the form (set it's Visible to =No)

 

[JC]

Wayne,

Thanks for the help!! I made all the changes, but I got a data
mis-match error when running it. Is there something else I need to do
with the code to designate numeric? Example.. date fields need #.

Thanks...

Justin

Hi JC

You should use the ID field of the record to search on. Set this as the
first column of your list box and set it's width to 0. This way you will
only see the names but the actual search is done on a unique number (assuming
that you are using autonumber as the primary field).

Private Sub lstStudent1_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[TableIDField] = '" & Me![FormIDField] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Ensure you have the ID field on the form (set it's Visible to =No)




--
Wayne
Manchester, England.
Enjoy whatever it is you do

I have a list box loaded with Student Name, Social Security. When the
user click on a specific record, the following code is assigned to the
lstStudent1_AfterUpdate procedure.

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[StudentName] = '" & Me![lstStudent] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark


End Sub

This works great, but not when you have duplicate Student Name and
Social Security records in the table. At this point, the recorset uses
FindFirst and returns the first instance in the record-set. It never
moves to the next one.

How do I get my list box to move to the next record in the table when
you click on the next matching record in the list box?

Thank you to anyone that can assist,

Justin

 

[JC]

Hey Wayne,

I got it...Access wanted me to change the numeric value to a string
character.

In SQL though it does not require one to convert values of any type.

Do you know why Access wants to do this?


Private Sub lstStudent_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![frmID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Thanks for you help!!!!

Justin

Hi JC

You should use the ID field of the record to search on. Set this as the
first column of your list box and set it's width to 0. This way you will
only see the names but the actual search is done on a unique number (assuming
that you are using autonumber as the primary field).

Private Sub lstStudent1_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[TableIDField] = '" & Me![FormIDField] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Ensure you have the ID field on the form (set it's Visible to =No)




--
Wayne
Manchester, England.
Enjoy whatever it is you do

I have a list box loaded with Student Name, Social Security. When the
user click on a specific record, the following code is assigned to the
lstStudent1_AfterUpdate procedure.

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[StudentName] = '" & Me![lstStudent] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark


End Sub

This works great, but not when you have duplicate Student Name and
Social Security records in the table. At this point, the recorset uses
FindFirst and returns the first instance in the record-set. It never
moves to the next one.

How do I get my list box to move to the next record in the table when
you click on the next matching record in the list box?

Thank you to anyone that can assist,

Justin

 

[JC]

Hey Wayne,

I have made the following changes to my code. 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

Hey Wayne,

I got it...Access wanted me to change the numeric value to a string
character.

In SQL though it does not require one to convert values of any type.

Do you know why Access wants to do this?


Private Sub lstStudent_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![frmID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Thanks for you help!!!!

Justin

Hi JC

You should use the ID field of the record to search on. Set this as the
first column of your list box and set it's width to 0. This way you will
only see the names but the actual search is done on a unique number (assuming
that you are using autonumber as the primary field).

Private Sub lstStudent1_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[TableIDField] = '" & Me![FormIDField] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Ensure you have the ID field on the form (set it's Visible to =No)




--
Wayne
Manchester, England.
Enjoy whatever it is you do

I have a list box loaded with Student Name, Social Security. When the
user click on a specific record, the following code is assigned to the
lstStudent1_AfterUpdate procedure.

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[StudentName] = '" & Me![lstStudent] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark


End Sub

This works great, but not when you have duplicate Student Name and
Social Security records in the table. At this point, the recorset uses
FindFirst and returns the first instance in the record-set. It never
moves to the next one.

How do I get my list box to move to the next record in the table when
you click on the next matching record in the list box?

Thank you to anyone that can assist,

Justin

 

[Graham Mandeno]

Hi Justin

Answered in your repost above...
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Hey Wayne,

I have made the following changes to my code. 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

Hey Wayne,

I got it...Access wanted me to change the numeric value to a string
character.

In SQL though it does not require one to convert values of any type.

Do you know why Access wants to do this?


Private Sub lstStudent_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![frmID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Thanks for you help!!!!

Justin

Hi JC

You should use the ID field of the record to search on. Set this as
the
first column of your list box and set it's width to 0. This way you
will
only see the names but the actual search is done on a unique number
(assuming
that you are using autonumber as the primary field).

Private Sub lstStudent1_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[TableIDField] = '" & Me![FormIDField] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Ensure you have the ID field on the form (set it's Visible to =No)




--
Wayne
Manchester, England.
Enjoy whatever it is you do


:

I have a list box loaded with Student Name, Social Security. When
the
user click on a specific record, the following code is assigned to
the
lstStudent1_AfterUpdate procedure.

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[StudentName] = '" & Me![lstStudent] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark


End Sub

This works great, but not when you have duplicate Student Name and
Social Security records in the table. At this point, the recorset
uses
FindFirst and returns the first instance in the record-set. It never
moves to the next one.

How do I get my list box to move to the next record in the table when
you click on the next matching record in the list box?

Thank you to anyone that can assist,

Justin

 

[JC]

Thanks Graham...

Regards,

Justin

Hi Justin

Answered in your repost above...
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Hey Wayne,

I have made the following changes to my code. 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

Hey Wayne,

I got it...Access wanted me to change the numeric value to a string
character.

In SQL though it does not require one to convert values of any type.

Do you know why Access wants to do this?


Private Sub lstStudent_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![frmID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Thanks for you help!!!!

Justin



Wayne-I-M wrote:
Hi JC

You should use the ID field of the record to search on. Set this as
the
first column of your list box and set it's width to 0. This way you
will
only see the names but the actual search is done on a unique number
(assuming
that you are using autonumber as the primary field).

Private Sub lstStudent1_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[TableIDField] = '" & Me![FormIDField] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Ensure you have the ID field on the form (set it's Visible to =No)




--
Wayne
Manchester, England.
Enjoy whatever it is you do


:

I have a list box loaded with Student Name, Social Security. When
the
user click on a specific record, the following code is assigned to
the
lstStudent1_AfterUpdate procedure.

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[StudentName] = '" & Me![lstStudent] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark


End Sub

This works great, but not when you have duplicate Student Name and
Social Security records in the table. At this point, the recorset
uses
FindFirst and returns the first instance in the record-set. It never
moves to the next one.

How do I get my list box to move to the next record in the table when
you click on the next matching record in the list box?

Thank you to anyone that can assist,

Justin