By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,398 Members | 975 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,398 IT Pros & Developers. It's quick & easy.

Auto. display of table record in form

100+
P: 135
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
Sep 13 '07 #1
Share this Question
Share on Google+
11 Replies


Scott Price
Expert 100+
P: 1,384
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
Sep 15 '07 #2

100+
P: 135
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:

Expand|Select|Wrap|Line Numbers
  1. "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
Sep 17 '07 #3

Scott Price
Expert 100+
P: 1,384
Have you tried something like this?

In the RowSource for your combo box Products:
Expand|Select|Wrap|Line Numbers
  1. 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
Sep 17 '07 #4

100+
P: 135
Have you tried something like this?

In the RowSource for your combo box Products:
Expand|Select|Wrap|Line Numbers
  1. 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
Sep 19 '07 #5

Scott Price
Expert 100+
P: 1,384
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
Sep 19 '07 #6

100+
P: 135
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.
Sep 19 '07 #7

Scott Price
Expert 100+
P: 1,384
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
Sep 19 '07 #8

100+
P: 135
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
Sep 19 '07 #9

Scott Price
Expert 100+
P: 1,384
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
Sep 19 '07 #10

100+
P: 135
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
hehehe Yeah im even more confused now hahah. I dont get what you are saying. Both boxes are bound to a table called engineering where the products the user is entering gets recorded, as well as their commodities. For the lists i just used the row source from the product catalog and commodities list.

Thanks again,
Gilberto
Sep 19 '07 #11

Scott Price
Expert 100+
P: 1,384
hehehe Yeah im even more confused now hahah. I dont get what you are saying. Both boxes are bound to a table called engineering where the products the user is entering gets recorded, as well as their commodities. For the lists i just used the row source from the product catalog and commodities list.

Thanks again,
Gilberto
Thanks for sticking this out even though we're both a little confused!

I usually don't suggest this, but could you email me a copy of your database with just a bare sample of data in it? This will help me see what you are seeing, and maybe will help me to understand better what you are talking about.

Click on my profile and click the send email button to do this.

Regards,
Scott
Sep 19 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.