Link to home
Start Free TrialLog in
Avatar of airforcefrank

asked on

How can I get a Form to AutoComplete?

I have a fairly good database set up with my entire inventory. The system allows my workers to look up parts, to determine whether we have them in the shop. I would like my Form to have some level of "search" ability. When my workers start typing information into either of two fields I would like the most accurate information in the other fields to Auto Populate. I'm not sure if this is possible, but it would be great if it did. As always any suggestions or advice is helpful.

P.S. I see something similar on the Northwind template Form, Product Details, but I'm not smart enough to figure how to look at the embedded Macro.

Avatar of MikeToole
Flag of United Kingdom of Great Britain and Northern Ireland image

This behaviour is built-in to Combo Boxes and is turned on by setting Auto Expand to Yes on Data Tab of the Combo's Property Settings.
Change you look-up fields to Combos if they're not already.
Generally "autocomplete" is used for filling in fields with Default values. As Mike said, a combo is often easier to deal with since you can add many hidden columns containing the data you wish to use for autofill purposes, then grab that data in the combo's AfterUpdate event and use it to fill your other controls. For example, if you have a combo with this Recordsource;

SELECT CarID, CarMake, CarColor, CarEngine FROM tCars

You could then use the AFterUpdate event of this combo to get the info in CarColor and CarEngine after the user has selected a value. To do that;

Sub MyCombo_AfterUPdate
  Me.txtCarColor = Me.MyCombo.Column(2)
  Me.txtEngineSize = Me.MyCombo.Column(3)
End Sub

Combo and listbox columns are zero based, so the first column is Column(0), second is Column(1) etc etc

You can also use DLookup for this, if desired, but it's much slower than combos. And, of course, you could always just open a recordset and retrieve information that way, but again it's slower than the combo. Which of these methods you use would depend on the level of functionality you need; if all you need to do it fill in a few Default values, then the combo or DLookup would work fine. If you need a LOT of values, or must perform calculations, then a recordset (or class module) would be the ticket.
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of airforcefrank


Thanks Jeff
>>>When I open the Nothwind sample db, I don't see a "Product Details" form?
It's in the new 2007 version of Northwind

I thought MS had replaced NorthWind.mdb with AdventureWorks.accdb.

It's good to know my old friend NorthWind is still there for me!