I don't know if this is possible, but...
I have a main form with a subform. I would like to build a search form that allows users to base their search on many criteria. I know how to do this for the main form, but I don't know how to find a record if the criteria is a field on the subform. Here is a portion of what I have:

'this creates a dynamic SQL statement that changes with each new criteria
Private Sub search_Click()
Dim mySql As String
Dim myWhere As String

'search the letterinfo and letter tables
mySql = "SELECT tbl_letterinfo.*, tblletter.* FROM tbl_letterinfo LEFT JOIN tblletter ON tbl_letterinfo.rec_id = tblletter.recid"
myWhere = ""

If Nz(Forms!frmsearch!txtname) <> "" Then
If myWhere <> "" Then
myWhere = myWhere & " AND"
End If
myWhere = myWhere & " (tbl_letterinfo.lastname Like '*" & Forms!frmsearch!txtname & "*')"
End If
If Nz(Forms!frmsearch!cboletter) <> "" Then
If myWhere <> "" Then
myWhere = myWhere & " AND"
End If
myWhere = myWhere & " (tblletter.lettertype Like '*" & Forms!frmsearch!cboletter & "*')"
End If

'add WHERE statement if any search criteria are provided
If myWhere <> "" Then
mySql = mySql & " WHERE " & myWhere
End If

DoCmd.OpenForm "frmmain", , mySql
DoCmd.Close acForm, "frmsearch"
End Sub

This works if I am only querying a field on the main form (based on tblletterinfo), but not if I am querying a field on the subform (based on tblletter). Is there any way to do this?

Thanks in advance!



[This message has been edited by LQ (edited 11-19-2001).]

Some further information:

I get an enter parameter value dialog box when I try to run the above SQL statement in my search form. But if I paste the statement into the SQL view in a query, it runs just fine.

Am I just trying to do something that can't be done???

Any ideas will be appreciated. I'm getting really frustrated here!!!