By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,519 Members | 2,412 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,519 IT Pros & Developers. It's quick & easy.

Auto-Fill data entry form

P: 78
I have a form "Add" that I use to enter data into my database. All the controls on this form are textbox controls (for data entry). How would I make these textboxes auto-fill as the user types into that text box?

For example:

From an empty database.

I enter "Steven" as a first name into a field bound to my textbox control.

On the next record, as I type "St" as a first name into my textbox. The textbox should populate with "Steven". Until I finish entering "Stu".

Basically, I want it to try to populate to the most common entry in each field that the textboxes are bound to based on previous records.

Any help or insight would be most appriciated.

Thank!
Dec 13 '11 #1

✓ answered by patjones

Yes, absolutely. There are a few things you need to be aware of in order to make it work. Namely, in the combo box's property sheet:
  1. Column Count should be set to 1.
  2. The Control Source needs to be set to the column that you are doing data entry for.
  3. The Row Source needs to be set to the table in question.
  4. Limit to List needs to be set to 'No'.
  5. Auto Expand is set to 'Yes'.
When you do this, be aware of what the required columns are. For example, if employee ID, first name, and last name are all required per the table's design, then at minimum you need to supply controls for entering these.

Share this Question
Share on Google+
6 Replies


patjones
Expert 100+
P: 931
This kind of functionality could be difficult to implement in Access with a text box, as are many other things that we see on webpages.

My question is what you'd want to see happen if there is more than one match for "St". For instance, what if Steven and Stacy are already in the table? Which one shows up? In the context of a webpage, this issue would be solved by using a drop-down that appears with the possible matches. Taking a cue from that technique, it may be possible to accomplish what you are looking for by using a combo box. If the combo box is bound to the column in question, you can set it to auto fill. I recommend taking a look at that option.

Pat
Dec 13 '11 #2

P: 78
Pat,

Thank you for reading my post.

My thought is that if there are multiple entries that may satisfy the auto-fill, the most commonly used entry will pull up first.

I understand the auto-fill with a dropdown list. However, would using a combo box still allow for new entries or would it be limited to a specific list?
Dec 13 '11 #3

patjones
Expert 100+
P: 931
Yes, absolutely. There are a few things you need to be aware of in order to make it work. Namely, in the combo box's property sheet:
  1. Column Count should be set to 1.
  2. The Control Source needs to be set to the column that you are doing data entry for.
  3. The Row Source needs to be set to the table in question.
  4. Limit to List needs to be set to 'No'.
  5. Auto Expand is set to 'Yes'.
When you do this, be aware of what the required columns are. For example, if employee ID, first name, and last name are all required per the table's design, then at minimum you need to supply controls for entering these.
Dec 13 '11 #4

P: 78
It seems to be working for the most part. A few issues:

The Combo box control is pulling up repeats. (data is alread in the database).

For example in my CmbCity combo box for city it pulls:
Expand|Select|Wrap|Line Numbers
  1. City A
  2. City A
  3. City B
  4. City B
  5. City B
I only want each city to show up once in the selection.

Originally I had this set up with several lookups. This cause a great deal of extra relationships. The structure was not managable.

*FIXED* I fixed the repetitive entries by adding this as my Row Source:

Expand|Select|Wrap|Line Numbers
  1. SELECT distinct [field] FROM [Table] ORDER BY [field]
However, I cannot select an entry to place in the combo box. It is just a dead click. Hmmm.

*Fixed* The ControlSource was incorrect.
Dec 15 '11 #5

P: 78
Thanks for your help, Pat!
Dec 15 '11 #6

patjones
Expert 100+
P: 931
It's really no problem. Please feel free to post again if you have other questions in regard to your project! (Make it a new thread if it's about something unrelated to this issue).
Dec 15 '11 #7

Post your reply

Sign in to post your reply or Sign up for a free account.