RunTime Error 2465 on RecordSource

[entrist]

RunTime Error 2450 on RecordSource

Hey guys and Girls. I am having a bit of a problem on this bit of source. It (for some strange reason) wont locate a form for me... The Error Code is actually 2450...

I have a form with a tabbed page (frmQuoteCenter), on one of the tabs is a search form (frmQuoteSearchPrime)... in the search form is a subform (frmQuoteSearch). I want it to update the figures shown with different search criterias, I have achieved this on a different vba script... i have copied and pasted, changing the neccessaries... and it wont friggen work?!

Private Sub quotesrchbang()
Dim strWherequote As String               'The criteria string.
Dim LngLenquote As Long                   'Length of the criteria string to append to.
Dim quotesearchsql As String
Dim quotesearchcount As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
    
    quotesearchsql = "SELECT Quotes.[Quote Number], Quotes.QuoteDate, Quotes.[Customer Number], Quotes.QuotePrice, " & _
    "Quotes.Dead, Quotes.Ordered, Quotes.[Quoted By], Customer.[First Name], Customer.[Last Name] " & _
    "FROM Customer INNER JOIN Quotes ON Customer.[Customer Number] = Quotes.[Customer Number] "

    quotesearchcount = 0
    
    If Me!srchQuoteID = "" Or IsNull(Me!srchQuoteID) Then
        ' Do Nothing
        Else
        strWherequote = strWherequote & "((Quotes.[Quote Number]) like " & """" & "*" & Me!srchQuoteID & """" & ") AND "
        quotesearchcount = quotesearchcount + 1
    End If
    
    If Me!srchQuotePrice = "" Or IsNull(Me!srchQuotePrice) Then
        ' Do Nothing
        Else
        strWherequote = strWherequote & "((Quotes.Quoteprice)=" & """" & Me!srchQuotePrice & """" & ") AND "
        quotesearchcount = quotesearchcount + 1
    End If
    
    If Me!srchCustID = "" Or IsNull(Me!srchCustID) Then
        ' Do Nothing
        Else
        strWherequote = strWherequote & "((Quotes.[Customer Number]) Like " & """*" & Me!srchCustID & "*""" & ") AND "
        quotesearchcount = quotesearchcount + 1
    End If
    
    If Me!srchFirstname = "" Or IsNull(Me!srchFirstname) Then
        ' Do Nothing
        Else
        strWherequote = strWherequote & "((Customer.[First Name]) Like " & """*" & Me!srchFirstname & "*""" & ") AND "
        quotesearchcount = quotesearchcount + 1
    End If

    If Me!srchLastname = "" Or IsNull(Me!srchLastname) Then
        ' Do Nothing
        Else
        strWherequote = strWherequote & "((Customer.[Last Name]) Like " & """*" & Me!srchLastname & "*""" & ") AND "
        quotesearchcount = quotesearchcount + 1
    End If
    
    If Me!srchDead = False Then
        ' Do Nothing
        Else
        strWherequote = strWherequote & "((Quotes.Dead)=False) AND "
        quotesearchcount = quotesearchcount + 1
    End If
    
    If Me!srchOrdered = False Then
        ' Do Nothing
        Else
        strWherequote = strWherequote & "((Quotes.Ordered)=True) AND "
        quotesearchcount = quotesearchcount + 1
    End If
    
        
    If Me!srchQuoteDate1 = "" Or IsNull(Me!srchQuoteDate1) Then
        ' Do Nothing
        Else
        If Me!srchQuoteDate2 = "" Or IsNull(Me!srchQuoteDate2) Then
        strWherequote = strWherequote & "((Quotes.Quotedate)=" & Format(Me!srchQuoteDate1, conJetDate) & ") AND "
        quotesearchcount = quotesearchcount + 1
        Else
        strWherequote = strWherequote & "((Quotes.Quotedate) Between " & Format(Me!srchQuoteDate1, conJetDate) & " And " & Format(Me!srchQuoteDate2, conJetDate) & ") AND "
        quotesearchcount = quotesearchcount + 1

        End If
    End If
    
    If Me!srchQuoteBy = "" Or IsNull(Me!srchQuoteBy) Then
        ' Do Nothing
        Else
        strWherequote = strWherequote & "((Quotes.[Quoted By])=" & Me!srchQuoteBy & ") AND "
        quotesearchcount = quotesearchcount + 1
        
        End If
        
    
    If quotesearchcount > 0 Then
    strWherequote = "WHERE (" & strWherequote
    LngLenquote = Len(strWherequote) - 5
    strWherequote = Left$(strWherequote, LngLenquote)
    strWherequote = strWherequote & ")"
    Else
    strWherequote = ""
    End If
    
    quotesearchsql = quotesearchsql & strWherequote & "ORDER BY Quotes.[Quote Number] DESC;"
Dim oquoteDB As Database
Dim oquoteQuery As QueryDef
Set oquoteDB = CurrentDb
Set oquoteQuery = oquoteDB.QueryDefs("qryQuoteSearch")
oquoteQuery.SQL = (quotesearchsql)
Set oquoteQuery = Nothing
Set oquoteDB = Nothing
Forms![frmQuoteCenter]![frmQuoteSearchPrime]![frmQuoteSearch].Form.RecordSource = quotesearchsql
End Sub

The working form is....

