Crosstab Query on Form

[primagic]

Hi
The database I set up with two tables - tblItems and tblComponents.

One Item can have many components, So I created another table called tblProducts.

It works like:

Item Component Quantity
---- --------- --------
Item 1 Gas 5
Item 1 Oil 20
Item 1 Valve 8
Item 2 Gas 3
Item 2 Cans 6

My crosstab query displays all the items down the left hand side and along the top it displays the components, then in the middle sells it displays the quantity.

I want to be able to create a form using the crosstab query but will be empty. However, I want the user to be able to select an Item from a dropdown list and it will populate the components in the crosstab query.

So this will be an orders form and the user can select as many items as they want and it will display the components required.

I was thinking I may need to use DLOOKUP but cant figure it out

Any help please

Thanks

 

[Remou]

I suggest you use a multi-select listbox, rather than a combobox (dropdown), because it will allow the user to select multiple items.

You can create a list from the listbox like so:

'Assumes that the first column has a numeric ID for
'the items selected
For Each itm In Me.lstItems.ItemsSelected
    strItems = strItems & "," & Me.lstItems.Column(0, itm)
Next

'Get rid of initial comma
strItems = Mid(strItems,2)

You can then use this list as a parameter to build your limited query:

[tt]strSQL="SELECT * FROM qryCrosstab WHERE ItemID In (" _
& strItems & ")"[/tt]

This can be use to build a query:

   If DLookup("Name", "MSysObjects", "Name= 'qryItems'") <> "" Then
        Set qdf = CurrentDb.QueryDefs("qryItems")
        qdf.SQL = strSQL
    Else
        Set qdf = CurrentDb.CreateQueryDef("qryItems", strSQL)
    End If

Or you could create the crosstab itself using the list, or use the list as a parameter.

You can set the Source Object of a subform control to a query.

http://lessthandot.com