Synchronized Combo Boxes - Maintaining combo box values in a datasheet form

[kassondrajane]

I have an access database to manage inventory we receive. We receive many similar products from different suppliers. I have a form set up database style to receive the different products including the categories of "Supplier" and "Product". I currently have the ROWSOURCE of the Product to be limited to suppliers that match the supplier selected on the form. It works just fine. However, as I move to the next record and put in a new supplier, the previous record's product goes blank (obviously be I changed the supplier value). Is there a way fix this?

[zmbd]

kassondrajane
You really haven't provided us with enough detail of your database design to really help.

I can make some guesses based on past experience and similar issues from other members.

First thing to do:
Check that the control size is actually large enough to show the value of the field(s)

The next easiest, shut down everything and COLDBOOT the PC. If this is merely a display issue this will often fix things.

However,
Usually this has to do with how your CONTROL SOURCE and ROW SOURCE properties are setup - these two, although related, are vastly different in their usage.

I think first you should read thru:
> Database Normalization and Table Structures.

DO you have more than one table?
If not, you're most likely not normalized and a normalized database will make setting up the combo/list-box a breeze!

So, how I would do this in my DB, a quick outline follows:

1) In all of my tables, I use the autonumber datatype as my primary key (PK) within the table. It is usually best practice to never use a PK that has any meaning. I also do not rely on the autonumber to give me sequential (serial numbers or check numbers) results - I have a function for that.

tbl_suppliers
This is the table that has nothing except the supplier specific information... think address book.
I even go so far in my database to split out the supplier contact from this table as the sales rep will be promoted, transferred etc... I do not put the products here.

tbl_products
This table has the product details, think of it as an address book for the product. So name, part/catalog number, etc...
it also has a foreign key (FK) that relates the product to the supplier
([tbl_suppliers]![PK] - 1 : M - [tbl_products]![FK_Suppliers])

And so forth... as I mentioned pulling my supplier contact out into their own table... you can do the same with your products table, say colours, fragrance, fabrics... the list is endless. The idea is that it takes less space to store numbers than text and searching on a number is faster.

So now for the inventory.
IF you are tracking serial numbered items then you have an entry for each actual asset (6 items, 6 records) because you need to track the serial numbers Otherwise this can be a very simple table that simply has the product, and the on hand count:
tbl_inventory
([tbl_products]![PK] - 1 : M - [tbl_inventory]![FK_Products])
you might also keep the pricing information here, the products table, or indeed a separate table just depends on your particular needs.

tbl_invoice
depending on your design, this would be related to either the inventory table (due to serial numbers) or to the product table and ideally a customer table (might be able to recycle the suppliers table to handle this (^_^) )
This would be where you would track your actual sales of what products and to whom they were sold. Because this would be a history table, certain calculated values might be stored here.


Now, lets go back to the tbl_products:
In the form for this table, the row source for a combo/list-box on the form that would be used to record which supplier would be set to the tbl_suppliers with the bound column set to the [tbl_suppliers]![PK] and the control source set to the [tbl_products]![FK_Suppliers])

Similar idea with tbl_invoice and other tables with relationships.