Base data on Multiple categories

  • Thread starter Thread starter Zurghew
  • Start date Start date
Z

Zurghew

Hi all,

Please help me here. I need to have a form which will allow me to select a
particular type of service through a combo box and based on that value
choose another subcategory and based on that subcategory choose another
subcategory and then according to the item selected store the rates in the
table transaction.

Graphically, (pls excuse if not clear)

combo1--choose-->CategoryA...based on catA choose from combo2------>
SubCategoryA---based on SubCategoryA choose from combo3---->
SubSubCategoryA......based on this combination of all the three multile
categories fill up the rates field .

I have no idea how to acheive this any tips will be of great help.
 
Zurghew,

For the first part of your requirement, you need cascading combos. To do
that, see the following link:
http://www.pacificdb.com.au/MVP/Code/ComboRS.htm

For the second part, "fill up the rates field", I imagine you mean to
retrieve data from a table, based on the combination of the values selected
in all 3 combos. To do that, all you need is the right query.
SELECT * FROM tblRates
WHERE CategoryA = " & Me!cboCategoryA & " AND " & _
CategoryB = " & Me!cboCategoryB & " AND " & _
CategoryC = " & Me!cboCategoryC

The above assumes that all 3 category values are numeric. For the ones that
are textual, use the following syntax:
CategoryX = """ & Me!cboCategoryX & """ AND " & _

When you say you want to "fill up the rates field", where is the rates
field? Is it a textbox or listbox on a form, or is it one or more columns in
a table? Your answer will determine the exact method of retrieving the data.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Are you familiar with referring to a control on a form in the criterion row
of a query? If you have a form named MyForm and with control named
ControlOne, a query can use the value in ControlOne as a criterion this way:

SELECT * FROM MyTable WHERE FieldOne = Forms!MyForm!ControlOne

or something similar.

You would need a series of combo boxes in which the Row Source for each one
referred to the value in the previous one as above. The SQL statement above,
for example, might be the Row Source for a combo box named ControlTwo. In
order to get ControlTwo to pay attention to what you've selected in
ControlOne, you need to requery the control after you've updated the value
in ControlOne, so you would put the following in the AfterUpdate event
procedure for ControlOne

Me!ControlTwo.Requery

And then so on down the line until you've dealt with all the choices you
want.
 
Back
Top