Re: 3 linked combo boxes in a form
- From: Opal <tmwelton@xxxxxxxxxx>
- Date: Sat, 18 Aug 2007 18:15:21 -0700
On Aug 16, 10:51 pm, Vantastic <vantastic...@xxxxxxxxxxxxxxxxxx>
wrote:
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.
"Opal" wrote:
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!- Hide quoted text -
- 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
.
- References:
- 3 linked combo boxes in a form
- From: Opal
- 3 linked combo boxes in a form
- Prev by Date: Re: Help with between dates code
- Next by Date: 3 linked combo boxes in a form Options
- Previous by thread: 3 linked combo boxes in a form
- Next by thread: Re: control source to update text box
- Index(es):
Relevant Pages
|