Private Sub msgsrchbang()
Dim strWheremsg As String               'The criteria string.
Dim LngLenmsg As Long                   'Length of the criteria string to append to.
Dim messagesearchsql As String
Dim msgsearchcount As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
    
    messagesearchsql = "SELECT Messages.MessageID, Messages.[Customer Number], Customer.Title, Customer.[First Name], Customer.[Last Name]," _
    & " Messages.[Order Number], Messages.[Quote Number], Messages.Message, Messages.MessageDate, Messages.MessageAction, Messages.MessageFor," _
    & " Messages.Messageby FROM Customer RIGHT JOIN Messages ON Customer.[Customer Number] = Messages.[Customer Number]"
    
    msgsearchcount = 0
    
    If Me!srchMessID = "" Or IsNull(Me!srchMessID) Then
        ' Do Nothing
        Else
        strWheremsg = strWheremsg & "((Messages.MessageID)=" & """" & Me!srchMessID & """" & ") AND "
        msgsearchcount = msgsearchcount + 1
    End If
    
    If Me!srchCustID = "" Or IsNull(Me!srchCustID) Then
        ' Do Nothing
        Else
        strWheremsg = strWheremsg & "((Messages.[Customer Number])=" & """" & Me!srchCustID & """" & ") AND "
        msgsearchcount = msgsearchcount + 1
    End If
    
    If Me!srchFirstname = "" Or IsNull(Me!srchFirstname) Then
        ' Do Nothing
        Else
        strWheremsg = strWheremsg & "((Customer.[First Name]) Like " & """*" & Me!srchFirstname & "*""" & ") AND "
        msgsearchcount = msgsearchcount + 1
    End If
    
    If Me!srchLastname = "" Or IsNull(Me!srchLastname) Then
        ' Do Nothing
        Else
        strWheremsg = strWheremsg & "((Customer.[Last Name]) Like " & """*" & Me!srchLastname & "*""" & ") AND "
        msgsearchcount = msgsearchcount + 1
    End If
    
    If Me!srchOrdernumber = "" Or IsNull(Me!srchOrdernumber) Then
        ' Do Nothing
        Else
        strWheremsg = strWheremsg & "((Messages.[Order Number]) Like " & """*" & Me!srchOrdernumber & """" & ") AND "
        msgsearchcount = msgsearchcount + 1
    End If
    
    If Me!srchQuoteNumber = "" Or IsNull(Me!srchQuoteNumber) Then
        ' Do Nothing
        Else
        strWheremsg = strWheremsg & "((Messages.[Order Number]) Like " & """*" & Me!srchQuoteNumber & """" & ") AND "
        msgsearchcount = msgsearchcount + 1
    End If
    
    If Me!srchMessage = "" Or IsNull(Me!srchMessage) Then
        ' Do Nothing
        Else
        strWheremsg = strWheremsg & "((Messages.[Order Number]) Like " & """*" & Me!srchMessage & "*""" & ") AND "
        msgsearchcount = msgsearchcount + 1
    End If
    
    If Me!srchMessageDate1 = "" Or IsNull(Me!srchMessageDate1) Then
        ' Do Nothing
        Else
        If Me!srchMessageDate2 = "" Or IsNull(srchMessageDate2) Then
        strWheremsg = strWheremsg & "((Messages.MessageDate)=" & Format(Me!srchMessageDate1, conJetDate) & ") AND "
        msgsearchcount = msgsearchcount + 1
        Else
        strWheremsg = strWheremsg & "((Messages.MessageDate) Between " & Format(Me!srchMessageDate1, conJetDate) & " And " & Format(Me!srchMessageDate2, conJetDate) & ") AND "
        msgsearchcount = msgsearchcount + 1

        End If
    End If
    
    If Me!srchMessageFor = "" Or IsNull(Me!srchMessageFor) Then
        ' Do Nothing
        Else
        strWheremsg = strWheremsg & "((Messages.MessageFor)=" & Me!srchMessageFor & ") AND "
        msgsearchcount = msgsearchcount + 1
    End If

    If Me!srchMessageby = "" Or IsNull(Me!srchMessageby) Then
        ' Do Nothing
        Else
        strWheremsg = strWheremsg & "((Messages.Messageby)=" & Me!srchMessageby & ") AND "
        msgsearchcount = msgsearchcount + 1
    End If

    
    If msgsearchcount > 0 Then
    strWheremsg = "WHERE (" & strWheremsg
    LngLenmsg = Len(strWheremsg) - 5
    strWheremsg = Left$(strWheremsg, LngLenmsg)
    strWheremsg = strWheremsg & ")"
    Else
    strWheremsg = ""
    End If
    messagesearchsql = messagesearchsql & strWheremsg & "ORDER BY Messages.MessageID DESC;"
Dim oDB As Database
Dim oQuery As QueryDef
Set oDB = CurrentDb
Set oQuery = oDB.QueryDefs("Message Search")
oQuery.SQL = (messagesearchsql)
Set oQuery = Nothing
Set oDB = Nothing
Forms![Message Center]![MessageSearchPrime]![Message Search].Form.RecordSource = messagesearchsql
End Sub

I am a self taught vba scriptor so i am probably doing lots of things wrong.... but the 2nd script works... the first doesnt...

thank you all in advance... =)

Entrist

PS The problem i get is that it wont find the table.... the Forms!tabletabltable.Form.Recordsource = SQL is at the bottom of the first source code is where i am getting the debuggin problem

 

[entrist]

Hey Guys I Figured Out the Problem. When I Copied and Pasted the Forms/Queries, I Changed the Subforms path locations to the correct ones but I didnt change the Name of it in the form. As Forms.blah.blah locates the subform Name In the form and not the Subforms actual name. It couldnt Find it...

If anyone can explain this better it may help alot more newbs like me....