Auto. display of table record in form

[Gilberto]

Hello

I have a table PRODUCT NAMES with fields PRODUCTNAME and COMMODITY, another table called COMMODITY NAMES with the only field COMMODITIES (containing a list of 7 different commodities), and a form called ENGINEERING with TWO separate combo boxes (PRODUCTNAME and COMMODITY) where the user has to select a product from the already filled in PRODUCT NAMES table which are displayed as "options".

I need the COMMODITY combo box in the form to automatically display the value of its related PRODUCTNAME in the PRODUCT NAMES table. And if that specific product doesnt have an already specified one, that the user could select the commodity from the combo box list (which "row source" is the COMMODITIES field on the COMMODITY NAMES table).

How can i do this? or

Is it possible or is it better to delete the COMMODITY NAMES table out of the question so that initially EVERY product has to have their corresponding commodity filled in?

Thanks,
Gilberto

[Scott Price]

What is the nature of the relationship between Commodities and Products?

You are looking for One To One, One To Many or Many to Many... Ask yourself questions like can one commodity have more than one product? Can one product have more than one commodity?

If one product will only have one commodity and one commodity will only be associated with one product then you have a one to one relationship and it will be all right to combine the two tables.

If one product can have more than one commodity, or one commodity can be related to more than one product (which I suspect is the case) you have a one to many and therefore need the second table.

If one product can be related to more than one commodity AND one commodity can be related to more than one product you have a many to many relationship and therefore need a third, linking, table.

Now then, on your commodities table, normal practice is to place a second field named CommodityID and use this field as the primary key, giving it an autonumber value. In your combo box you reference this in the row source like this: SELECT CommodityID, Commodity From Commodities.

Create a query that pulls the values you want to show on the form, then bind your various controls to update the fields being passed by the query.

Any questions, don't hesitate to ask!

Regards,
Scott

[Gilberto]

What is the nature of the relationship between Commodities and Products?

You are looking for One To One, One To Many or Many to Many... Ask yourself questions like can one commodity have more than one product? Can one product have more than one commodity?

If one product will only have one commodity and one commodity will only be associated with one product then you have a one to one relationship and it will be all right to combine the two tables.

If one product can have more than one commodity, or one commodity can be related to more than one product (which I suspect is the case) you have a one to many and therefore need the second table.

If one product can be related to more than one commodity AND one commodity can be related to more than one product you have a many to many relationship and therefore need a third, linking, table.

Now then, on your commodities table, normal practice is to place a second field named CommodityID and use this field as the primary key, giving it an autonumber value. In your combo box you reference this in the row source like this: SELECT CommodityID, Commodity From Commodities.

Create a query that pulls the values you want to show on the form, then bind your various controls to update the fields being passed by the query.

Any questions, don't hesitate to ask!

Regards,
Scott

Scott, thank you for your reply.

As you supposed 1 commodity can be asigned to more than one product but a product can have ONLY one commodity. I already have the two tables related (COMMODITIES and ENGINEERING) and on my COMMODITY combo box (in the ENGINEERING FORM) i already have:

"SELECT [Commodity Name].CommodityId, [Commodity Name].Commodity FROM [Commodity Name] ORDER BY [Commodity];"

which works very good displaying the list of the 11 different types of commodities.

I just created a one to many relationship between tables COMMODITIES and PRODUCTS (linking COMMODITY ID field from both tables, which im not sure its correct....). By now, SOME products (in the PRODUCTS table) ALREADY have the commodity specified under the field name (COMMODITY on the PRODUCTS table) and i want THIS already specified commodity (from COMMODITY in table PRODUCTS) to be automatically displayed in the combobox.

To make it a bit clear, i have:

PRODUCTS ("catalog" table) with fields:
ProductNameID
CommodityID
ProductName
Commodity

COMMODITIES (table) with:
CommodityID
Commodity

ENGINEERING (table AND FORM) whith:
ProductName
Commodity

In this last table the user selects from the combo box PRODUCTS (which is linked with a whole catalog of product names from the table PRODUCTS) a product and from the combo box COMMODITY, its commodity.

Thanks,
Gil

[Scott Price]

Have you tried something like this?

In the RowSource for your combo box Products:

SELECT ProductID, Product FROM tblProducts WHERE tblProducts.CommodityID = Me!cboCommodity

You'll have to change the names I used here to reflect the names you are using for the respective objects.

Regards,
Scott

[Gilberto]

Have you tried something like this?

In the RowSource for your combo box Products:

SELECT ProductID, Product FROM tblProducts WHERE tblProducts.CommodityID = Me!cboCommodity

You'll have to change the names I used here to reflect the names you are using for the respective objects.

Regards,
Scott

Thanks for the reply Scott. I just dont understand why i should use this with the PRODUCTS combo box instead of the COMMODITY one, which is what i want to automatically display ONCE you select the product (as each product already belong to a commodity in the products table). I tried to adapt what you gave me with no luck, at the same time i cant see how this (your code) could ALSO allow you to select from the different commodity options from the combo box, in case the product didnt have one specified already.

