2455 error - invalid reference to property RecordSource

[neobrainless]

So trying to fix one error, I have discovered/created another. I was advised to start a new thread and attach the full code I'm 'working with' ('ineffectually clubbing at' might be a better term to describe where I'm at right now!).

My goal with this code is to allow me to search my simple database (two tables linked by an autonumbered ID column). I have adapted code from the internet, using help from here and my very rudimentary VBA knowledge (mostly from Excel macros and logic, rather than Access know how).

Here's the VBA bits:

Option Compare Database
Option Explicit

Private Sub btnClear_Click()
    Dim intIndex As Integer
    
    ' Clear all search items
    Me.txtConcessionNo = ""
    Me.txtCategory = ""
    Me.txtProject = ""
    Me.txtDateCreated = ""
    Me.txtPartNo = ""
    Me.txtDescription = ""
    Me.txtDocumentNo = ""
    Me.txtReferenceNo = ""
    
    btnSearch_Click
    
    
    
End Sub

Private Sub btnSearch_Click()
    
    Dim sqlinput As Variant
    
    ' Update the record source
    sqlinput = "SELECT * FROM FileInfQry " & BuildFilter
    
    Debug.Print sqlinput
    
    Me.FileInfSubForm.Form.RecordSource = sqlinput
    
    ' Requery the subform
    Me.FileInfSubForm.Requery
End Sub


Private Sub Form_Load()
    
    ' Clear the search form
    btnClear_Click
    
End Sub

Private Function BuildFilter() As Variant
    Dim varWhere As Variant

    Dim varItem As Variant
    Dim intIndex As Integer

    varWhere = Null  ' Main filter

    
    ' Check for Concession Number
    If Me.txtConcessionNo > "" Then
        varWhere = varWhere & "[ConcessionNo] LIKE """ & Me.txtConcessionNo & "*"" And "
    End If
       
    ' Check for Category
    If Me.txtCategory > "" Then
        varWhere = varWhere & "[Category] LIKE """ & Me.txtCategory & "*"" And "
    End If
    
    ' Check for Project
    If Me.txtProject > "" Then
        varWhere = varWhere & "[Project] LIKE """ & Me.txtProject & "*"" And "
    End If
    
    ' Check for Date Created
    If Me.txtDateCreated > "" Then
        varWhere = varWhere & "[DateCreated] #" & Format(Me.txtDateCreated, "DD/MM/YYYY") & "# AND "
    End If

    ' Check for Part Number
    If Me.txtPartNo > "" Then
        varWhere = varWhere & "[PartNo] LIKE """ & Me.txtPartNo & "*"" AND "
    End If
    
    ' Check for Description
    If Me.txtDescription > "" Then
        varWhere = varWhere & "[Description] LIKE """ & Me.txtDescription & "*"" AND "
    End If
    
    ' Check for Document Number
    If Me.txtDocumentNo > "" Then
        varWhere = varWhere & "[DocumentNo] LIKE """ & Me.txtDocumentNo & "*"" AND "
    End If
    
    ' Check for Reference Number
    If Me.txtReferenceNo > "" Then
        varWhere = varWhere & "[ReferenceNo] LIKE """ & Me.txtReferenceNo & "*"" AND "
    End If
    
 
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
        
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
    
    BuildFilter = varWhere
    
End Function

And here's the query:

SELECT      FileInformation.ConcessionNo AS ConcessionNo
                , FileInformation.Category AS Category
                , FileInformation.Project AS Project
                , FileInformation.DateCreated AS DateCreated
                , FileInformation.PartNo AS PartNo
                , FileInformation.Description AS Description
                , FileInformation.DocumentNo AS DocumentNo
                , FileInformation.ReferenceNo AS ReferenceNo
                , Files.LinkPath AS Path

FROM FileInformation, Files

WHERE (((FileInformation.ID)=[Files].[FileID]));

Any help would be greatly appreciated! :)


[z{Link to backstory: http://bytes.com/topic/access/answer...ismatch-errors]

[zmbd]

Before we start, please ensure that you have a backup copy of the project.

Line 24 of the posted code block post #1 please insert a STOP command.
Save, close, reopen the form
Setup the form with the entries a user would normally made and click on the command button.
The code should go into debug mode and the VBA editor window open with the stop code highlighted (usually in yellow).
Using the {[F8]} key step thru your code - slowly. Please report back what line the error actually occurs.

[ADezii]

I didn't have much of a chance to look at the Code, but what typically causes this Error is that the User attempts to access the Sub-Form itself as opposed to the actual Sub-Form Control. The Syntax would be:

With Me![<Name of Sub-Form Control>]
  .Form.RecordSource = "Table, Query, SQL Statement"
    .Requery
End With

[zmbd]

I'm suspecting that the fail will be on either line 32 or 35 for the reason given by ADEzii in that both make reference to the subform.

This is why I wanted the step thru as I'm not sure which of the two will fail.

[neobrainless]

Ok, so I followed zmbd's instructions in the first reply and I got a DIFFERENT error >.<

Run-time error '3075':

Syntax error (missing operator)in query expression '[DateCreated] #21/08/1991#'

And it failed on line 32 of the first lot of code in my original post - where you predicted! Just odd (to me) that it's a different error today! :/

Thanks :)

[zmbd]

In the build function starting on line #46
The code starting on line #70 is as follows

' Check for Date Created
    If Me.txtDateCreated > "" Then
        varWhere = varWhere & "[DateCreated] #" & Format(Me.txtDateCreated, "DD/MM/YYYY") & "# AND "
    End If

The

Format(Me.txtDateCreated, "DD/MM/YYYY")

Is wrong.
It needs to be

Format(Me.txtDateCreated, "MM/DD/YYYY")

as per the link I provided in BackStory Post#10

Please make this change, do a compile and the restart and do the step thru as before.

[neobrainless]

*facepalm* I thought I'd changed that, must have forgotten to click save after or something >.<

And it's still having the same error I reported in my last post

Cheers

[zmbd]

same error yes... however, when you stepped thru the code, which line did the error occure on?

[neobrainless]

It stopped on the same line as before, line 32.

[MikeTheBike]

Hi

You could try putting an = sign in ie

[DateCreated] = #21/08/1991#

??

MTB

[neobrainless]

Thanks, that's cured it! Knew it would be something simple and probably obvious if you have the experience!

Cheers :)

[zmbd]

poooh, I missed when I was looking at the date format (#6) - colour me a tad embarrassed... just proves that the more eyes on task will often find the solution!
(also I was expecting the reference to the subform issue)