Does anyone know these search controls I remember from the 1990's?

[Ally1205]

Hi folks,


I am trying to locate the code for a search control I once obtained back in the days of Access95.

As best as I can remember it, it was an input box on a single-record form which searched all records in the form's query, searching on one specific field. Suppose that specific field was called "surname"... The effect was that it searched the surname field of the form's query for text strings that started with the characters you typed into the input box, as you typed... So, if you typed a solitary "A" into the search box, your form (which displays one record at a time), would show the first alphabetically sorted record whose surname field started with "A". If you then typed "b", so that your search box now contained "Ab", the aphabetically-first record whose surname field starts with "Ab" would show (assuming there were any names starting with "Ab").

So, if you wanted to search for someone called, say, Smithson, you'd start typing that name into the search box. It's likely that by the time you had typed "Smi" your form would already be displaying a "Smith". However, if there were Smiths and Smithsons in your DB, you would have to continue spelling out "Smithson" before a Smithson would appear - which would probably be as soon as you had typed "Smiths" or "Smithso".

This search device also sorted your records alphabetically, as you typed.. So, if you typed, say, Br into the search box, the record returned in your form would be the first Surname starting with Br. For argument's sake, it might be 'Brackley'. If you then hit the 'next record' button, you might see, in succession, records whose surname field contained, Bradford, Brendon, Brintz, Bronson and Bruford. I'm not sure, but it may have been configured to a search on a secondary field too, which might have been, for example, your 'first name' field, so that hitting your 'next record button a number of times might return, successively: Alan Brown, Bob Brown, Charles Brown and Dave Brown, etc.

I found this search device to be extremely efficient way of searching, from within my main form, because it always found the sought-after record, using the minimum necessary number of keystrokes, and there was never any need to try again.

Does anyone remember the search device I am talking about? It was originally posted somewhere on the internet in the 1990s. Can anyone provide a link to the coding? I'm now using Access2000. I'm not sure if the original code would work in Access200 or not, but if I can locate the code, it might be easy to adjust it to suit Access2000...

Or perhaps someone knows of another, equally efficient search device for a form.

Many thanks!
Ally1205

[ItsMe]

Have you tried creating a combobox using the wizard and adjusting the limit to list property? Another approach would be to use an unbound textbox that would display results in a listbox.

[orange]

I think you are looking for Find as you Type by Allen Browne.
see http://allenbrowne.com/appfindasutype.html

[Ally1205]

I think you are looking for Find as you Type by Allen Browne.
see http://allenbrowne.com/appfindasutype.html

Thanks... However, the one I remember was used on a form that shows only individual records (one at a time). I guess the sorting-as-you-type effect was much the same. There are clearly some similarities...

[Ally1205]

Have you tried creating a combobox using the wizard and adjusting the limit to list property? Another approach would be to use an unbound textbox that would display results in a listbox.

That sounds interesting. Can you point to an example of this is action?

[orange]

Another idea. I have a form that has a textbox named txtSearch and a listbox named lstStaff.

The rowsource of the lstStaff is

SELECT tblStaff.id, tblStaff.lastname, tblStaff.firstname, tblStaff.Num, tblStaff.unit
FROM tblStaff
WHERE (((tblStaff.lastname) Like [Forms]![frmStaff]![txtSearch] & "*"));

txtSearch has an Change event associated with it

Public Sub txtSearch_Change()
Debug.Print "In txtSearch_change " & Now

Me.lststaff.Requery   '<--------------This adjusts the form's lstStaff data 
Me.Refresh              '<--------------and then it is displayed
Debug.Print "WHERE " _
& "[Fieldname1] LIKE '*" & Forms!frmstaff!txtSearch & "*' "   'my debugging statement to show progress

Me.txtSearch.SelStart = Me.txtSearch.SelLength
End Sub

The attached jpgs show what the form looks like when opened (shows all Staff Lastnames); then when "L" is type in txtSearch(shows those starting with L); and
then when "La" is typed into txtSearch. Isolated down to one entry (Lastname found.).

Good luck.

[ItsMe]

That sounds interesting. Can you point to an example of this is action?