Thanks again,
Gilberto

[Scott Price]

The row source code i gave you was assuming that you want to be able to select from the Commodities combo box a commodity, say Windows 2000 Computer Systems, and have the Products combo box filter to only those products that match the chosen commodity.

Obviously I didn't understand what you are trying to accomplish. Leaving aside the technical aspect for the moment, just tell me in plain language what you want this combo box setup to do.

Regards,
Scott

[Gilberto]

The row source code i gave you was assuming that you want to be able to select from the Commodities combo box a commodity, say Windows 2000 Computer Systems, and have the Products combo box filter to only those products that match the chosen commodity.

Obviously I didn't understand what you are trying to accomplish. Leaving aside the technical aspect for the moment, just tell me in plain language what you want this combo box setup to do.

Regards,
Scott

OK...

Actually i think you are right and this could be another approach i could take.

However in plain english i want this second combo box to DISPLAY the selected product current "pre-difined" commodity or to let the user select from the 11 different commodity possibilities. Thats all.

Thanks again for your time and effort.

[Scott Price]

OK...

Actually i think you are right and this could be another approach i could take.

However in plain english i want this second combo box to DISPLAY the selected product current "pre-difined" commodity or to let the user select from the 11 different commodity possibilities. Thats all.

Thanks again for your time and effort.

Ok, so given one combo box that chooses commodities, you want the second combo box to display products that relate to that chosen commodity... The row source i gave you should do that as is. If you are just looking to display, try changing the products combo box to a list box (right click on combo box in design view, choose Change To>ListBox, you can change it back this way also if you don't like it :-) It takes up a bit more form real estate, but will give a better view of the related products.

Now the second part after "or" is where I'm not understanding yet :-) Are you wanting to be able to select a product in the combo box, and within the same combo box be able to change it's related commodity? If this is the case, I don't think that's possible. That would be something like a multifunction combo, which I'm sure Access (at least 2003 and earlier) doesn't provide.

To accomplish this, you will need to display the Product details in something like a continuous form or subform on your main form. In this view, each product will be listed with it's attributes (Price, Commodity, ManufactureDate , ExpiryDate, etc.) In the corresponding controls you will be able to change whatever information you wish to change about the selected product.

Regards,
Scott

[Gilberto]

Ok, so given one combo box that chooses commodities, you want the second combo box to display products that relate to that chosen commodity... The row source i gave you should do that as is. If you are just looking to display, try changing the products combo box to a list box (right click on combo box in design view, choose Change To>ListBox, you can change it back this way also if you don't like it :-) It takes up a bit more form real estate, but will give a better view of the related products.

Now the second part after "or" is where I'm not understanding yet :-) Are you wanting to be able to select a product in the combo box, and within the same combo box be able to change it's related commodity? If this is the case, I don't think that's possible. That would be something like a multifunction combo, which I'm sure Access (at least 2003 and earlier) doesn't provide.

To accomplish this, you will need to display the Product details in something like a continuous form or subform on your main form. In this view, each product will be listed with it's attributes (Price, Commodity, ManufactureDate , ExpiryDate, etc.) In the corresponding controls you will be able to change whatever information you wish to change about the selected product.

Regards,
Scott

Sorry Scott i think im still not being very clear.

The form has TWO combo boxes, one where the user selects the product (from a catalog) and another where the user "classifies " that selected product with one commodity. All the selected and classified products go to a separate table than from the catalog.

so, for example:

The user clicks on the PRODUCTS combo and a list of product names appear (from the catalog table), he selects one product.
Then on another combo he clicks to see the list of commodities and selects the commodity that corresponds to that selected product.

PRODUCT ---driver head rest cushion---- COMMODITY ---Head Rest---

So far it works ok, however SOME of this products, have already a commodity record on the catalog table. So having to select the commodity is just double work.

So for THIS products that have already a specified commodity i want the commodity box to display it, otherwise the user just can select it from the list.

So it CANT work the way you are suggesting (As in FIRST selecting the commodity and THEN selecting each product)

Hope its better now.
Gil

[Scott Price]

Sorry Scott i think im still not being very clear.

The form has TWO combo boxes, one where the user selects the product (from a catalog) and another where the user "classifies " that selected product with one commodity. All the selected and classified products go to a separate table than from the catalog.

so, for example:

The user clicks on the PRODUCTS combo and a list of product names appear (from the catalog table), he selects one product.
Then on another combo he clicks to see the list of commodities and selects the commodity that corresponds to that selected product.

PRODUCT ---driver head rest cushion---- COMMODITY ---Head Rest---

So far it works ok, however SOME of this products, have already a commodity record on the catalog table. So having to select the commodity is just double work.

So for THIS products that have already a specified commodity i want the commodity box to display it, otherwise the user just can select it from the list.

So it CANT work the way you are suggesting (As in FIRST selecting the commodity and THEN selecting each product)

Hope its better now.
Gil

The light is beginning to dawn :-) So how does leaving the Product combo unbound, and then binding the Commodity combo to it's field in the Product(Catalog ) table work?

Regards,
Scott