How to indicate FindNext has stopped finding

[reddwarf999]

Hi all,

Access/VBA noob using Access 2003. My DB is for loaning equipment to users. Got pretty much everything working well but someone asked if this could be done: When the Find Next button reaches the last record matching criteria, have a MsgBox indicate so to the user ie: "Search complete.No further records found."

Form is bound to a table. Find Next button is unbound with fol code:

Private Sub cmdModelFindNext_Click()
On Error GoTo Err_cmdModelFindNext_Click
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone

rst.FindNext "[Model]='" & Me.Model & "'"
Me.Bookmark = rst.Bookmark

Exit_cmdModelFindNext_Click:
Set rst = Nothing
Exit Sub
Err_cmdModelFindNext_Click:
MsgBox Err.Description
Resume Exit_cmdModelFindNext_Click

End Sub


It has no problem finding the next record. When it reaches the last matching record it remains on that record and some of the Users think it has locked up and is not at the last matching record.

I've tried using rst.EOF in a variety of ways to call a MsgBox to no avail.
Any suggestions would be appreciated. Thanks in advance.

Doug

P.S. 99.9% of my VBA knowledge comes from forums like this. The other .1% is lost due to memory leakage.

 

[Isskint]

hi reddwarf999

Declare an extra vaiable

Dim lngRecCount as Long

After you have done the

Set rst = Me.RecordsetClone

do this;

rst.MoveLast
lngRecCount=rst.recordCount
rst.MoveFirst

Then you check the current record number against your record count to see if you have reached the end of the dataset

 

[spikepl]

Learn to use the documentation. USe the help file in the application by pressing F1 ... and google, to get a structured view of the Access functions, objects, methods etc. Most come with examples. Chuck rst.FindNext into Google and read how to determine whether the search was successful or not.

 

[reddwarf999]

Thank you so much Isskint and Spikepl.

Isskint,

I did as you told me and the button wouldn't work anymore so I added
rst.Bookmark = Me.Bookmark right after your rst.MoveFirst
and it worked like a charm.

Spikepl
Thank you for the pointers. I usually do as you suggested and in this case I did also. I think the plethora of info out there oversaturated my grey matter causing my memory leakage to exceed the .1% average.
Your google suggestion prompted me to use rst.Nomatch to call the MsgBox.
I had tried using it previously before Isskint's rst.recordcount suggestion and it didn't work. So I assume a count of the records must be done before using NoMatch?

Anyways once again a very big thank you to both
Here's the latest code:

Private Sub cmdModelFindNext_Click()
On Error GoTo Err_cmdModelFindNext_Click
Dim rst As DAO.Recordset
Dim lngRecCount As Long
Set rst = Me.RecordsetClone
rst.MoveLast
lngRecCount = rst.RecordCount
rst.MoveFirst
rst.Bookmark = Me.Bookmark
rst.FindNext "[Model]='" & Me.Model & "'"
Me.Bookmark = rst.Bookmark

If rst.NoMatch = True Then
MsgBox "No further records found.", vbOKOnly, "Search Complete"
End If

Exit_cmdModelFindNext_Click:
Set rst = Nothing
Exit Sub
Err_cmdModelFindNext_Click:
MsgBox Err.Description
Resume Exit_cmdModelFindNext_Click

End Sub