Solved: Price Query from Macro in form?

Hi,
There are two parts to this question, But I'm in China at the moment and can only post at an internet cafe, so please excuse the single thread for both items.

1. Search from macro:
I've wrtten a database (my very second one) when an excel program outgrew it's abilities.
I've subdivided various aspects into different tables, e.g. "Panelorder" (load of superfluous info, but also the main one), "2d sizes" (height x width, also tells if it's a custom job!), "material" (just the type of material) and "Paneltype" (material, width, height and price).

Most of it is now working, except I need to take the information from text boxes for height, width (both computed in a macro as it's not as straight forward as it should be) the material (combi box, pull down menu defined from the price table) all on a form, and search the "Paneltype" table for the appropriate price.

Since the Pricing can be done on material and width alone (all individual distinct prices in about 10 increments), this really only needs two search criteria, material and width to give a price.

e.g.
brass
aluminium
glass
wood

and

(under)
500mm
600mm
..
..
1500mm
or Custom.

I've got the rest of it working, I just need the price search to work.

I can't see how to do this as a native feature of access or as a macro.

I currently have a nearly working version which uses a combi box, and a search criteria as follows:

Row Source:
SELECT PanelType.Price, PanelType.Width, PanelType. Panel FROM PanelType WHERE (((PanelType.Width)
=forms.PanelOrderForm.leftpanelbasis) And ((PanelType.Panel)=forms.PanelOrderForm.Pmat));

This provides a combi box with a pull down menu with only one option: the correct price. But the correct price isnt automatically displayed or selected.

Can I take the "Row Source" data and use it directly in a macro?
In other parts of the controlling macro I use the following method:
[vba]
me.leftpanelbasis = 900
[/vba]

2. Duplicate data:
There is duplicate data in this method: e.g. sizes are repeated in the "2d sizes" and the "PanelType", and materials are repeated in the "material" and the "paneltype" tables, but since pricing depends on sizes and material, and both need to be used in pull down menus without duplicate entries, I think this might be the only way.

"Paneltype" looks a little like this:

ID panel width height price
1 brass 500 750 ?0
2 brass 700 1000 ?2
3 brass 800 1200 ?4
4 brass 900 1350 ?6
5 brass 1000 1450 ?8
6 brass custom custom ?0
7 aluminium 500 750 ?2
8 aluminium 700 1000 ?3
9 aluminium 800 1200 ?4
10 aluminium 900 1350 ?5
11 aluminium 1000 1450 ?6
12 aluminium custom custom ?9
13 glass 500 750 ?0
14 glass 700 1000 ?2
15 glass 800 1200 ?4
16 glass 900 1350 ?6
17 glass 1000 1450 ?8
18 glass custom custom ?0
19 wood 500 750 ?5
20 etc


and material looks like this:

ID material
1 brass
2 aluminium
3 glass
4 wood
5 d.u.
6 etc.

"2dsizes" is again similar.

If I were to use the "paneltype" table for the two pull down menus for the sizes and the materials would there be multiple options the same, e.g.:

Row Source:

SELECT PanelType.ID, PanelType.Panel FROM PanelType ORDER BY [Panel];

This gives multiple options for "brass", Multiple options for "Glass" etc.

Is there a better way to do this than my way using duplicate data entries, but without giving multiple identical options?

Thanks for your help.


-Andy.

"bump"

hi,

If anyone can heklp or poit out a KB item, I'd be much obliged.

-Andy

To determine your price based on the material and width, you mey be able to use a DLookup function to get the price from your price table. Save the value of your dLookup to a variable, and then feed the variable's value into a textbox to show the price.


Have you tried using:
[VBA]SELECT DISTINCT PanelType.ID, PanelType.Panel FROM PanelType ORDER BY [Panel];[/VBA]

I'll give that a go, thanks for your input.

-Andy.

I'm having problems with using DLookup on two variables, is there a recommended way to "nest" the DLookup, or is it possible to DLookup on two variables at the same time?

Thanks,

-Andy.

Please post your Dlookup code.

The WHERE Criteria for Dlookup can handle multiple expressions- you write them basically the same way as you would for SQL (just without the WHERE). Also, you have to wrap the evaluated criteria with the correct symbols- single quotes for string, pound signs for dates, nothing for numbers.

Example using form object value for criteria:
[vba]DLookup("EmployeeName", "tblEmployees", "EmployeeID = '" & Me.EmployeeID & "' AND HireDate = #" & Me.HireDate & "#"[/vba]

I was using:

[vba]

Dim IDmat As Integer, IDwidth As Integer
IDwidth = DLookup("ID", "PanelSize", "SearchWidth= '" & Me.LeftPanelBasis & "'")
IDmat = DLookup("ID", "PanelType", "Panel= '" & Me.pmat & "'")
Me.LeftPanelPrice = DLookup("PRICE", "PanelType", "ID= " & IDwidth + IDmat - 1)
[/vba]

But this had flaws: if I were to add another basis type of panel size, I'd have to re-number the unique ID of all the entries.

Now I'm using:

[vba]

Me.LeftPanelPrice = DLookup("PRICE", "PanelType", "Panel= '" & Me.pmat & "' And Width= '" & Me.LeftPanelBasis & "'")

[/vba]

It's a bit neater. Hopefully more robust.

Thanks for the help with the syntax. Not sure what the "#" was for, but it didn't like it.

Was it Numerical conversion from text string?

-Andy.

Quote:

---

Thanks for the help with the syntax. Not sure what the "#" was for, but it didn't like it.

Was it Numerical conversion from text string?

---

You have to wrap the evaluated criteria with the correct symbols- single quotes for string, pound signs for dates, nothing for numbers.

I see. My little mod seems to work ok, shall I leave it be?

And thanks for the "Select Distinct" Tip, it's working a treat and I've got rid of two unneccessary tables with duplicate data.

Thanks,

-Andy.

Quote:

---

Originally Posted by andysuth

I see. My little mod seems to work ok, shall I leave it be?

Thanks,

-Andy.

---

You're new DLookup function looks good to me- it's correct for using multiple WHERE criteria.

As long as it's working for you, then I'd say you're fine.