Auto Fill Textbox From Past Records

[Reese]

[Solved] Auto Fill Textbox From Past Records

I have a form for creating new entries in a table. In it there is a textbox for a City field that I would like to have some kind of auto-complete based on past records in the database--similar to how Excel provides auto-complete options within a spreadsheet based on entries in previous cells.

In other words if "Binghamton" has been entered in a past record and the user starts typing "Bing" in the textbox, then "Binghamton" will become an autofill option. Is there a way to set this up?

Thanks!

 

[Cronk]

Replace the text box with a combo control whose row source is set to a query listing all the cities in your table. Use a totals query so there is only one instance of each city.

 

[Reese]

But wouldn't that mean that I could no longer enter in a new city using the same control? From my experience with combos they only allow entries that are currently present in their row source.

My goal is to have the text box (or combo or what ever control it ends up being) to function like an spreadsheet cell or a internet search bar. As you type it has suggested auto complete options, which change as you continue to type. But, if it's something entirely new, it can still be entered and saved in the database.

This isn't a must-have feature, but it would be nice.

 

[Reese]

I tried a combo control & query and I was right. It works great if I only wanted to enter in cities that are already in the database but it won't let me enter cities that aren't currently already there.

Are there any other ways to accomplish this? It seems like it should be easy to do; Excel has it so why doesn't Access?

 

[Cronk]

Yes, you are correct. My mistake in overlooking not being able to add to a totals query.

Instead, create a tblCities with City a unique field. Base the combo row source on this table but bind the control to your main data table field.

Set the combo property Limit to List to No.

A better way but a bit more work is to use the NotInList event for the combo to add the new city to tblCities.

 

[Reese]

I think I understand what you mean, but how will the tblCities know what cities are in the main table?

 

[Cronk]

Put all Cities currently in the main table into tblCities and for every new city use the NotInList event to add the new city to tblCities.

 

[spikepl]

A new table is not necessary. Use SELECT DISTINCT when drawing city names from your main table as row source for the combo. Set Limit to list to No. Requery the combo in the AfterUpdate event of the combo. This is it.

 

[Cronk]

Will the requery work before the new or edited record is saved?

If it did work, mistakenly spelt entries would be saved.

 

[Reese]

Cronk, wouldn't misspelled cities be saved either way? Like anytime a feature like this is used, it's up to the user to make sure that they spell it correctly--I've had this same problem in spreadsheets, online searches and forms--anywhere.

Spikepl, I tried it your way and it works great--except that when I use the Requery macro in the AfterUpdate, and then tab to the next field the form moves to a new record. I removed the macro event and it works fine.

Was there a particular reason for the Requery that I'm not aware of?

 

[spikepl]

The idea of the requery was to make the newly added city available when you type the next line. Actually, the new typed city does not exist in the table until the record is saved, so the requery should be done on the form's after update event.

 

[Cronk]

The event procedure could open a form which requires the user to validate the entry, rather than just adding a typo.

 

[Reese]

...the requery should be done on the form's after update event.

Thanks, that worked great.

The event procedure could open a form which requires the user to validate the entry, rather than just adding a typo.

I understand why you're worried about typos, but that's just adding a step that would become tedious and annoying.

Plus, when entering the data, the user will usually be on the phone with the client and we always review the information at the end with them to double check. And since the row source is based on a query (I decided not to use the tblCities idea), after the current record is corrected it won't be an issue for future entries, right?

Thanks, both of you, for your help!