Add Subform . . . Datasheet . . . to a Form.

[Robeen]

I have a Form on which I have a Multi-Select ListBox from which I am able to retrieve IDNumbers that are attached to Names that also show in the ListBox.



What is the best way to take those IDNumbers and use them to display [on the same Form] related data from another Table?

I was thinking of a subform in datasheet view, perhaps?
The other Table also has a matching IDNumber field.

Since the user is able to select multiple IDNumbers - the datasheet has to be able to display detail for all the IDNumbers selected.


I'd appreciate any help.

[pbaldy]

If I understand correctly, you could modify this technique:

http://www.baldyweb.com/multiselect.htm

instead of opening a report, building SQL and setting the record source of the subform to it.

[Robeen]

Thanks, Paul!
I think what I ended up doing was pretty much on the lines of what you recommended. I did go to your site while I was searching.

Here's what I did:
1. I created a report [so I could add Grouping features easily] that met my requirements and then dropped the report onto my Form.
2. Behind a Command Button on the Form [that the report was now part of] - I put the following code:

Dim oItem As Variant
Dim sTemp As String, strSQL As String
Dim iCount As Integer
iCount = 0

If Me!NamesList.ItemsSelected.Count <> 0 Then
    For Each oItem In Me!NamesList.ItemsSelected
        If iCount = 0 Then
            sTemp = sTemp & Me!NamesList.ItemData(oItem)
            iCount = iCount + 1
        Else
            sTemp = sTemp & "," & Me!NamesList.ItemData(oItem)
            iCount = iCount + 1
        End If
    Next oItem
Else
    MsgBox "No Items selected from List.", vbInformation
    Exit Sub  'Nothing was selected
End If

strSQL = "SELECT a.VenNum, b.VenName, a.InvNum, a.DteVouchEnt, a.DueDte, a.PaidDte, CCur(Abs(a.GrossAmt)) AS GrossAmount, a.CheckNum, a.PaidAmt, a.GrossAmt " _
            & "FROM ApTest AS a INNER JOIN ApVenTest AS b ON a.VenNum = b.VenNum " _
            & "WHERE (((a.VenNum)=[b].[VenNum] And (a.VenNum) In (" & sTemp & ")) AND ((Abs([GrossAmt])) In " _
            & "(SELECT Abs([GrossAmt]) " _
            & "FROM [ApTest] as Temp " _
            & "WHERE Abs(Temp.[GrossAmt]) = [Temp].GrossAmt " _
            & "GROUP BY Abs([GrossAmt]) " _
            & "HAVING Count(*) > 1))) " _
            & "ORDER BY a.VenNum, Abs([GrossAmt]) DESC;"
            
Me!InvoiceDetail_Report.Report.RecordSource = strSQL

The subquery was to fulfill part of my requirements that specified that records that had no matching GrossAmt should not be displayed.

Once again - thanks for your help!!