How to auto-complete a data entry.

[gottnoskill]

I've tried to look on how to find a way to make this work, but I just keep finding solutions on auto-fill/auto-populate depending on what preset value is picked.



What I'm trying to accomplish is add a function that depending on when a key is pressed a result will pop up. Narrow down the result with each keystroke until you find the desired value so you do not have to type in the same value all the way through every time for a new entry. Now when no value is found in previous entries I would like to allow the user to add that unique value to the table. I'm trying to create this function for visitor names so that if i would like to search for a specific visitor I will find all entries to that visitor rather than miss one because the user creating the entry did not type in the name correctly.

The way I have my form setup is that I have a header/footer and in the middle a sub-form table that shows results based on what is searched. The footer has a search function and an add function. You may add entries via the add function or directly place them on a new record in the sub-form table.

Combo-Boxes seem to have the characteristics described above I'm looking for to make this work, but I cannot find a way for a combo-box to allow a new entry on a unique value if whatever is typed is not already in the combo-box.

Ideally I would like a regular text boxes to have these characteristics, but I have a feeling no SQL or changes to the properties will allow for this to happen. Please let me know if this is actually possible though!

The closest I have come to make this sort of work is using a combo-box and bounding it to the [VisitorName] Column in my table, but this does not allow for unique entries.

Let me know if you need more information! Thank you for any help!

[ItsMe]

I believe in order to add to a combo you need the combo's row source to be a Value List. This way, the combo has control in order to edit the value list vs. appending a table. I am not sure though because I always disable "Limit to List" in the combo properties.

I often use a double click event to open a second modal form to a new record that allows the user to add a record. Below is an example of a list box that uses the On Change event to modify its RowSource. It is the only other thing I can think of at the moment that may offer you an option.

[Missinglinq]

...I believe in order to add to a combo you need the combo's row source to be a Value List...

You can do that regardless of whether the Combobox Row Source Type is set to Value List or Table/Query. For the latter, you'll need a Form based on the Table or Query where you can add a New Record to the underlying Table. Just enter the Form's name in the List Items Edit Form Property and you're set. Right-Click on the Combobox and the Form will come up. Once the New Record is entered and the Form closed, the new data will appear in the Combobox.

Linq ;0)>

[ItsMe]

Never used that property. Good to know.

[Missinglinq]

Beats having to roll-your-own Not In List procedure! I've been doing this for well over a decade, and there's always something new to learn! It's what makes Access fun!

Linq ;0)>

[gottnoskill]

Thanks for the feedback. I definitely believe I can make something work with what you guys have discussed so far.

I still don't believe that these will solve my issue, but now I'm thinking maybe I can make a workaround, take a combo-box or list box and attach it to the [VisitorName] in my table. With each Key press on a textbox I could add SQL that would refresh and narrow down the results in the listbox/combobox (similar to ItsMe's file) until the desired value is found. When the desired value is found the user can click on that value and send it to the text box you are typing in. After that you go on with the rest of the entry and press then execute the add function.

I'll let you guys know what I come up with tomorrow at work. I'm optimistic that I can make this work now in some way. If I can make it work I'll try and create a separate file to place in here of what I come up with.

[ItsMe]

...When the desired value is found the user can click on that value and send it to the text box you are typing in...

Whatever the result from the list box is you should consider using a key value from the selected row as a value to store in another record. Be careful so the user does not have an opportunity to adjust the value after a selection is made and the application determines to use it.

[gottnoskill]

Alright so I have another question then. How would I grab a value selected in a list box and place it in a text box? Every time I try and retrieve a selection it seems that the value retrieved is null. So how do I grab a single selection from a list box?

[ItsMe]

The listbox's RowSource determines how many columns are retrieved. I will include at least two field names in my SELECT statement for the RowSource; one field for the ID/PK and another field for the User's benefit. There is a column count property for the list box. If you select 2 or more fields and set the column count to = 1 only the first field within the SELECT statement will be part of the properties.

You can bind one of those columns from the SELECT statement using the Bound Column property. The first field listed within the SELECT statement will be column 1 when using the property sheet and the Bound Column property. You can retrieve the value from the bound column like this
Me.ListBoxName.Value

You can adjust the Column Widths to display or hide a column like,
Column Width = 0", 1"
This will control what the user sees