Actively look up existing records while typing entry into form

[sephiroth2906]

There is a very good chance this is far more complicated that I am hoping, but I often overcomplicate things, so I thought I would ask after putzing around for a while.

I have a table in which the name of an item is the primary key, since it will always be unique, and I don't want duplicate records. I am updating the number of the items, so right now what I do is search for the name of the item to see if it already exists (with Ctrl F in the form) and, if so, I update the number. If not, I make a new entry.

I am trying to figure out if there is a way to have the form actively look at the table to see if the name of the item (primary key) exists and bring up that record if so to eliminate the search. Otherwise, it just lets me happily type away. Kind of a lookup with the source being the field in the table itself instead of a separate source table.



I appreciate any guidance you may be able to provide. Thanks!

[vicsaccess]

sounds like you are describing a combo box based on the table ID.

[ssanfu]

I agree with vicsaccess. I believe it is the "ComboBox.AutoExpand Property" in the Data tab of the combo box properties sheet.

I wouldn't have the PK field a text type field. I would use an autonumber and set an unique index on the "name of an item" field.

Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

[sephiroth2906]

Geez, that was way easier than I expected. I am glad I asked.

I wouldn't have the PK field a text type field. I would use an autonumber and set an unique index on the "name of an item" field.

I am curious, why is this a problem? I suspect there is a good reason or you would not have mentioned it.

Thanks!

[ssanfu]

I am curious, why is this a problem? I suspect there is a good reason or you would not have mentioned it.

Did you read the link? Read the link again... it has lots of good info (I still refer to the link - it is one of my special reference bookmarks)
Pay attention to "Use a Meaningless Primary Key"

Good luck with your project...

[sephiroth2906]

Sorry. Missed the link. Thought it was part of your sig. I will get right on that.

Thanks!

[sephiroth2906]

That makes a lot of sense. I am sure that is proper database management 101. That is what I get for fumbling my way through computer programming. Unfortunately for me, I am awful at learning anything computer related unless I am actively solving a problem. I have tried structured learning and it never sticks.

Thanks again!

[sephiroth2906]

I have begun implementing the changes you recommended here. If I do not create a unique index, the form will pull up all of the information associated with the record. If I do index it, it will prefill the name but will not pull up information associated with the records for changes. In either case, it has the auto-expand property enabled.

Any ideas what I am doing wrong?

[ssanfu]

Hard to tell - Don't know which changes you have made. Is the auto-expand working?

[sephiroth2906]

Sorry for the delay in this post.

I made no changes to the form. The only change made to the table was to index it. The auto-expand is not functioning either, but is still turned on.

Thanks again for all your help!