Live Picklist

[GoodNamesTaken]

Forgive me if this has already been covered, I couldn't find anything with the search terms I could think of.

I'm trying to create a picklist that updates based upon what a user types. I've seen this done in other databases, but have not encountered it in my limited Access experience.

My goal is to reduce duplicate name records in a database that stores applications. My thinking is that the process to add an application starts at the picklist screen. As the user is typing, the picklist filters the display to matching records. If there is a match, hitting enter on the matching record directs the user to a form for application entry tied to the selected name record. If there is no match, the user hits enter on the now blank picklist, bringing them to the new name entry screen.

Is this feasible in Access 2000?

Even If I can't get exactly that, if something similar can be achieved I would be delighted.

Any help would be greatly appreciated!

 

[MarkK]

For starters you want to take a look at the Change event of the Access.Textbox object. This event fires for every keystroke in the control. During that event you need to use the .Text property of control, as contrasted with the .Value property you would normally use, to get at the data that was just entered.
Then you need to dynamically write some SQL...
More later...

 

[GoodNamesTaken]

Awesome! So glad it's possible. Though my SQL background is very strong I lack experience in Access to put it to good use. I'm assuming there will be some VB involved, which I'm also pretty shaky at. If there are any tutorials or relevant resources to building this, I'm happy to do the lions share of the research/work here. I found a picklist tutorial from the first result of the google search "access picklist" (i would link - I've less than 10 post count...), but couldn't really put the dots together on how it's anything close to what I'm thinking.

Thanks for the help, looking forward to your reply later!

 

[GoodNamesTaken]

I saw the post on live search, but the files are not in 2000 compatible format. Hooray

I know I'm starting with the on change event. I dont quite follow you on using the .Text or .Value property though. I know it's probably pretty basic, but I dont know what element or level you're speaking to. My primary reference guide for Access is a 1200 page book, so the index has been less than helpful. Once I've got the first part, I know the SQL won't be an issue.

I'm hoping that it can be done in 2000 since no one in the live search thread seemed to be using 2000 still. Say it ain't so!

 

[pbaldy]

Some info on the differences:

http://www.mdbmakers.com/forums/showthread.php?t=11681

You might also consider a combo box, which can follow what the user is typing (ie jump to that part of the list).

 

[GoodNamesTaken]

Thanks for the link, very helpful background.

My only reason for not wanting to use a combo box is that it could only display one record, and I'd like the user to be able to see all results that are similar to what they're typing. Thanks for the suggestion! If it comes down to it, I'll certainly be keeping that in mind though.

 

[MarkK]

Sorry for the delay...
The bottom line of making this thing work is that you need to requery the recordsource for every character the user types, so you handle the change event of a text box. Then, say the user types in the letter 't', you want to produce a WHERE clause that looks like...

WHERE field1 LIKE '*t*' OR field2 LIKE '*t*' OR field3 LIKE '*t*'

Of course you will have started this operation with some SQL statement that provides you with the fields and table that you want to search. I include the 'identity' WHERE clause 'WHERE True '. That way i only have to worry about appending criteria....

WHERE True OR field1 LIKE ...
[COLOR="Green"]'or, if the user deletes everything from the textbox[/COLOR]
WHERE True
[COLOR="Green"]'which selects all records in the original SQL.[/COLOR]

So lets say we start with...

SELECT ID, field1, field2 FROM table WHERE True

Well need to know what fields to include in the where clause. You can parse the sql, hardcode a list, or whatever. This example will open a QueryDef with the given SQL, traverse the fields collection, and build a list of fields. We do this once for the lifetime of the search, and that list of fields becomes the control structure for the construction of the WHERE clause on every keystroke.

module level variables...
dim varFields as variant
dim sql as string
...
dim qdf as dao.querydef
dim fld as dao.field
dim i as integer

redim varFields(0) 

sql = "SELECT ID, field1, field2 FROM table WHERE True "
set qdf = currentdb.createquerydef("", sql)
for each fld in qdf.fields
  redim preserve varFields(i)
  varfields(i) = fld.name
next

Those are the fields we'll search. Now the Change handler...

private sub tbSearch_Change()
  dim var
  dim where as string
  for each var in varfields
    where = where & "OR " & var & " LIKE '*" & me.tbSearch.[B]Text[/B] & "*' "
  next
  me.lstResults.rowsource = SQL & where
  me.lstResults.requery
end sub

That's a fast and dirty overview of the process. That code'll be full of errors, but you see the logic, right?
1) Determine your SQL and your fields...
2) Build SQL WHERE clase or run a parameter query for every keystroke
3) Apply the results to a list.
Post again as required...
Cheers,

 

[GoodNamesTaken]

Lagbolt, thanks for the thorough response.

Something else came up at work, preventing me from putting any effort into this for the time being. I'll be sure to check back in a week or two when it dies down. Thanks again for the awesome reply, cleared it right up!

Have a good'n

 

[MarkK]

You bet. Post back as required. It's a cool problem to solve.