Search in Access

[nico5038]

The idea is to use the right-click pop-up to do the filtering. The right-click and putting the ID# in the "Filter for" field will do the same as your input-form triggered by the query.
But also a search for the Surname and/or court can be achieved.

Entering the ID# manually will always hold the error of typo's, using the data sheet will allow some control.

Check out the Right-click Instruction !

Getting the idea ?

Nic;o)

[riaane]

Ahh ok gotcha.

Problem is that the use of this is on a random query of the population to see if there are any matches on the recordset in the person's ID Number. Therefore a typed in search criteria will always have to be used as the operator is tasked with finding out of the random person in front of them has any records as listed in the WOA Table.

So the operator can right-click and select "Text Filters - Equals...", but how do you then return to the full record set data sheet view after your search?

I will try and build a button that will return the form to fully populated view.

[nico5038]

After right-clicking in the Surname and entering e.g. "*hans*" (without quotes but with the *'s) Access will fill the data-sheet with all rows holding "hans" anywhere in the Surname.

Just try yourself.

Nic;o)

[nico5038]

You cold also right-click the ID# and use your sample value "7608245057081" .
You'll see three rows that hold this value...

Nic;o)

[riaane]

Nico, we are talking past each other here :-)

I get the results you point out. However, the searches are repeated many times per hour, each on different ID#'s so the operator must be able to return to the full record set to enable him/her to do the next search on the full record set, not only on the previous match list.

I can click on the "Filtered / Unfiltered" button on the bottom taskbar but I would like to have a "Return Full Record Set" button below the "Show Warrant Scan" button for ease of use.

Sorry for the confusion. I will try and dream up a button that does this.

[nico5038]

Hmm, something like this attachment perhaps ?

I've build in the past a solution where the filter is stored in a table, so allowing the user to re-use them...

Nic;o)

[riaane]

Nico, you are BRILLIANT! Thanks so much for your help and patience. Your beer is in the mail ;-)

[nico5038]

Glad I could help.

Some tips:
. There's some VBA code "behind the forms", perhaps you'll now be able to understand it. It's better to use VBA instead of the macro's you were using.
. Try not to use spaces in table and field names.
. Use (like I did with the table and forms) prefixes like tbl / qry and frm to identify the database objects.

Success with your application!

Nic;o)
PS I hope it's a "Trappist" that's being mailed <LOL>

[riaane]

Nico, 1 last question: The operators that will use this system are complete technology Philistines and many of them can literally barely drive a mouse. Please can you tell me how I can replace the Right’click-Search process with a button that pops up a little form allowing you to enter the ID Number and the “Search” to execute the query. The result should still be displayed in the data sheet view.

I'm a little lost without my trusty old Macro's.....
PS: My 1st installment is attached. I selected one of the Belgium versions as the ones in your native Netherlands may be too well-known to you ;-)

[nico5038]

You've selected one of my favorites. There's only one Dutch Trappist and some 6 Belgian.

I've added a solution that's using a combo box to select the (distinct) ID's.
A combo box has an auto-append feature, thus typing an existing code will show the "final" part, but when a typo is made the trailing part will be empty...
Just try it yourself. I prefer this way as it's supporting the user.

Nic;o)

[riaane]

"You have done well, young Skywalker. Your training is complete" ;-)