473,320 Members | 1,853 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

2455 error - invalid reference to property RecordSource

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:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub btnClear_Click()
  5.     Dim intIndex As Integer
  6.  
  7.     ' Clear all search items
  8.     Me.txtConcessionNo = ""
  9.     Me.txtCategory = ""
  10.     Me.txtProject = ""
  11.     Me.txtDateCreated = ""
  12.     Me.txtPartNo = ""
  13.     Me.txtDescription = ""
  14.     Me.txtDocumentNo = ""
  15.     Me.txtReferenceNo = ""
  16.  
  17.     btnSearch_Click
  18.  
  19.  
  20.  
  21. End Sub
  22.  
  23. Private Sub btnSearch_Click()
  24.  
  25.     Dim sqlinput As Variant
  26.  
  27.     ' Update the record source
  28.     sqlinput = "SELECT * FROM FileInfQry " & BuildFilter
  29.  
  30.     Debug.Print sqlinput
  31.  
  32.     Me.FileInfSubForm.Form.RecordSource = sqlinput
  33.  
  34.     ' Requery the subform
  35.     Me.FileInfSubForm.Requery
  36. End Sub
  37.  
  38.  
  39. Private Sub Form_Load()
  40.  
  41.     ' Clear the search form
  42.     btnClear_Click
  43.  
  44. End Sub
  45.  
  46. Private Function BuildFilter() As Variant
  47.     Dim varWhere As Variant
  48.  
  49.     Dim varItem As Variant
  50.     Dim intIndex As Integer
  51.  
  52.     varWhere = Null  ' Main filter
  53.  
  54.  
  55.     ' Check for Concession Number
  56.     If Me.txtConcessionNo > "" Then
  57.         varWhere = varWhere & "[ConcessionNo] LIKE """ & Me.txtConcessionNo & "*"" And "
  58.     End If
  59.  
  60.     ' Check for Category
  61.     If Me.txtCategory > "" Then
  62.         varWhere = varWhere & "[Category] LIKE """ & Me.txtCategory & "*"" And "
  63.     End If
  64.  
  65.     ' Check for Project
  66.     If Me.txtProject > "" Then
  67.         varWhere = varWhere & "[Project] LIKE """ & Me.txtProject & "*"" And "
  68.     End If
  69.  
  70.     ' Check for Date Created
  71.     If Me.txtDateCreated > "" Then
  72.         varWhere = varWhere & "[DateCreated] #" & Format(Me.txtDateCreated, "DD/MM/YYYY") & "# AND "
  73.     End If
  74.  
  75.     ' Check for Part Number
  76.     If Me.txtPartNo > "" Then
  77.         varWhere = varWhere & "[PartNo] LIKE """ & Me.txtPartNo & "*"" AND "
  78.     End If
  79.  
  80.     ' Check for Description
  81.     If Me.txtDescription > "" Then
  82.         varWhere = varWhere & "[Description] LIKE """ & Me.txtDescription & "*"" AND "
  83.     End If
  84.  
  85.     ' Check for Document Number
  86.     If Me.txtDocumentNo > "" Then
  87.         varWhere = varWhere & "[DocumentNo] LIKE """ & Me.txtDocumentNo & "*"" AND "
  88.     End If
  89.  
  90.     ' Check for Reference Number
  91.     If Me.txtReferenceNo > "" Then
  92.         varWhere = varWhere & "[ReferenceNo] LIKE """ & Me.txtReferenceNo & "*"" AND "
  93.     End If
  94.  
  95.  
  96.     ' Check if there is a filter to return...
  97.     If IsNull(varWhere) Then
  98.         varWhere = ""
  99.     Else
  100.         varWhere = "WHERE " & varWhere
  101.  
  102.         ' strip off last "AND" in the filter
  103.         If Right(varWhere, 5) = " AND " Then
  104.             varWhere = Left(varWhere, Len(varWhere) - 5)
  105.         End If
  106.     End If
  107.  
  108.     BuildFilter = varWhere
  109.  
  110. End Function
  111.  
And here's the query:

Expand|Select|Wrap|Line Numbers
  1. SELECT      FileInformation.ConcessionNo AS ConcessionNo
  2.                 , FileInformation.Category AS Category
  3.                 , FileInformation.Project AS Project
  4.                 , FileInformation.DateCreated AS DateCreated
  5.                 , FileInformation.PartNo AS PartNo
  6.                 , FileInformation.Description AS Description
  7.                 , FileInformation.DocumentNo AS DocumentNo
  8.                 , FileInformation.ReferenceNo AS ReferenceNo
  9.                 , Files.LinkPath AS Path
  10.  
  11. FROM FileInformation, Files
  12.  
  13. WHERE (((FileInformation.ID)=[Files].[FileID]));
  14.  
