Search as I type

[Cindy]

My table has a compound primary key consisting of Trip_ID and Patient_ID. How can I make my data entry form "search as I type" in this key?

The form also has a combo box and many tab controls. I really appreciate any help on this one.

 

[vbaInet]

Here's a search form you can adapt:
http://allenbrowne.com/ser-62.html

 

[Cindy]

Many thanks for this vbaInet

 

[CJ_London]

I think you may want something slightly different since the allen browne does not search as you type, you have to click a filter button.

Try putting this code in your searchbox change event. I'm assuming you have a subform (I'll call it sfmResult) in either datasheet or continuous form view

if searchbox.text="" then
    sfmResult.form.filter=""
else
    sfmResult.form.filter="[Trip_ID] & '|' & [Patient_ID] Like '*" & searchbox.text & "*'"
end if
sfmResult.form.filteron=true

assuming your ID's are numeric and you type in 1 this will return all records with a 1 in either the trip_ID or the Patient_ID. When you type the next character (say 2) it will return all records with 12 in the trip or patientid

the pipe (|) is required so you do not return anything that, use the above example has a trip_id ending in 1 and a patient_id starting with 2

 

[Cindy]

Thanks CJ London.
have a great weekend ALL!!

 

[CJ_London]

Second with large data sets the *search as you type* technique can get bogged down especially over networks

Agreed - I have a technique for minimising the effect by putting a delay of a few milliseconds after each change before updating the filter. This allows the user to type several characters before the filter is striggered.

 

[Cindy]

thanks AccessBlaster & CJ London!!

excuse my limited knowledge but just to be clear on this statement:

"By contrast pressing a button to see your search results is lighting fast especially when you set an index or two"

you mean having a button beside the searchbox, right?

 

[CJ_London]

you mean having a button beside the searchbox, right?

correct.

If you want to go down my route, I can dig out the code I referred to in my post so that the filtering is not triggered until you stop typing.

 

[Cindy]

the challenge I'm having on that is, each record in the database is uniquely identified by two fields, not one. The search will require both the TripID and PatientID to pull up the record...

 

[Cindy]

CJ London, since these two data have to be provided for the record to be identified, I think placing a search button beside them is the way to go.

The only trouble I anticipate is my current table validation rule: If the TripID and PatientID fields are not null, then [Condition] is not null. It is currently driving me nuts. Once I type in both IDs it doesn't let me play around with the form or table. I guess I'll cross that bridge when I get to it.

TripID and PatientID are already indexed to allow for duplicates. Each ID can occur more than once but each combination of both IDs is unique. Could you help or advise on the code for this button please. I appreciate your help

 

[CJ_London]

the challenge I'm having on that is, each record in the database is uniquely identified by two fields

As answered in my post #4

sfmResult.form.filter="[Trip_ID] & '|' & [Patient_ID] Like '*" & searchbox.text & "*'"

 

[vbaInet]

CJ London, since these two data have to be provided for the record to be identified, I think placing a search button beside them is the way to go.

Lets say you have 30,000 records in your database and you type a "T" in the search box, search as you go starts looking at all the T's in the 30,000 records. On the other hand, If you type a string like "F640112536" in a text box and press a search button it looks for that entire string at once.

If you don't have too many records to deal with then yes the "search as you type" functionality is a great idea. If you do however have lots of records (like AccessBlaster explained), is it really worth having the DB do all that work? Perhaps at the moment you don't have many records but bear in mind that your db will grow, so it's up to you if you implement the "search as you type" functionality now that you don't have that many records and in the future revise your strategy.

I prefer to give total control to the user. If a user enters text (no matter how short or long) they can hit the Return key which to activate the search. In addition to this there will be a Search button beside the search box too.

 

[CJ_London]

TripID and PatientID are already indexed to allow for duplicates. Each ID can occur more than once but each combination of both IDs is unique

my suggestion would do the following:

If your data is

TripID PatientID
11 12
11 22
11 33
12 13
12 41

my suggestion would produce the following results from which you can then select the required record

enter 1
TripID PatientID
11 12
11 22
11 33
12 13
12 41

followed by 2 (ie. you have now entered 12)

TripID PatientID
11 12
12 11
12 41

If you only want a unique combination of TripID and PatientID then you would remove the pipe from the filter. The code would still produce the same results as above to this point but if you then entered 4 (so you now have 124) the original code will return nothing whereas the amended code would return

TripID PatientID
12 41

 

[Cindy]

thanks vbaInet and everyone. I'm taking every advise in and trying to process them.

CJ London, please, please bare with me. I'm still wondering what the user'd type into the searchbox... when two fields are required?? If they've already typed in, in both fields then clicking the button would be a step closer to getting the results. Sorry but just wondering, are you saying that after both fields have been filled, then they'd still type in the search box as well? Please advise.

 

[vbaInet]

If you only want a unique combination of TripID and PatientID then you would remove the pipe from the filter.

A better non-visible and non type-able character would be vbNullChar or Chr(0) instead of pipe.

Besides, once you combine these two fields you will lose the benefit of their indexes. You can still achieve the same result by searching both fields individually using the OR predicate. That way indexing is still applied.

 

[CJ_London]

are you saying that after both fields have been filled

Not sure if this was sent after my last post, but the user only fills in one field in the form, and the filter limits the records displayed to those where whatever the user has typed can be found in either TripID or PatientID

 

[Cindy]

only just seen your illustration CJ London with the figures. Thank you!! I think one reason why I was confused there was that, I'd created my form as a single form not as a continuous form. I could not visual what you were saying there for a sec. So in essence, until all the search criterion is fully provided, the list of possibilities appear in the continuous form. Got it!

Hey vbaInet, I appreciate your point. However if the user (a physician) does a search, it will be for reference purposes and I don't anticipate it happening frequently. They will mostly be entering data. Other users will not use the form but Queries to analyze the database.

It is getting clearer to me now

 

[CJ_London]

@vbaInet

Besides, once you combine these two fields you will lose the benefit of their indexes. You can still achieve the same result by searching both fields individually using the OR predicate. That way indexing is still applied.

Good point

@Cindy - you would modify your filter to

sfmResult.form.filter="[Trip_ID] Like '*" & searchbox.text & "*' OR " & [Patient_ID] Like '*" & searchbox.text & "*'"

 

[vbaInet]

Hey vbaInet, I appreciate your point. However if the user (a physician) does a search, it will be for reference purposes and I don't anticipate it happening frequently. They will mostly be entering data. Other users will not use the form but Queries to analyze the database.

I'm not sure which of my points you're referring to but if you're talking about post #15 then like I said, it's up to you how you choose to implement it. For me, entering "abc" and hitting the Return (or Enter) key on the keyboard is more viable and easy on your db. I'm sure CJ has some tricks to show you anyway.

Most applications you see that have the "search as you type" functionality are hooked up to more powerful db servers (which Access isn't) or they've implemented some sort of complexity algorithm (like Google does).

 

[Cindy]

hey CJ London, I am not using subforms on my form but a tab control with pages. I later tried to add a subform but it was covering other controls on the tab page so I removed it.

my form is called ObstetricsForm and the tab control Tabct1147. I have removed the pipe from the below code but not sure how to tweak the rest...?

if searchbox.text="" then
sfmResult.form.filter=""
else
sfmResult.form.filter="[Trip_ID] & [Patient_ID] Like '*" & searchbox.text & "*'"
end if
sfmResult.form.filteron=true