3 linked combo boxes in a form

[Opal]

I need some assistance with a form in Access. I have a combo box
based on a Shop table.
It is followed by a combo box based on an Area table. The areas are
unique to each shop. I have created an SQL query that makes it so
when a certain shop is selected in the first (Shop) combo box, only
the areas unique to that shop are selectable from the Area combo box.
I have also added a macro to requery the Shop combo box to update the
Area selectable items. Now here is my problem. I have a third combo
box for Equipment that is based on an equipment table listing over 900
pieces of equipment specific to a shop and an area within the shop.
How can I make it so the Equipment combo box only gives me the
specific items for each Shop and Area selected? Will this require
some specific VB coding beyond a simple macro? I am new to VB but I
am learning. Thank you for your help!

 

[Vantastic]

I'll make assumptions here as to the name of your combo boxes

cmbShop, cmbArea, cmbEquipment

You're probably after a heirarchy kinda thing... so equipment is not
available across the board, ie, equipment number 1 is only found in area 1,
in shop 1... right?

You need to have the following fields in the tables:

Shop: ShopName

Area: AreaName, ShopName

Equipment: EquipmentName, AreaName

Your combo boxes should have the criteria set so that "Area = cmbArea" in each

You could use a macro but VBA is better.

Use it in the beforeupdate event

Private Sub cmbShop_BeforeUpdate()
cmbArea.Requery
End Sub

Private Sub cmbArea_Beforeupdate()
cmbEquipment.Requery
End Sub

Hope this helps

 

[Opal]

I'll make assumptions here as to the name of your combo boxes

cmbShop, cmbArea, cmbEquipment

You're probably after a heirarchy kinda thing... so equipment is not
available across the board, ie, equipment number 1 is only found in area 1,
in shop 1... right?

You need to have the following fields in the tables:

Shop: ShopName

Area: AreaName, ShopName

Equipment: EquipmentName, AreaName

Your combo boxes should have the criteria set so that "Area = cmbArea" in each

You could use a macro but VBA is better.

Use it in the beforeupdate event

Private Sub cmbShop_BeforeUpdate()
cmbArea.Requery
End Sub

Private Sub cmbArea_Beforeupdate()
cmbEquipment.Requery
End Sub

Hope this helps
--

---
The glass is neither half empty nor half full. It is simply twice the size
it needs to be.





- Show quoted text -

Thank you for your response. Unfortunately, the option you suggested
did not produce the desired result. Perhaps if I explain further what
I am trying to do, you can offer some alternatives.

Shopcbo based on Shopqry based on Shoptbl. Columns in the table are
ShopName and ShopID. The shop same is self explanatory. The shop ID
is a field a created to match up with a field in the equipment
table.

Areacbo based on Areaqry based on Areatbl. Columns in the table are
AreaName, AreaLink and ShopName. The area link is a field created to
identify each area that a piece of equipment belongs to and has a
corresponding field in the equipment table.

I have revised the form with VB code, as opposed to a macro, to run
the requery so that whenever the Shopcbo is changed, the Areacbo
"updates" with the area selections available to that specific shop.

Now the Equipmenttbl has 4 columns: EquipmentName, EquipmentNumber,
AreaLink, ShopID. The Equipment table was created from a make table
query because I linked 3 separate equipment tables from outher
databases to this database I am working in. Three shops maintain
separate databases with equipment listed for other purposes. Since I
did not want have to update a table in this database everytime a piece
of equipment was added, or changed or deleted, I chose to link them to
this database. I performed a union query to bring them all together
into one Equipmenttbl. As a result of the Equipmenttble data coming
from separate databases, the AreaLink number is not unique. For
example: Two shops have several areas in their shop named the same
and since they work out of two different databases, the AreaLink
number is also the same. That is why I added the ShopID indicated to
differentiate between the two shops. So, even though the areas may be
named and numbered the same, the equipment in these areas is
completely different. Follow me so far?

ShopName