Search function

[Matt]

Hi,

I've already had some help for this from an MVP, but I can't seem to
make it work correctly.

I have a search form that I am designing. The form contains a combo
box (so it Autocompletes searches) and a command button that searches
another form for the desired value. My problem is that it always
displays the results in the form it is searching in a filtered view,
only showing those records that relate to the search. Instead, I would
like it to jump to the correct record in the full display of records.
If possible, I would also like to give it the "Find Next" function,
since it will be jumping directly to a single record (the first hit).
Does anyone have any ideas? Here is my current code:

Private Sub cmdCompanyNameSearch_Click()
Dim strWhere As String

strWhere = "CompanyName like '*" & Me.CompanyNameSearch & "*'"
DoCmd.OpenForm "Contacts", , , strWhere
End Sub


Thanks,
Matt

 

[Allen Browne]

If you want the form to have all records available, but open to the record
you chose, you will need to FindFirst in the RecordsetClone of the form.

Private Sub cmdCompanyNameSearch_Click()
Dim frm As Form
Dim rs As DAO.Recordset
Dim strWhere As String

'Open the form
DoCmd.OpenForm "Contacts"
Set frm = Forms("Contacts")
Set rs = frm.RecordsetClone

'Search for the record
strWhere = "CompanyName like '*" & Me.CompanyNameSearch & "*'"
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found"
Else
frm.Bookmark = rs.Bookmark
End If

Set rs = Nothing
Set frm = Nothing
End Sub

 

[Helen]

Try this

Dim rs As Recordset

DoCmd.OpenForm "Customers"
Set rs = Forms("Customers").Recordset.Clone
rs.FindFirst "[CustomerID] = '" & Me.CompanyNameSearch
& "'"
Forms("Customers").Bookmark = rs.Bookmark

Hope this helps,
Helen

 

[Matt]

If you want the form to have all records available, but open to the
record you chose, you will need to FindFirst in the RecordsetClone of
the form.

Private Sub cmdCompanyNameSearch_Click()
Dim frm As Form
Dim rs As DAO.Recordset
Dim strWhere As String

'Open the form
DoCmd.OpenForm "Contacts"
Set frm = Forms("Contacts")
Set rs = frm.RecordsetClone

'Search for the record
strWhere = "CompanyName like '*" & Me.CompanyNameSearch & "*'"
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found"
Else
frm.Bookmark = rs.Bookmark
End If

Set rs = Nothing
Set frm = Nothing
End Sub

Thanks! That does it exactly.
How do I cause successive clicks to bring up the next records, though?
The above code obviously sets a bookmark, but how do I use it?

 

[Matt]

Try this

Dim rs As Recordset

DoCmd.OpenForm "Customers"
Set rs = Forms("Customers").Recordset.Clone
rs.FindFirst "[CustomerID] = '" & Me.CompanyNameSearch
& "'"
Forms("Customers").Bookmark = rs.Bookmark

Hope this helps,
Helen

-----Original Message-----
Hi,

I've already had some help for this from an MVP, but I can't seem to
make it work correctly.

I have a search form that I am designing. The form contains a combo
box (so it Autocompletes searches) and a command button that searches
another form for the desired value. My problem is that it always
displays the results in the form it is searching in a filtered view,
only showing those records that relate to the search. Instead, I would
like it to jump to the correct record in the full display of records.
If possible, I would also like to give it the "Find Next" function,
since it will be jumping directly to a single record (the first hit).
Does anyone have any ideas? Here is my current code:

Private Sub cmdCompanyNameSearch_Click()
Dim strWhere As String

strWhere = "CompanyName like '*" & Me.CompanyNameSearch & "*'"
DoCmd.OpenForm "Contacts", , , strWhere
End Sub


Thanks,
Matt
.

Thanks Helen. I went with Allen's solution because my combo box does
not include the ID numbers of the records, and his code does not
require I use it.

Regards,
Matt

 

[Allen Browne]

Successive clicks should work, unless the Contacts form has been edited in
such as way that its record cannot be saved, so it can't move to the desired
record.

The bookmark is a temporary one only. To find a record in a bound form, you
search in the RecordsetClone: if you don't find it you have not lost your
place in the form; if you do find it, you set the form's Bookmark to the
found record, and you're there.

 

[Matt]

Successive clicks should work, unless the Contacts form has been
edited in such as way that its record cannot be saved, so it can't
move to the desired record.

The bookmark is a temporary one only. To find a record in a bound
form, you search in the RecordsetClone: if you don't find it you have
not lost your place in the form; if you do find it, you set the
form's Bookmark to the found record, and you're there.

Hmm...
I currently have the search form opening in a modal pop-up from the
Contacts form. I cannot find any setting in the Contacts form that
would not allow it to be saved (we make data entry to this form all the
time, so it is set up to accept pretty much everything), and it is even
set up to allow Design changes in all modes. When I search 'John' the
first record with John comes up, but successive clicks cause it to hit
the first record every time, even though there are records after it
with 'John' in them. Is there anything else I can check? Perhaps my
design is causing the problem.


Thanks,
Matt

 

[Matt]

Replace "FindFirst" in the code with "FindNext" if that's what you
want to achieve.

Personally I would set the Filter of the target form so that only
John's are currently available.

Perfect! Thanks so much!
I understand that filters are normally more useful in cases like this,
but my boss is not very computer savvy, and using filtering is just one
more button he has to click. Plus, he's already used to the Access
built-in search function, so the closer it behaves to that the better.

Regards,
Matt