Hello all:

I have a form that opens up named "Order_Form". I have a Lookup command button on it. When I click 'Lookup' then the code listed below runs and a read-only datasheetview window open using the Orders table. What I would like to do is create a subdatasheet using vba to link it to the Orders_Detail table via OrderName. Can that be done?

Thanks,
Tony


My code:
=================================================
Private Sub cmdSearchOrders_Click()
    Dim db As Database
    Dim QD As QueryDef
    Dim where As Variant
    Set db = CurrentDb()

    On Error Resume Next
    db.querydefs.Delete ("Orders_Dynamic_Query")
    On Error GoTo 0

    where = Null
    If Not IsNull(Me![scboOrderName]) Then
        where = where & " AND [OrderName] = '" + Me.scboOrderName + "'"
    End If

    If Not IsNull(Me![scboOrderType]) Then
        where = where & " AND [OrderType] = '" + Me.scboOrderType + "'"
    End If

    Set QD = db.CreateQueryDef("Orders_Dynamic_Query", _
    "Select * from Orders " & (" where " + Mid(where, 6) & ";"))
    DoCmd.OpenQuery "Orders_Dynamic_Query", , acReadOnly

End Sub

I did it another way..

I created a form called Orders_Dynamic_Query_Form and set it as a datasheet linked to my Orders_Dynamic_Query from my Search code. I then created a subform called Orders_Dynamic_Query_Subform and set it as a datasheet for my Orders_Revision Table and linked the ordername fields together from the Orders_Dynamic_Query and the Orders_Revision_Table. I dragged the subform to the main form and replace the following code in my search subroutine

DoCmd.OpenQuery "Orders_Dynamic_Query", , acReadOnly

with this:
DoCmd.OpenForm "Orders_Dynamic_Query_Form", acFormDS, , , acFormReadOnly, acDialog


It works great.