Recordsource problem

[lxh]

Hi There

I'm using a generic search facility and having problems implemeting it. I get the error "Object doesn't support this property or method". The search code is below. Can anybody see the problem?

There is a subform (searchresultssubform) in a main form (findrecord). The search in a table called projectdetails.

Option Compare Database
Option Explicit
Private Sub FIND_Click() '   <<<<< This line is put in by the button wizard
On Error GoTo Err_FIND_Click '  <<<<< This line is put in by the button wizard
'‘------------------------------ Starting here ----------------------
    Dim MySQL As String, MyCriteria As String, MyRecordSource As String
    Dim ArgCount As Integer
    '  Initialize SELECT statement.
    MySQL = "SELECT * FROM [FindRecord] WHERE "
    
    '  Use values entered in text boxes in form header to create criteria for WHERE clause.
        'text box name on form     'Field name in Table  'blank info ' number of times run Add
    AddToWhere [Find1], "[ProjectDetails]![NameProject] ", MyCriteria, ArgCount
    AddToWhere [Find2], "[ProjectDetails]![ProjectNumber] ", MyCriteria, ArgCount
   
    '  If no criterion specifed, return all records.
    If MyCriteria = "" Then
        MyCriteria = "True"
    End If

    '  Create SELECT statement.
    MyRecordSource = MySQL & MyCriteria

    ' Optional Order By clause
    If Me![Find1] <> "" Then
        MyRecordSource = MySQL & MyCriteria & " ORDER BY [ProjectDetails]![NameProject]"
    ElseIf Me![Find2] <> "" Then
        MyRecordSource = MySQL & MyCriteria & " ORDER BY [ProjectDetails]![ProjectNumber]"
    Else
        MyRecordSource = MySQL & MyCriteria & " ORDER BY [ProjectDetails]![NameProject]"
    End If
  

     ' set record source to Subform
'THIS IS THE LINE CAUSING THE PROBLEM 
Me![searchresultssubform].Form.RecordSource = MyRecordSource
    
'-------------------------------------------------------------
'the follow lines are put in by the button Wizard
Exit_FIND_Click:
    Exit Sub

Err_FIND_Click:
    MsgBox Error
    Resume Exit_FIND_Click
    
End Sub

Here's some additional info on the the procedure:
http://www.barcodeone.com/access_mega_search.htm

By the end of the sub "myrecordsource" = the string "SELECT * FROM [FindRecord] WHERE [ProjectDetails]![NameProject] Like 'jamie*' ORDER BY [ProjectDetails]![NameProject]"

Does any one have any thoughts?

Thanks
Lex

 

[chris_dono]

try calling the subform by it's name instead of a control on the main form

something like forms("subform").form.recordsource