Search and Add should probably be two separate functions, because mixing them together may not be feasible.  Your search term may only be a partial piece of text: long, longsword, longswords, longswordman, etc.  If you search for "long", hoping to find any items that contain the word "long," and if nothing comes up, you may not need the option to add "long" into your table, because "long" is not something you want to add.  Search terms can be "fuzzy", but data that are to be added to your tables need to be precise and exact.  In other words, search and add seem two incompatible actions.  Also, the user may not want to be interrupted with the option to add when he only wants to search.

First of all, love the magic items reference. I was a huge D&D player in my youth.

I've got to agree with Kevin. I think search and add should be two separate processes. But I do see where you're going with this. It's kinda like you have a combo box filled with customers. If you type in "Jean" you want it to jump right to "Jean-Luc" and if it doesn't then you know you can add it.

I'd suggest a search combo box in the form's header, start typing it in, and if it doesn't come up as one of the options (i.e. the combo box will go blank) then you could use an Event of some kind to add it. Maybe an "On Not In List" event? See the extended cut for Relational Combo for ideas.

That's how I'd do it.

Hi Richard, typing on a combo box to find a match would only work if what you type matches the start of the matching item.  To use your example, if you type "Luc", then "Jean-Luc" won't show up as a matching item.  Only typing "Jean" will match "Jean-Luc".  And it has to be an exact match too: typing "Jean Luc" also won't find a match.  This is another reason why search and add don't mix.  A good search feature needs to allow "fuzzy" search entries.  But data entry can only allow exact data.

That is true. I was just going on his example. If he's entering a "Longsword +3" and he starts typing in "Long..." then he'll see which Longswords are already in the table.

You could also use a Multi-Field Find with wildcards. So many options.

Thanks both Richard and Kevin for responses. Kevin, I understand what you're saying, but in this particular case (item detail entry) Richard is correct-- I have found my Longsword +2, +4 vs dragons, and will now type in the letters from the start-- "L-o-n-g..." to narrow down my search. Later, when I'm playing a second playthrough, I'll want your (Kevin's) fuzzy search-- "what was that sword vs dragons?" but that will be a different form.

What I've got so far, before reading these responses, is a split form listing the search as you type. I start in a text box named SearchBox and typing will narrow down the split form above. Once I see what's filtering, I can a) delete what I've entered if it's already there, or b) tab to the PasteBtn I added, hit Enter, and my focus shifts back to a new record for entry. Not elegant. Will definitely be looking at those combo box possibilities.

I'll post the code I have at the moment, using the blank template FirstName field.

Interesting. Let me toss some ideas around in my head and see what I can come up with. This might make an interesting video once I'm done with the Account Balances series.

I have this about half-way. Below is a picture of my split view form with some test data. The header text box is just the search box you taught me how to make. The button is the clear button you created, but with one more line of code:
       DoCmd.GoToRecord , , acNewRec
By its default behavior, if I
1) start in the text box (can set the focus to it in On Load)
2) type some letters to narrow down the list
3) see that the Longsword +2, +4 v Undead is NOT there
4) I can just Tab > Spacebar and I'm on a new record, in the Item field, ready to enter a new Item!

I'm thinking of a second button (2 Tabs away) that would take me to the last remaining record showing in the split view, so I can edit it. I have to figure out that code, plus I'd likely have to solve the spaces problem (I know, buy your seminar!).

Didn't take long to figure the second button out. So now, if I'm looking up an item to edit it, I narrow the search to one item, then Tab, Tab, Spacebar to end up with the cursor at the end of the Item field entry. The code on the second button is:

  Item.SetFocus
  Item.SelStart = Len(Item)

Image below is after I type "sh" in search box, then Tab, Tab, Spacebar.

Looks good.