Find Record Problem

G

GOL

I have created a text box that you enter a purchase order number into and
then hit a "search" command button underneath it that searches for the
purchase order entered. This was done with the GoToControl and then
FindRecord macros. It works fine, however, sometimes two orders on the
system use the same purchase order number, so there are two completely
different orders that use the same number. When a purchase order number is
searched for as it is, it returns only the first number that matches, when
there are still others that match. How can I get the "Find Next" option that
is on the Edit...Find Menu to the easier to use form I have it as now?
 
G

Graham Mandeno

First, I suggest you don't use GoToControl and FindRecord. You can do so
much more by writing your own code.

Try adding this function to your form module:

Private Function FindPO( fNext as Boolean )
Dim sSearch as String
sSearch = "[PurchaseOrder]='" & [txtFindPO] & "'" '<<
With Me.RecordsetClone
if fNext then
.Bookmark = Me.Bookmark
.FindNext sSearch
Else
.FindFirst sSearch
End if
If .NoMatch then
MsgBox "Purchase order not found"
cmdNextPO.Visible = False
else
Me.Bookmark = .Bookmark
' see if there are any more...
.FindNext sSearch
cmdNextPO.Visible = Not .NoMatch
End If
End With
End Function

I've made the following assumptions:
Your PO field is named "PurchaseOrder"
It is a text field (if it's numeric, then remove the single quotes in
the << line)
Your search textbox is named "txtFindPO"
Your "Find next" button is named "cmdNextPO"

Now, set the OnClick property for your "Search" button to =FindPO(True), and
for your "Find next" button to =FindPO(False).

Now, when you click the search button, the code will find the first instance
of that PO number. If none exists, it will show a message and hide the
"next" button. If it finds one, it will set focus to that record and check
to see if another matching record exists, showing or hiding the "Next"
button as appropriate.

When you click the "Next" button, it does the same thing, but it starts the
search from the current record, not from the start.
 
Top