Query Inventory/Populate form Problem... Help

[ipp]

Does any one have ideas how to do the following ???

A little bit of background on my App:

Equipment Type - Type of an Indiviual Piece of Equipment.
Equipment Kit Type - The Type a KIT of individual pieces Equipment

The Customer is basically creating Kits of equipment that are used internally to perform some work and when completed the Kit is returned to stock and kept together as a kit...

Part of this process is when the customer defines a new Equipment Type via a form he assigns the Kit Types that the new piece of equipment may be associated with when a Kit is built.... Each Equipment Type may be associated with multiple Kit Types... These values are stored in the Equipment Types Table for later use... At sometime in the future the customer will build a Kit of individual equipment to satisfy the need of his customer... and check this Kit out of Inventory.. (the Kit will later be returned and placed back into Inventory after the work is done)..

I have a table called Equipment_Types that contain the following fields:


Autonumber,Prefix,Type,KitType

In this table I track Types of equipment and the associated Equipment Kits that they may be associated with, i.e.


10 A Air Sampler Air Test
12 N Noise Dosi Noise Test
13 A Air Meter Air Test
14 A Air Meter Noise Test
15 A Air Meter Dummy


This table is populated via a form and as you can see above an Equipment Type may be associated with several Equipment Kits. I have a second form that is used to create an Equipment Kit and associated individual pieces of equipment with that Kit using the Type and an Asset Number...

The individual equipment records are stored in a table called Inventory... In the Inventory table I track Equipment Type, Serial Number, Model, Asset Number, Kit Type, etc, etc, etc...

Here is a better clarification of the steps I am trying to take when the actual Equipment Kit is built...

1) The customer selects the type of Equipment Kit they wish to build from a
Combo Box.

2) I want to take that Equipment KIT Type and perform a select on the Equipments
Types Table that returns all of the Specific Individual Equipment Types that
may be associated with this Equipment KIT Type.

3) This query would return anywhere from 1 to 20 items..

4) I would then like to use each of these items (Equipment Types) to return all of
the Inventory Records for that Equipment Type so that I can assign them to a
Combo box on the form...

5) Then I would move to the next Equipment Type and do the same... Until I have
all of my Equipment Type Boxes on the form populated.... By the way I have 20
boxes on the form and they start out disabled and I would enable each box as it
is populated...

6) The customer could then pick each an Inventory record to assign to this
Equipment KIT for each Equipment Type..

I can do a SELECT get the data but its all lumped together... what I would like to do is get the data in a way that I can seperate the records for each Equipment Type so that I can associate each set of records to a different combo box based on Equipment Type....

The only way I could figure out how to do this was to try and loop through the query and assign each group of records or by a Query/SubQuery but couldn't get either to work... Any help would be greatly appreciated....

 

[RickSpr]

I think you'll have to use VBA code to do this. But you don't have to fill the combo box lists directly. Instead, in your KitType combo box's AfterUpdate event, run this code (I'm assuming the Inventory combo boxes are named cboInv1, cboInv2, etc.):

    Private Sub cboKitType_AfterUpdate
        Dim db As DAO.Database, rst As DAO.Recordset
        Dim SQL As String
        Dim index As Integer
        Dim cbo As ComboBox

        Set db = CurrentDb()
        SQL = "SELECT Type FROM Equipment_Types WHERE KitType = '" & Me!KitType.Value & "';"
        Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
        index = 1
        Do While Not rst.EOF
            SQL = &quot;SELECT <list of fields> FROM Inventory WHERE EquipmentType = '&quot; &amp; rst!EquipmentType &amp; &quot;';&quot;
            Set cbo = Me!Controls(&quot;cboInv&quot; &amp; CStr(index))
            cbo.RowSource = SQL
            cbo.Enabled = True
            cbo.Requery
            rst.MoveNext
        Loop
        rst.Close
        Set cbo = Nothing
        Set rst = Nothing
        Set db = Nothing
    End Sub

What this does is loop through a query that returns just the equipment types, and constructs Row Source queries for each inventory combo box. The inventory combo boxes are indexed by their names in the Controls collection, calculating their names as a string. You could do it your way, too, but this code is a bit simpler. Rick Sprague