Post #12 in this link has an example of the list box.
https://www.accessforums.net/forms/f...lem-38239.html
If I remember correctly, it is similar to what Orange is illustrating here accept the list box only displays similar results. In other words, the RowSpource is dynamically updated by the unbound textbox. Orange provided an example of highlighting rows that match the user input criteria.

As for the combobox, I would recommend trying it out with the help of the wizard and see if it gets you where you want to be.

[Ally1205]

Hi Orange & ItsMe,
Am I right in thinking (hoping) that if you click on one of the records that comes up in the ListBox, it will then make that record appear in my one-record main form, if the above SearchBox and ListBox is located on that form? If not, then it might not be suit my needs, as I want my form to display all 50-or-so fields of each member record at once, and that can only be done with a form, which allows me toan arrange all the fields so they are all visible at once.

Post #12 in this link has an example of the list box.
https://www.accessforums.net/forms/f...lem-38239.html
If I remember correctly, it is similar to what Orange is illustrating here accept the list box only displays similar results. In other words, the RowSpource is dynamically updated by the unbound textbox. Orange provided an example of highlighting rows that match the user input criteria.

I downloaded the example and tried to reproduce it on my main form. I added the appropriately changed code to my SearchBox's 'On Change' event as follows:

but on testing it, I got this error:

My mistake is probably obvious to you! Can you tell me what I did wrong? perhaps I should have tried to emulate orange's example! His code looks a bit more straight forward, in retrospect, as it doesn't contain that "option compare database", and the green text. (Thanks to Orange for the example; I may turn to your version if I don't succeed with the above...)

[ssanfu]

"Option Compare Database" MUST be the first line in a module.

For that matter, the following two lines should be the first two lines in every module.

Option Compare Database
Option Explicit

[ItsMe]

option compare database belongs at the very top of the module.

The code that is green is commented out. You can delete the commented code. Don't remember why the green "Commented" code is there. I probably commented it out because I rewrote that code block and did not want to delete it until after testing.

The idea of showing results in a list box is for the benefit of the User. It illustrates possible options as they type. Then, they can select from the "short list" the record(s) they want to work with. You would have to handle additional events to, for instance, open another form filtered to the value of the listbox.

I didn't look to closely at Orange's example but I believe it does a similar thing. The main difference being it displays the entire listbox RowSource as the User types into the Unbound TextBox. Post if you still have questions about how this stuff works.

[orange]

As others have said, you would need a different event to open a form with a single record.

If you research DoCmd.Openform you'll see OpenArgs

see http://www.datawright.com.au/access_..._arguments.htm

[Ally1205]

As others have said, you would need a different event to open a form with a single record.

Thank you, but I don't want to open a form. I want the search device to be located on my single-record form which is already open.
Regards,
Ally1205

[Ally1205]

"Option Compare Database" MUST be the first line in a module.
For that matter, the following two lines should be the first two lines in every module.
Option Compare Database
Option Explicit

Ok - thanks. That solved that part. Now I get the following error. Perhaps this search device needs modification to work on a single-record form....

[Ally1205]

As for the combobox, I would recommend trying it out with the help of the wizard and see if it gets you where you want to be.

Thanks, but I need something that works on a single-record form. I actually have an Access95 database that has the search control I was originally trying to find, but I can't seem to convert in to Access2000. When I do, I get:

I can open one copy that I converted but it keeps throwing up an error massages saying I'm "trying to run a macro that uses the DoCmd object in visual basic to carry out an action". That was after I tried to convert it to Access2000. If I try to open the non-converted copy, I get:

And then I can't close down the DB, except by hitting "end task" in Windows Task Manager. Unfortunately, I am unable to edit or modify the DB, otherwise I would remove the sensitive data from the main table and upload the .mdb file in the hopes that someone might be able to do something with it (maybe someone with Axcess97). One thing I can tell you is that the Text Box in which you entered your search string had some code in the "On Key Press" event. This was a dynamic search that brought up the 'best-fit' record, as you typed.
Ally1205

[ItsMe]

So you need to loop through the controls in your form and find a value? That sounds like your tables are not of a normalized structure.

Is something like this what you are after?

Dim ctl as Control
For Each ctl in Me.controls
if ctl.controltype = acTextbox then
do something
end if
Next ctl