Prob. to display only form that match the criteria on listbox.

[awinnn]

Hi,
I have combobox, cboAssetCode (used to select AssetCode) and listbox, lstAssetDetails (to display all info that relevant with the selected AssetCode).
My problem is, all the AssetCode have different info. It means, each AssetCode has their own form. Any idea?
This code is taken from the sample database on the FAQ. This is what i've done..

Private Sub lstAssetDetails_DblClick(Cancel As Integer)
    DoCmd.OpenForm "frmHardware", , , "[ID] = " &
    Me.lstAssetDetails, , acDialog
End Sub

Private Sub cboAssetCode_AfterUpdate()
    Dim strSQL As String, strOrder As String, strWhere As  
               String
    Dim qryDef As QueryDef
    Dim dbNm As Database
    Set dbNm = CurrentDb()

    strSQL = "SELECT ID, AssetCode, AssetNo, Desc, 
             SerialNo FROM tblHardware"

    strWhere = "WHERE"

    strOrder = "ORDER BY tblHardware.ID;"

If Not IsNull(Me.cboAssetCode) Then 
    strWhere = strWhere & " (tblHardware.AssetCode) 
               Like '*" & Me.cboAssetCode & "*'  AND" 
End If

strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

Me.lstAssetDetails.RowSource = strSQL & " " & strWhere 
           & "" & strOrder
End Sub

It works if the AssetCode is the same. What i'm trying to do is to combine all the different AssetCode (using UNION ALL) and when user double click on the listbox, it will goes to the respective form.
Any idea?
Thanx..

 

[FancyPrairie]

I would think the easiest way to handle it would be to name your forms after the AssetCode. For example, if the AssetCode is "ABC", then the form to open would be "frmABC". Therefore, in the OnDoubleClick event of the listbox, do something like this:

Docmd.OpenForm "frm" & lstAssetDetails.Value

If you can't, for whatever reason, use a naming convention, then stick the form name within the query results. Column(0) of the list box would contain the AssetCode and Column(1) would contain the form name. In the OnDoubleClick event, do this

Docmd.OpenForm lstAssetDetails.Column(1)