I have being trying to develop a quotation formula, so that it helps me when I make quotations for my customers.

This is my problem. I create the tables and all of that, my problem is in my FORM. My quotations goes like this:

The price per sqf is determine by 3 variables:
client type: wholesale or retail
thickness of glass: 10 diferent thickness
glass type: clear glass, tempered glass or mirror

the combination of these variables determine the price per sqf.

Then this price is multiplied by the total sqf of the glass.

And that's it for that.

My problem is that I haven't being able to make three seperate drop-down lists for each variable and that the price per sqf (the result from the combination) appears also in the form and automatically updates when a variable is changed.

After that I can take it.

What I am able to do: one drop down list whith all the diferent combinations about 30, but that is not practical.

Please help

Why cant you make the three combo boxes?

You can make the three comboboxes with the separate choices with no problem. Hence Lee's good question.

I think the problem here is how does EACH of those choices affect the price per square foot? Do you have a list of how EACH of those 15 choices affects the price? You'd need three tables, each with three of the fields you mentioned. Does the price per sq ft work by multiplying three factors to get the overall price? If so, then the final price is just a multiplication of three numbers.

You'd have three comboboxes, each with two columns. The first column is the user's choice, the second is the field that affects price. Then the final price is simply a textbox that is equal to the multiplication of the second column of each combobox.

If the price is not a simple multiplication of three numbers, then it's more complicated. How do you determine the final price when you do it by hand? Can you give us an example?


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

hey guys thanks for helping I never thought I was going to receive any help.

This is how it is done:

The combination of the variables determine what price to use. In order words, there are 30 posible combinations so there are 30 diferent prices. What the list boxes would do is to determine what price to use.

I have 4 tables: customer type, glass thickness, glass type, and prices. All of the above except prices, is the list of its kind, for example, the customer type has 2 entries: wholesale and retail, the glass thickness 10 entries: 1/8, 5/32,... and so on...

The price table has 30 entries, 1 for each posible combination. It has 5 columns: ID, customer type, glass thickness, glass type, price per sqf...

I am new at access so I dunno other way to structure the tables. If there is a better way please let me know.

What the 3 list boxes should do is to look the price per sqf in the price table (it sounds easy but I dunno how to).

Please help.

Given your last table structure, you can just put a button on your form with a label, say "Calculate!". Clicking it would do this

Dim strCriteria as String

strCriteria = "[customer type] = '" & Me.cboCustType & "'"

strCriteria = strCriteria & " And [glass thickness] = '" & Me.cboGlassThick & "'"

strCriteria = strCriteria & " And [glass type] = '" & Me.cboGlassType & "'"

Me.txtPrice = DLookUp("[price per sqf]", "Name of Price Table Here", strCriteria)

The button to get the price will not enable until you enter all three data. So in each combobox's AfterUpdate event AND in the form's On Current event, you can put

If IsNull(Me.cboCustType) Or IsNull(Me.cboGlassThick) Or IsNull(Me.cboGlassType) Then
    Me.cmdGetPrice.Enabled = False
Else
    Me.cmdGetPrice.Enabled = True
End If

Note that I'm making up the names of your comboboxes, buttons, etc. You need to replace what I wrote with the REAL names of your controls. I'm also assuming that all three comboboxes hold text, not numbers.

Good luck and welcome to P2P!




Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

thanks greg

but because of my little experience I dunno where to paste the strings

anyways i named the combo boxes like this:
cboCustType
cboGlassThick
cboGlassType

the related tables are (in order top to bottom):
PriceLeveltbl (price level = cust type)
Thicknesstbl
Typetbl (type = glass type)

each combo box looks for the entries in its related table.

What I understand is to paste this code:

-------------------------------------------------------------------
strCriteria = "[PriceLeveltbl] = '" & Me.cboCustType & "'"

strCriteria = strCriteria & " And [Thicknesstbl] = '" & Me.cboGlassThick & "'"

strCriteria = strCriteria & " And [Typetbl] = '" & Me.cboGlassType & "'"

Me.txtPrice = DLookUp("[price per sqf???]", "Name of Price Table Here", strCriteria)
------------------------------------------------------------------

but I have this doubts:
1) in the [price per sqf] i have to put the [Pricetbl] (price table) or [Price] (the column named price in the price table)
2) I have to paste this in a comand of the Get Price button, but in which event.

This part:
-------------------------------------------------------------
The button to get the price will not enable until you enter all three data. So in each combobox's AfterUpdate event AND in the form's On Current event, you can put

If IsNull(Me.cboCustType) Or IsNull(Me.cboGlassThick) Or IsNull(Me.cboGlassType) Then
    Me.cmdGetPrice.Enabled = False
Else
    Me.cmdGetPrice.Enabled = True
End If
---------------------------------------------------------------
When I paste the code in VBA on AfterUpdate on each combo box it will show me the same VBA for all the boxes. In order words only one screen of code for all the combo boxes. So I only need to paste it into one combo box or write it three times.

Also I don't find in the properties the OnCurrent Event.

Do you mine seeing my file so that you might understand me a little.

Thanks for your help.

The DLookUp function looks something like this

DLookUp("[Field you want to get]", "Name of Table to Look In", "[Another Field in Table] = '" & YourCriteria Here & "'")

So in your case

DLookUp("[A]", "B", strCriteria)

Replace A with the name of the field that holds price per square foot.
Replace B with the name of that fourth table that relates everything to price.
strCriteria is that expression you evaluated correctly from the comboboxes.

The AfterUpdate code should go in the AfterUpdate event in EACH of the three boxes (i.e. three times), not just once. The On Current event is that for the FORM itself, not the comboboxes.

I suggest you reverse your table naming convention. Continue to put the describing factor up front like you did in the comboboxes and how you should do for everything. That is, PriceLeveltbl, Thicknesstbl, and Typetbl should be tblPriceLevel, tblThickness, and tblType respectively.

This comes in VERY handy because in Access VBA when you type, the VBA editor's autofill kicks in often, especially when you type the periods. So things will try to autofill in alphabetical order. You'll get all your cbos together, all your txts together, all your tbls together, etc. in alphabetical order.

Also, when you choose recordsource or sourceobjects for controls, the dropdowns will have all tables grouped together alphabetically if they all start with "tbl" and all your queries will be together if you start them with "qry", etc.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Greg,

Thanks. I just got all code setup and it has no errors. The button enables when all the 3 combos are selected.

But now the problem is that the Text Box is not updating or looking up the price because nothing happens when I click calculate.

Can you please help me on that.

What exactly does your button's ON CLICK event say?

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

greg, this is what the calculate button does

---------------------------------------------------------------
Private Sub cmdGetPrice_Click()

Dim strCriteria As String

strCriteria = "[PriceLevel] = '" & Me.cboPriceLevel & "'"

strCriteria = strCriteria & " And [Thickness] = '" & Me.cboThickness & "'"

strCriteria = strCriteria & " And [Type] = '" & Me.cbotype & "'"


Me.txtPrice = DLookup("[Price]", "tblPrices", strCriteria)


End Sub