List Box on main form

[pete]

Hi
I have a list box on my main form which shows all entries in the database. I
am really struggling now, this is what I have so far and it works.

' Find the record that matches the control.
Dim rs As Object

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

What I want to do is when I click a particular record the record shows in
the relevant fields on the form ready for editing. My find next record button
just goes down the list, not very practicle when I can run up over a thousand
records a month.
Thanks
Pete

 

[Jack Leach]

You're close, but rather than checking for the rs.EOF, check the rs.NoMatch
property when using FindFirst

With Me.Recordsetclone
.FindFirst "[AssetNos] = '" & Me![List80] & "'"
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[pete]

Hi
thanks for the help it works fine here at home where I'm using 2007, but
when I get it to work - on 2003, it works partially. I've converted it at
home to 2003, got it to work converted it again but no matter how hard I try
I keep getting the same problem.
The problem is that not all the fields are showing the data, the columns are
there the data is in the database, but just not showing where I want them,
very frustrating
Excellent site this for beginners like me, everyone seems so friendly and
helpful
Thanks
pete

But for direct navigation purposes, I think it would be
better to use the form's recordset directly instead of a
recordset clone:

Sheesh, I skipped the rest of the code for using the form's
recordset:

The find button's code would be like:

Dim strMark As String
strMark = Me.Bookmark
With Me.Recordset
.FindFirst "[AssetNos] = '" & Me![List80] & "'"
If .NoMatch Then
Me.Bookmark = strMark
Beep
End If
End With

and the next button is the same using FindNext instead of
FindFirst.

If there is any chance that the form might not have any
records then you should probably add a check for
..RecordCount > 0 before searching.