Drop down - Auto fill

[siamsunset]

Hey everyone,

I am currently working on a qoute system and am trying make the form user friendly. The company i work is a printing firm and when we make a quote we have to select the type of paper required for our job. We have over 10 different types of paper each with about 10 different weights and dimensions. something like this

Gloss text / 85gm./ A2/ Cost 0.02
Gloss text / 120gm/A2/ Cost 0.04
Gloss text/ 85gm./A3/ Cost 0.01
Matte Text/ 85gm./A2/ Cost 0.50
Matte Text/ 120gm/A2/ Cost 1.20
Matte Text/ 120gm/A3/ Cost 1.00

Basically what would be perfect would be a set of 3 drop down menus.

So the first drop down menu the user would select the type of paper. IE "matte text" then they would go to the second drop down box and select weight (85gm.) and then in the third box, the size (A2) And then the cost of the paper would autofill in the text box. (cost 0.50)

I have mad e table of paper types , weights and costs. How would i go about doing auto fill with the drop down selections
Thanks alot everyone, your help is appreciated.
Ben

 

[SydneyGeek]

Hi Ben,

this topic comes up quite a bit on the Board. Here's one thread... http://www.mrexcel.com/board2/viewtopic.php?t=94118 That'll show how to link the combos.
This one shows how ot push the data from the last combo to a textbox...
http://www.mrexcel.com/board2/viewtopic.php?t=139321

Denis

 

[siamsunset]

Hey Dennis,

HEy mate, first of all thanks alot for your response, your a legend. Ok, i looked at the first example you gave me. I dont need a subform and i only have 1 table. I entered in the same values for the combo box props as you stated, i also made some changes to the code. The problem is when i run the form, the first combo box works fine, i then go onto the next combo box where i have to select weight, it works for the first click but if i go to change it to a different type of paper the values dont move to the next selection and will remain as the original selected value. Also the last box where size should fill in , wont give me any options as all. This is what ive done so far.


Name cmbPaper
Row Source Type Table/Query
Row Source SELECT DISTINCT Paper.Paper FROM Paper;
Column Count 1
Column Widths 3

Second Combo,
Name cmbWeight
Row Source Type Table/Query
Row Source SELECT Paper.Weight FROM Paper WHERE (((Paper.Paper)=[cmbPaper]));
Column Count 2
Column Widths 3;0

Third Combo:
Name cmbRace
Row Source Type Table/Query
Row Source SELECT DISTINCT Paper.Size FROM Paper WHERE (((Paper.weight)=[cmbWeight]) AND ((Paper.Paper)=[cmbPaper]));
Column Count 4
Column Widths 0;3;0;0

Im not sure on how the code should be laid out in order to push the answer into a text box.

Im sorry, must seem really stupid to you but im trying my best
Cheers
Ben

 

[SydneyGeek]

Hi Ben,

You're close but there's another bit of info you need - thought it was in that post but if not, here it is...
Basically, you need to tell Access to requery each combo as its upstream combo is updated. So...
In Design view, go to cmbPaper, right-click and select Properties. Click the Events tab.
Double-click the blank line next to After Update. You'll see Event Procedure. Clck the Builder (...) button at the end of the line to see the code window. In the blank line between cmbPaper_AfterUpdate and End Sub, type
[cmbWeight].Requery

Similarly for cmbWeight, you need to requery cmbCase so the code is
[cmdCode].Requery

In cmbCase, you put this into the AfterUpdate:
[txtCost]=[cmbCase].Column(3)
...for example. In combos, the first column is Column(0), the second is Column(1), and so on. Adjust the name of the text box and the column number to suit.

See how you go with that...

Denis

 

[siamsunset]

Hiya Dennis,
Ok, now the weight combo is working great, but for some reason the size combo isnt pulling in the info i need.
here is what i have.
Name cmbPaper
Row Source Type Table/Query
Row Source SELECT DISTINCT Paper.Paper FROM Paper;
Column Count 1
Column Widths 3

Second Combo,
Name cmbWeight
Row Source Type Table/Query
Row Source SELECT Paper.Weight FROM Paper WHERE (((Paper.Paper)=[cmbPaper]));
Column Count 2
Column Widths 3;0

Third Combo:
Name cmbSize
Row Source Type Table/Query
Row Source SELECT DISTINCT Paper.Size FROM Paper WHERE (((Paper.weight)=[cmbWeight]) AND ((Paper.Paper)=[cmbPaper]));
Column Count 4
Column Widths 0;3;0;0

and for the code....

Private Sub cmbPaper_AfterUpdate()
[cmbweight].Requery
End Sub

Private Sub cmbsize_AfterUpdate()
[txtcost] = [cmbsize].Column(4)
End Sub

Private Sub cmbweight_AfterUpdate()
[cmbsize].Requery
End Sub


My table has 5 columns, ID< PAPER< WEIGHT< SIZE< COST

Another question..This form will be used for quoting, so the selected info from the combo boxes and text box need to be entered into a new table called "qoutes" so i can have records of every qoute that ive sent out, is it possible to pull from other tables and put it into another....

Cheers Mate
Ben

 

[SydneyGeek]

Hi Ben, it looks like Combo 3 is a bit messed up. Change its properties to:
Third Combo:
Name cmbSize
Row Source Type Table/Query
Row Source SELECT DISTINCT Paper.Size, Paper.Cost FROM Paper WHERE (((Paper.weight)=[cmbWeight]) AND ((Paper.Paper)=[cmbPaper]));
Column Count 4
Column Widths 3;0;0;0

Inserted Paper.Cost into the SQL so that you'd have something to push.
Also changed column widths so you'd see the first column (Size)

For the code, change cmbSize_AfterUpdate to this:
Private Sub cmbsize_AfterUpdate()
[txtcost] = [cmbsize].Column(1)
End Sub

This pushes the value in the SECOND column (counting starts from 0) to txtCost

As for the quote, there's a couple of ways to go. Is every quote a one-liner, or do you place multiple items on a quote?

Denis

 

[siamsunset]

Dennis,
Your a champion mate! It works perfectly!

I am trying to migrate a qoutes system from excel to access, its an advanced system with lots of formula's but im trying to simplify it in access. Im guess one table will be fine as long as i have a feild for every entry that has to be made for the forumula's. Basically i just need to pull info from other tables and put it into the qoutes table. What would be the best way for me to do this?
Thanks mate
Ben

 

[siamsunset]

Also, do all excel type calculations have to be made in a query? or is it possible to preform the calculations on the form and fill into a feild in a table? Seriously, i have about 50 calculations that depends on one another.
Please help
Ben