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

 

[Marshall Barton]

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

In spite of what the wizard does, unless you are using ADO
recordsets for your form, that should be:

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

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.

Your next matching record button should look more like:

With Me.RecordsetClone
.FindNext "[AssetNos] = '" & Me![List80] & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Else
Beep
End If
End With

But for direct navigation purposes, I think it would be
better to use the form's recordset directly instead of a
recordset clone:
With Me.Recordset
You might some day have another use for the RecordsetClone
and then you would not be able to rely on the starting point
for FindNext.

 

[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)

 

[Marshall Barton]

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.

 

[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.

 

[Marshall Barton]

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

I douvt that the version of Access has anything to do with
data being displayed ot not. It kind of sounds like
something in the data tables at home are different from the
tables at work.