Basic Form Help

[sanfly]

Hi

Im an absolute noob when it comes to Access so please bear with me

For work I want to make a database to keep track of ordering, so thus far i have made 2 tables: suppliers and products

in my form to add products, i want it to display a drop down with the supplierName in it, but store the supplierID in the products table.

I figure this should be pretty simple, and could easily do it if this were php....

Can anyone help?

 

[carash77ash]

yes you need to create a relationship between the two tables. Both tables should have a supplier id. In the supplier table the supplier id needs to be the primary key.

Hope this helps

Ash

 

[CEH]

I would think a bit more about what your trying to do. So far in my experience with Access and relational databases in general, the most important thing I have come to learn is this...... "Think ahead.... think very, very far ahead"
I would not relate your Suppliers table and products table directly. Your suppliers will supply many Products and your Products could be obtained from many Suppliers. (in most cases, I'm not sure what your items are)
But my point is your Suppliers are linked to Products through your orders. As you said your database is to keep track of orders.
So at a minimume 3 tables... Suppliers, Products, and Orders. Having SupplierID and ProductID as foreign keys in the Orders table.... Along with a few other fields.... ie..OrderID(PK),OrderDate, OrderQuantity,..... so on.....

Just a thought for you............

 

[x0reset]

CEH is right on, for more info on what he is talking about do a search for "Many-to-Many" relationships.

Going back to your original question, you are looking for two properties on your combo box: "Control Source" and "Row Source".

"Row Source" is the property that populates the list of data displayed when you click on the combo box. There is a handy query builder option buttion next to this property. Click on that button and build a query displaying supplierID and supplierName. Then close the query and change the "Column Count" property to 2, and the "Column Widths" property to 0";1". Make sure your supplierID field is in the first column of the query, and set your "Bound Column" property to 1. This will display supplierName in the combo box list, but keep supplier ID as the bound column.

The "Contol Source" property is where the combo box stores the selected item. This is where you specify a field from the Products table.

Voila; selecting a supplierName will store the supplierID in the corresponding field of your Products table.

However, I strongly recommend you take a good long look at what CEH is talking about and decide if you need to create a Many-To-Many relationship. If you do, you will need to use a subform.

 

[sanfly]

thanks guys, already got the answer from another site, more or less using what x0reset suggested.

Dont worry about my database design, im sweet with that, have done a lot of complicated web based databases so have done the planning, just need to figure out how to make access do what i want it to

Thanks again