What's the best way to add a record to a normalized database utilizing combo boxes?

[tjp]

I have four tables with unique records as shown below. I'd like to add a record for a new item to the database by selecting the values from combo boxes for Supplier, Item and Unit. If the values do not exist the user would then type in a new value. SuppliersItemCode and Cost would always be new values. What's the best way to go about this? I am unsure how to add a record to a normalized database where you sometimes have to use / reference existing unique values in multiple tables via foreign keys for the new record.

The logic of the form would be:

1. Select existing or add new Supplier.
2. Select existing or add new Item.
3. Select existing or add new Unit.
4. Enter new SuppliersItemCode
5. Enter Cost

Suppliers
---------
SupplierID (primary key)
Supplier (indexed unique)

Items
------
ItemID (primary)
Item (indexed unique)

Units
-----
UnitID (primary key)
Unit (indexed unique

SuppliersCostsAndCodes
-----------------------
SuppliersItemCostCode (primary key)
Cost
ItemID (foreign key)
UnitID (foreign key)
SupplierID (foreign key)

Any advice or assistance is greatly appreciated, thank you.

 

[FoFa]

I don't know if there really is a "right" answer. However what I typically like to to do, if the the data supplied needs to be more than a single item in a combobox, is to put a little button (usually with a plus sign) next to the combo box for ADD NEW ITEM, that will popup a form to add all the information required for the new value. Then when closed requery the combobox (to refresh it) and go on.

 

[tjp]

It will be a single item per combo box or enter new value. I thought the main benefit to combo boxes was that you could select OR add a new value without referencing another form. What I'm having problems getting my head round is adding a record that references multiple tables. Sometimes it will add a new value and sometimes it will reference an existing unique value by foreign keys. To use an example following the form logic in the first post:

Adding a new record could:

Use an existing unique Supplier in Suppliers, add a new unique Item to Items, use an existing unique Unit from Units, add a new unique SuppliersItemCode to SuppliersItemCodesAndCosts and add a non-unique value to Cost in SuppliersItemCodesAndCosts.

So the new record would consist of new records in some tables and foreign key references to existing unique records in some tables. If I was using one table this would be easy for me but I implemented the correct normalized structure in the database to ensure uniqueness and data integrity (and to do it properly for once instead of botching it ).

 

[FoFa]

Well if the CB value is suppose to reference in a table, and that reference contains additional information, to add a new value also means to supply that additional information. If they are allowed to enter a supplier (ABC Supply) but no information about them, what good is that information? Hence I use a POPUP to capture the information. Now there are other ways of this doing this depending on the business rules. Here only accounting can put in a customer (because of credit limits things like that), so we can'[t sell something to a customer until they are entered. Sales processing will call Accounting and have them put in a customer so they can enter a sales order. They are not allowed to sell to a customer not in the system. So our business rules preclude entery into a combobox. Short cuts are not always the best approach, and that is why I said there is not always a "right" answer.

 

[tjp]

I understand but I don't think i've explained myself well. I am adding a whole new record. I am populating every field in every table. If the supplier for the item I'm adding does not exist in the Suppliers table then I'm adding a new supplier to that table. If the supplier for the item I am adding does exist in the Suppliers table I am selecting it for the new item record and the new record will reference the Supplier through a foreign key lookup. In this way I keep the data uniform. I want to avoid having, for example, Jewson Ltd and Jewson, so that's why the user builds the whole new record from combo boxes using existing unique data where possible.

This other post is exactly what I want to do. http://www.access-programmers.co.uk/forums/showthread.php?t=78759. I will study that and see how I get on. I did spend a lot of time searching for another post similar. Surprised I missed it. Thanks very much for taking the time.

 

[FoFa]

Well then the CB should use the foreign key as the bound column, so when that record is stored, the proper key is stored along with it. These are typically 2 column (or more) CBs and you usually do not show the bound field (foreign key). Is that what you are looking for? But that precludes entering a value into the CB because you can't enter a foreign key typically (it is usually a autonumber in these cases).