Create a multi-field search box that goes to the record in a form

[Zak14]

I presume this is a popular question, but I couldn't find a decent one that works for me.

This is what the search box must do:
- Searches 3 fields (StudentID, FirstName, LastName) and jumps to the relevant record.

The closer to the search box in the navigation bar below, the better, so this is what I would really like too.
- A repeat click would take me to the next result/record (if any) with the same criteria
- Instant search as soon as I start typing

Thank you.

 

[vbaInet]

Very possible and you're looking to do quite a lot so how good is your VBA?

 

[Zak14]

My vba is very noobie, but I'm good at recognizing patterns and whatnot, so if you explain to me what happens in the code, I'll be able to understand it.

The instant search part isn't very important, but it would be pretty nice to have.

 

[vbaInet]

I can take you through the steps but it's going to be too long. So here's something close to what you're looking for:

http://allenbrowne.com/ser-62.html

 

[Zak14]

That's not close to what I'm looking for. It's a query-type search. I want something that jumps to a record in a form.

 

[vbaInet]

It's not completely irrelevant. You need to certain parts of a querThat was to give you a different twist on things and to give an idea of how a query should be built for multi-searching. You still need to build some parts of the query so

Look into the following:

1. RecordsetClone of a bound form
2. FindFirst of a recordset
3. FindNext of a recordset
4. NoMatch of a recorset
5. Bookmark of a... you guessed it... recordset

 

[Zak14]

I'm sorry, I tried to look into the stuff but just didn't understand how anything related.

Please walk me through your suggestion of making a search box just on basic terms and I'll look into the stuff I don't quite understand.

 

[vbaInet]

I'll give you some basic code.

If your form is bound to a query (not a table), you can use a recordset (this is almost like a tabular view of your query) to to manipulate data and navigate through records. I mentioned that your form should be bound to a query and not a table because the property RecorsetClone only works with queries. The RecorsetClone is a copy (or clone) of whichever query it's bound to. So let's get coding:

dim rst as dao.recordset

' Get a copy of the form's query
set rst = me.recordsetclone

' Find the first matching record
with rst
    .findfirst = "[Field1]=" & Me.Textbox1 & " AND [Field2] = '" & Me.Textbox2 & "'"

     ' Change the current record of the form if a matching record is found
     if not .nomatch then
          me.bookmark = .bookmark
     end if
end with

' Clean up
set rst = nothing

Once you get this working you can play around with the FindNext method.

 

[Zak14]

My form is bound to a table (3 in fact). How can I do it on a table-based form.

 

[vbaInet]

Create a query based on the table and bind it to your form.

 

[Zak14]

Create a query based on the table and bind it to your form.

So, I need to redraw my entire form for this?

 

[vbaInet]

A query is built on either another query or a table right? Use the query wizard, select the table, go through the wizard to the end and use that query name in your form's Record Source.