More than two combo boxes in a form

[aian]

Hello to everyone!

I have a serious issue to solve and I would like to ask for your help, as I just don't seem to know how and where to start from.

I need to make a database that will have 2 tables, say tbl_item and tbl_offer. The items table will have different cardboard dimensions and other characteristics (width, height, color, photo, price etc) while the tbl_offer will have different combinations of clients' cardboard requests.

The workflow is:

A client comes and asks for:

- two pieces of cardboard of 120x100 cm
- five pieces of cardboard of 135x90 cm
- one piece of cardboard of 110x125 cm

I want to be able to enter the client's request in a form and the form will do 2 things:

1. Store the clients request as a record in the table tbl_offer (for current or future referrence)
2. Create a report (out of the above record) that will be printed out and given to the client (but I suppose that's easy...)

Now tbl_item has different types of cardboard (their code names) along with different dimensions and price for each, for example:

Type|Width|Height|Price
----------------------------
001 | 120 | 100 | 10
001 | 135 | 190 | 12
001 | 110 | 125 | 11
001 | 110 | 100 | 16
001 | 150 | 100 | 12
002 | 165 | 170 | 17
002 | 140 | 105 | 10
002 | 140 | 130 | 18
002 | 170 | 130 | 18


The problems are

1. I don't know exactly how I can create a form (say frm_offer) that will give me the possibility to add many different cardboard types and save them as one offer to the tbl_offer. What I would like is to choose from a combobox (or any other similar function) the type (say 002) and by this selection a second combobox would be filled only with the available Width dimensions for this type (165, 140, 170). By choosing what I want from the width combobox, a third combobox would be available that would give me the available height dimensions for this width (if for example I chose Type 002 and Width 140, the last combobox would give me two choices, 105 and 130). After choosing the Height I want, I would fill in a text box of how many pieces the client wants and the form would also fill automatically the cost of all the pieces for that dimension (by taking the price/item from the tbl_item perhaps?). If 1 piece then it should say i.e. 18 for the 140x130 piece, if 10 pieces it should say 180. Finally, I want to add the photo of the cardboard automatically (pls note that same type of cardboards share the same photo) not at the form but at the report to be printed.

I have already looked in the Access FAQs section of the forum for the combobox techniques but I can't understand how I can apply them to three (or more) comboboxes plus the photo.

I can use Access only when little/no programming is required, and I am very much afraid that all the above will require a great deal of it, so I apologise in advance for my non-knowledge of programming

2. How do you think would be suitable to split all this data to tables? Is my thinking correct (2 tables, tbl_item and tbl_offer) or would you suggest something better? Will the tbl_offer get a lot of data that way? Mind you also that every once a year there is the need of updating the cardboards' prices (all will rise/fall at the same percentage) and, furthermore, we are talking for about approx. 100 different types of cardboards, each with about 15 different dimension combinations.

I am not sure if I had to post this message to the Tables section of the forum or here but I think here is better as the main problem is the form part.


Thank you all in advance and my apologies once again for the low level of my programming skills


Alexander

 

[supa_sumo]

Well by my reconing the simple thing is to combine the Width and Height Fields and make one combo box unless you need them seprate for a reason, now that would be the easy option.

This might be something that will intrest you though

Its a bit more complicated but worth the effort
http://www.access-programmers.co.uk/forums/showthread.php?t=63766

 

[aian]

supa_sumo: Thank you for your prompt reply. I checked the link you gave me and it's very close to what would fit my case. My only problem is that it will be difficult to include so many windows in one form as there are many cardboards and each one has several dimension combinations, while I need to be able to combine in one form many combinations of the same/different type of cardboard.

Any other ideas?

Thanks again!

 

[supa_sumo]

my point is that in that example the combo boxed are updated as you enter the value into the combo boxes on the on change there in a procedure which updates the values in the other combo boxes.

 

[Pat Hartman]

that will give me the possibility to add many different cardboard types and save them as one offer to the tbl_offer

You have many products related to the same order so you need an additional table. Take a look at Northwinds or any other order entry sample. The basic tables are:

customer, Order, OrderDetails, Products.

OrderDetails are the items that are ordered at one time. The Products are what was ordered. The customer is who is paying for the order.

 

[aian]

Thank you both very much for your reply. Pat, I will try and figure out what you said about Northwind database and will revert soon. I suppose I don't waste your time in vain.

Thanks again.