use filter string in a query (1 Viewer)

[kgcrowther]

I created a form that has a list box and based on which items you select it will open another form for editing and filter so that only those items can be edited. It works really well. (The idea came from John Viescas who wrote a book for microsoft.)

I'd like to use this same form to print a certain report for all the items that one selects. I wrote the program in the OnClick to write the sql statement that creates a simple sub-query, and I put this string in the control source of the report. I thought it would just read the code and understand that it is a sub-query, but it said it couldn't find the record source. I've tried to insert the statement directly into the query. Any ideas???

This is the code:

1. I have a public module:

Public gstrAssets As String

Public Function GetAssetID() As String
GetAssetID = gstrAssets
End Function

2. I have this function in the OnClick()

Private Sub Asset_Maintenance_Report_Click()
On Error GoTo Err_Asset_Maintenance_Report_Click
Dim strWhere As String, varItem As Variant

' If no items selected, then nothing to do
If Me!lstAssets.ItemsSelected.Count = 0 Then Exit Sub
' Loop through the items selected collection
For Each varItem In Me!lstAssets.ItemsSelected
' Grab the AssetID column for each selected item
strWhere = strWhere & Me!lstAssets.Column(0, varItem) & ","
Next varItem
' Trash the extra comma on the "IN" string
strWhere = Left$(strWhere, Len(strWhere) - 1)
' Write a sub-query in sql
gstrAssets = "SELECT [Asset Maintenance Query].* FROM [Asset Maintenance Query] " & _
"WHERE ((Assets.AssetID) IN (" & strWhere & "));"
' Open the form to enter the two fields needed for the report
DoCmd.openform ("Asset Maintenance")
DoCmd.Close acForm, Me.Name


Exit_Asset_Maintenance_Report_Click:
Exit Sub

Err_Asset_Maintenance_Report_Click:
MsgBox Err.Description
Resume Exit_Asset_Maintenance_Report_Click

End Sub