Populating form controls

[tfurnivall]

OK - I'm past the problem from earlier. If I change the underlying table design I can now make the fields visible and available to the form. Here's the next issue (and I suspect it'll be a doozy).

I'm using several fields as look-up paths into the database. Among these are Publisher, Label, LabelNumber. What I want to be able to do (and I've done it in Excel with a much smaller dataset) is to begin typing in a field (say, PublisherName), and have each keystroke present a refined list of possibilities from which I may select (or which may represent a new Publisher). (Think Google's search interface, for example)

So - I'm using a combo box, and trying to populate that combo box using a select statement on each keystroke (via the OnChange event).

I modelled the SQL to do this using a 'bound' combo box control and a fixed query to load it, but obviously this won't do for the situation I describe. However, when I extend the SQL with a WHERE clause, all sorts of nasty things happen:

Access puts up a totally spurious textbox where I can put in my search value; NO I want to use my combo box textfield.
Access complains that I have the wrong datatype (I get as far as "c" of "Columbia") rather than a Long (representing the underlying PublisherID - an autonumber unique key value.

The whole point of unbinding the control is to avoid getting locked in to the sequential navigation model which really seems to be all that Access supports.

Core of the question, then, is:

How can I use a changing value in the text field of a combo box to load the list part of the combo box with a set of matching values based on a Select..Where statement.

Thanks in advance!

Tony

 

[John Big Booty]

You may be able to adapt the code demonstrated in this sample

 

[Isskint]

This is an old 'friend' on the forum. There are several posts and examples throughout the forum. At random i suggest http://www.access-programmers.co.uk/forums/showthread.php?t=188663 which has a couple of examples.

 

[tfurnivall]

Thanks to Isskinnt and John Big Booty (love those user names!) for the pointer to the solution using the ReQuery method. This led to a runtime error 2118, which seems to want to save a record before doing a requery. Of course, because I'm simply looking at values there is no record to save. Nor do I want to add countless partial values to a centrally important dataset.

Here's the code:

'  Form frmMedia has a combo-box cmbPublisher, and a hidden field txtSearchValue
cmbPublisher_Change
txtsearchvalue.text=me.cmbPublisher.text
me.cmbPublisher.Requery

The Query for the cmbPublisher RowSource is:

SELECT PublisherName, PublisherID from Publishers
WHERE PublisherName like "*"+me.txtsearchvalue.text+"*";

(Code reduced to make manual transfer to an internet capable PC possible - Hurricane Sandy has had some weird after-effects)

Now - I can't simply 'empty out' the cmbTextBox area, because then I'd re-enter the event handler and get into a tight loop!

Help - this is getting to be no fun - fast!

Tony