Any help would be greatly appreciated! :)


[z{Link to backstory: http://bytes.com/topic/access/answer...ismatch-errors]
Jul 2 '13 #1

✓ answered by MikeTheBike

Hi

You could try putting an = sign in ie
Expand|Select|Wrap|Line Numbers
  1. [DateCreated] = #21/08/1991#
??

MTB

11 2664
zmbd
5,501 Expert Mod 4TB
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.
Jul 2 '13 #2
ADezii
8,834 Expert 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. With Me![<Name of Sub-Form Control>]
  2.   .Form.RecordSource = "Table, Query, SQL Statement"
  3.     .Requery
  4. End With
Jul 2 '13 #3
zmbd
5,501 Expert Mod 4TB
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.
Jul 2 '13 #4
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 :)
Jul 3 '13 #5
zmbd
5,501 Expert Mod 4TB
In the build function starting on line #46
The code starting on line #70 is as follows
Expand|Select|Wrap|Line Numbers
  1. ' Check for Date Created
  2.     If Me.txtDateCreated > "" Then
  3.         varWhere = varWhere & "[DateCreated] #" & Format(Me.txtDateCreated, "DD/MM/YYYY") & "# AND "
  4.     End If
The
Expand|Select|Wrap|Line Numbers
  1. Format(Me.txtDateCreated, "DD/MM/YYYY")
Is wrong.
It needs to be
Expand|Select|Wrap|Line Numbers
  1. 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.
Jul 3 '13 #6
*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
Jul 3 '13 #7
zmbd
5,501 Expert Mod 4TB
same error yes... however, when you stepped thru the code, which line did the error occure on?
Jul 3 '13 #8
It stopped on the same line as before, line 32.
Jul 4 '13 #9
MikeTheBike
639 Expert 512MB
Hi

You could try putting an = sign in ie
Expand|Select|Wrap|Line Numbers
  1. [DateCreated] = #21/08/1991#
??

MTB
Jul 4 '13 #10
Thanks, that's cured it! Knew it would be something simple and probably obvious if you have the experience!

Cheers :)
Jul 4 '13 #11
zmbd
5,501 Expert Mod 4TB
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)
Jul 4 '13 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Magnus Andersson | last post by:
I really need some help with a Visual Basic problem. I am using Visual Basic .NET and want find some text with a certain style in an MS Office document. I have written something like: Dim oFind...
2
by: Arnold | last post by:
Hey there, I just put the following code: DoCmd.GoToRecord , , acLast in the On Open event of my form, which has 2 subforms. The following error message appears when the form now opens: ...
0
by: John Hunter | last post by:
I've recently had a nasty problem with the "Invalid reference to the property Form" error in subforms - nasty because it doesn't seem to consistently happen to all forms which contain the same...
1
by: Jordan Fee | last post by:
I'd like to perform some conditional formatting in the OnFormat Event of my Parent Report. This works fine, until my subreports are blank, then the invalid references start happening. I understand...
2
by: Giovanni Bassi | last post by:
Hello All, I have encountered a problem. I am using visual inheritance and my base form adds an event handler on Form Load using the AddHandler Keyword. The problem is that if the Event...
4
by: Bill Coan | last post by:
NOTE: This was posted earlier to vsnet.vstools.office under a different subject line but received no response. I'm having a problem automating Word's Find object from a .NET application, using...
9
by: CQ | last post by:
Hi everyone, I get the following error when compiling my code: error: invalid initialization of non-const reference of type 'Vertex&' from a temporary of type 'int' The implementation of...
6
by: Dave | last post by:
This is the first time I'm trying this in an ADP. I'm using Access 2003 and trying to change the Apptitle Property using the below: With Application.CurrentProject .Properties("AppTitle").Value...
3
by: MyWaterloo | last post by:
I am trying to open my purchase orders form and go to the last record. In the on open command I do: DoCmd.GoToRecord , , acLast Seems straight forward enough...but I keep getting this message...
5
by: Abest | last post by:
I am trying to get my form to autofill the values from the last record to the new record. In the form properites on the BeforeInsert, I used the following module I have been away from VB for...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.