Code help please

[Afrosheen via AccessMonster.com]

When looking for a record that has duplicate last names I can press cmdNext
to get the next record. The source control is based on a master query. Once I
keep pressing the cmdNext it will find all the codes. When it gets to the end
of the cmdNext I would like something to say that this is the end of the
records. I don't believe it is a recordset and I'm not sure how to check and
see if it is.

This is the code for the cmdNext. Your help would be appreciated. Thanks in
advance.

Private Sub cmdNext_Click()
On Error GoTo Err_cmdNext_Click

Screen.PreviousControl.SetFocus
DoCmd.FindNext

Exit_cmdNext_Click:
Exit Sub

Err_cmdNext_Click:
MsgBox Err.Description
Resume Exit_cmdNext_Click

End Sub

 

[Tom van Stiphout]

On Sat, 27 Feb 2010 15:54:25 GMT, "Afrosheen via AccessMonster.com"

An alternative to writing code may be for you to Filter rather than
Find.

-Tom.
Microsoft Access MVP

 

[DevlinM]

Have you considered using a filter to retrieve only records with the desired
last name? This would seem more efficient than searching individual records.


If a search is your desire then you would want to do something like this:

Private Sub FindARecord(Criteria As String)
'This search will begin at the current position in the recordset
Dim rs As Recordset
Set rs = Me.RecordsetClone
With rs
If (Not rs.EOF) Then
.FindNext Criteria
If (Not .NoMatch) Then
Me.Bookmark = rs.Bookmark
Else
MsgBox "Search item cound not be found", vbInformation,
"Item Not Found"
End If
Else
MsgBox "No further records to search!", vbInformation, "No More
Records"
End If
End With
End Sub

 

[Afrosheen via AccessMonster.com]

A question to Tom. This form is part of a subform. Can you set up a filter on
the sub form?

DevlinM. I take it Private Sub FindARecord(Criteria As String) is a text box?
The find next is a command button

Thanks for replying.

Have you considered using a filter to retrieve only records with the desired
last name? This would seem more efficient than searching individual records.


If a search is your desire then you would want to do something like this:

Private Sub FindARecord(Criteria As String)
'This search will begin at the current position in the recordset
Dim rs As Recordset
Set rs = Me.RecordsetClone
With rs
If (Not rs.EOF) Then
.FindNext Criteria
If (Not .NoMatch) Then
Me.Bookmark = rs.Bookmark
Else
MsgBox "Search item cound not be found", vbInformation,
"Item Not Found"
End If
Else
MsgBox "No further records to search!", vbInformation, "No More
Records"
End If
End With
End Sub

When looking for a record that has duplicate last names I can press cmdNext
to get the next record. The source control is based on a master query. Once I

[quoted text clipped - 20 lines]

End Sub

 

[Afrosheen via AccessMonster.com]

DevlinM, I tried your code and what was happening is that it would not find
the next record. For example I put on Tom. It found the first Tom but then it
said from the MsgBox "Search Item could not be found. There are two Tom's in
the file.

The code that I originally posted worked. Its just when it gets to the last
record and I press Find Next it sits there showing the last name. I am at the
end of the records. I just wanted to display something like your Msgbox
saying that this is the end of the records.

Have you considered using a filter to retrieve only records with the desired
last name? This would seem more efficient than searching individual records.


If a search is your desire then you would want to do something like this:

Private Sub FindARecord(Criteria As String)
'This search will begin at the current position in the recordset
Dim rs As Recordset
Set rs = Me.RecordsetClone
With rs
If (Not rs.EOF) Then
.FindNext Criteria
If (Not .NoMatch) Then
Me.Bookmark = rs.Bookmark
Else
MsgBox "Search item cound not be found", vbInformation,
"Item Not Found"
End If
Else
MsgBox "No further records to search!", vbInformation, "No More
Records"
End If
End With
End Sub

When looking for a record that has duplicate last names I can press cmdNext
to get the next record. The source control is based on a master query. Once I

[quoted text clipped - 20 lines]

End Sub

 

[DevlinM]

First, let me take you back to my original question: Have you considered
using a filter to retrieve only records with the desired last name? This
would seem more efficient than searching individual records. Why are you
bypassing the power of database to do step-searches?

Anyway, if you use the Docmd.Find... Methods, you must start out with the
Docmd.FindRecord and supply your criteria there. Once you've done that, you
can use the Docmd.FindNext method. FindNext takes no arguments. The problem
here is that you will need to check your position in the recordset and the
data in the particular field to determine if you found anything at all before
attempting to do a FindNext. You might as well use the code I supplied.

So let's try this again...

Private Function FindARecord(Criteria As String) as boolean
'This search will begin at the current position in the recordset
Dim rs As DAO.Recordset
Dim bReturn as Boolean

Set rs = Me.RecordsetClone
With rs
If (Not rs.EOF) Then
'FindNext will begin at the current record and search to EOF
'If you want to begin at the first record, use FindFirst. For
more info, see help.
.FindNext Criteria
If (Not .NoMatch) Then
Me.Bookmark = rs.Bookmark
bReturn=True
Else
MsgBox "Search item cound not be found", vbInformation,
"Item Not Found"
End If
Else
MsgBox "No further records to search!", vbInformation, "No More
Records"
End If
End With
Set rs=Nothing
FindARecord=bReturn
End Function

