andysuth
09-27-2008, 02:09 AM
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:
me.leftpanelbasis = 900
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.
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:
me.leftpanelbasis = 900
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.