Table realtionships and RI...

[cheuschober]

Hi!

So, I have a table for expenditure logging ([Expense Logs]) and on that table is a Payee field. The payee is (currently) only a single-name field like SUBWAY, or WACHOVIA. It has been suggested to me that I place my PAYEE listings into a separate table on the instance that I might want or need to expand that information beyond simply the name.

I am reasonably confused, however, as to how I should go about defining this. The reason being that, in my dream world, I would like users of the data entry form to be capable of typing in a payee name (or part of a name) and have a commonly recognized payee's correct name inserted. As this could easily grow to include 300 or more distinctive payees a numeric code identifier would be difficult to memorize.

Moreso, as there will constantly be new payee's added, I would like to be able to use the same form to add a new payee if a currently existing record is not in place in my Payee table but with RI enforced it's not capable of doing that and conversely without RI mispelled entries and the like aren't weeded out.

In otherwords--I think I just want Access to read my mind which is a little unfair to it! Does anyone have an idea on a setup to achieve this goal?

Gratefully yours,
~Chad

 

[RoyVidar]

Don't be concerned, Access is a great mind reader (or at least those who programmed it)

Create another table with a numeric identifier (atuonumber?), relate it to a foreign key field in this table thru the relationship window.

Then on the form where you let your users perform dataentry, create a combobox thru the combobox wizard. Use the option "...look up the value in a table or query", select the new table, use the wizard suggested option of hiding the primary key, and voila - you have a combo where you start typing the payee...

When that's working as it should, have a look at the NotInList event. I think you should find several posts about that here thru search

 

[The_Doc_Man]

You'll have to look at the properties of the combo box on the form, though. I think there is a property to allow it to auto-fill when it does the lookup that Roy suggested. Set that to TRUE (YES).

I use this on my home-grown credit-card tracking DB to identify my most common card usages. Turns out to be restaurants. So putting in the first few letters of the service provider, in my case, looks up the entry in my "common service provider" table. Fills in what I want. Sweet & quiet.

As to whether you want the system to allow you to enter something not in the list or TRAP when you go somewhere not in the list, that is an added wrinkle. There are arguments both ways on this topic. Just sort of depends on how ambitious you are.