Private Sub CmdSearch_Click()
Dim sResponse as string
Dim bSearchAgain as Boolean

sResponse = InputBox(“Enter Your Criteria Hereâ€,â€Inut Requiredâ€)

if(len(sResponse)>0) then
Do
If(FindARecord(sResponse)) then
bSearchAgain = (MsgBox(“Search Again?â€,
vbQuestion+vbYesNo,â€Messageâ€)=vbYes)
End If
Loop While bSearchAgain
End If
End Sub

DevlinM, I tried your code and what was happening is that it would not find
the next record. For example I put on Tom. It found the first Tom but then it
said from the MsgBox "Search Item could not be found. There are two Tom's in
the file.

The code that I originally posted worked. Its just when it gets to the last
record and I press Find Next it sits there showing the last name. I am at the
end of the records. I just wanted to display something like your Msgbox
saying that this is the end of the records.

Have you considered using a filter to retrieve only records with the desired
last name? This would seem more efficient than searching individual records.


If a search is your desire then you would want to do something like this:

Private Sub FindARecord(Criteria As String)
'This search will begin at the current position in the recordset
Dim rs As Recordset
Set rs = Me.RecordsetClone
With rs
If (Not rs.EOF) Then
.FindNext Criteria
If (Not .NoMatch) Then
Me.Bookmark = rs.Bookmark
Else
MsgBox "Search item cound not be found", vbInformation,
"Item Not Found"
End If
Else
MsgBox "No further records to search!", vbInformation, "No More
Records"
End If
End With
End Sub

When looking for a record that has duplicate last names I can press cmdNext
to get the next record. The source control is based on a master query. Once I

[quoted text clipped - 20 lines]

End Sub

 

[Afrosheen via AccessMonster.com]

Sorry for not getting back to you this weekend I was tied up with the Honey
Dew lists.

I don't have a problem using a filter. The thing I need to know is this is a
subform on another main subform that uses the same query as the main subform.
Can that be filtered and if so how or point me in the right direction on
writing the code for it. I'm not too knowledgeable with filters. I will also
need something to tell the user that they are at the end of the file /filter
should that be the end.

The field I'm using is the Last Name or [lname]

I really appreciate the help.

First, let me take you back to my original question: Have you considered
using a filter to retrieve only records with the desired last name? This
would seem more efficient than searching individual records. Why are you
bypassing the power of database to do step-searches?

Anyway, if you use the Docmd.Find... Methods, you must start out with the
Docmd.FindRecord and supply your criteria there. Once you've done that, you
can use the Docmd.FindNext method. FindNext takes no arguments. The problem
here is that you will need to check your position in the recordset and the
data in the particular field to determine if you found anything at all before
attempting to do a FindNext. You might as well use the code I supplied.

So let's try this again...

Private Function FindARecord(Criteria As String) as boolean
'This search will begin at the current position in the recordset
Dim rs As DAO.Recordset
Dim bReturn as Boolean

Set rs = Me.RecordsetClone
With rs
If (Not rs.EOF) Then
'FindNext will begin at the current record and search to EOF
'If you want to begin at the first record, use FindFirst. For
more info, see help.
.FindNext Criteria
If (Not .NoMatch) Then
Me.Bookmark = rs.Bookmark
bReturn=True
Else
MsgBox "Search item cound not be found", vbInformation,
"Item Not Found"
End If
Else
MsgBox "No further records to search!", vbInformation, "No More
Records"
End If
End With
Set rs=Nothing
FindARecord=bReturn
End Function

Private Sub CmdSearch_Click()
Dim sResponse as string
Dim bSearchAgain as Boolean

sResponse = InputBox(“Enter Your Criteria Hereâ€,â€Inut Requiredâ€)

if(len(sResponse)>0) then
Do
If(FindARecord(sResponse)) then
bSearchAgain = (MsgBox(“Search Again?â€,
vbQuestion+vbYesNo,â€Messageâ€)=vbYes)
End If
Loop While bSearchAgain
End If
End Sub

DevlinM, I tried your code and what was happening is that it would not find
the next record. For example I put on Tom. It found the first Tom but then it

[quoted text clipped - 36 lines]

 

[ryguy7272]

Hey Afrosheen! What is your email? I tried to send you an email and it
bounced back to me. I have something that should do what you want, or get
you pretty close...

Since the email listed under your name doesn't work, send me an email and
I'll reply back with a file that should do what you want. I can't find the
original source, so I can't post it here.

Ryan---
(e-mail address removed)

 

[Afrosheen via AccessMonster.com]

Try again. It's 06:30 on the east coast so it should be ready about 8am

Hey Afrosheen! What is your email? I tried to send you an email and it
bounced back to me. I have something that should do what you want, or get
you pretty close...

Since the email listed under your name doesn't work, send me an email and
I'll reply back with a file that should do what you want. I can't find the
original source, so I can't post it here.

Ryan---
(e-mail address removed)

An alternative to writing code may be for you to Filter rather than
Find.

[quoted text clipped - 27 lines]

.