none
Run-time error '2465'

    Question

  • I have a form that is bound to two tables (Students and ServicePlans). There is a command button on the form to create a report showing data from the current records (i.e., the current service plan for the current student).

    I had to make some structural changes in the tables.  Prior to those changes, everything worked perfectly. However, the report does not gets its data directly from the tables but from a query that assembles the neccesary data in one place— and that query is still working properly. The report also still works when opened directly (except of course it shows all records, which defeats its purpose)

    When I press the command button I get the error "Run-time error '2465' ...can't find the field '|1' referred to in your expression"

    the code behind the button is:

    Private Sub CmdPrint_Click()
    Dim strWhere As String
    If Me.Dirty Then    'Save any edits.
            Me.Dirty = False
        End If
    
    If Me.NewRecord Then 'Check there is a record to print
            MsgBox "Select a record to print"
        Else
            strWhere = "[PlanID] = " & Me.[PlanID]
            DoCmd.Close
            DoCmd.OpenReport "Contract", acViewPreview, , strWhere
        End If
        
    End Sub

    I'm guessing " field '|1' " must refer to [PlanID], since there is no other field in the code. However, the field does exist both in the form and the report (and the query).

    1. Is there some problem with the way I have referenced [PlanID]?
    2. If so, is there some obvious reason why it worked before and not now?
    3. If not, is there some other explanation for this error message?

    (I checked the error message in the help menu and found nothing. A google search revealed numerous threads, but all seemed to be either unresolved or not connected with this situation)

    Thanks!


    —nick

    Tuesday, May 01, 2012 4:36 PM

Answers

  • Nick -

    OK...we know the issue is with that line of code.  The reason your ideas didn't work is that at this point in the code, you are just trying to build the WHERE clause BEFORE you open the report.  So it makes sense that it was bombing on that code you mentioned.

    The next question I would ask is - is it bombing in the code where I build strWhere or when I am opening the report.  One way to troubleshoot this is to try

    strWhere = "PlanID = " & 6  (obviously 6 would have to be a valid planID...use something valid from your tables.

    If the code doesn't work here, then you know the issue is in the report - when it tries to set the filter, it cannot. So you need to troubleshoot there.   You might try

    strWhere = "Reports!Contact!PlanID=" & me.PlanID or something like that.

    If the code does work here, then you know that the issue is that your form code cannot find Me.PlanID on the form.  In that case you need to check on the form and make sure there is still a text box or somethign called PlanID and that you can get to it (can you even do MsgBox me.[planID] and get a value?  What if you remove the brackets? I hate brackets and avoid at all cost).

    Last q if this doesn't work - what kind of structure change did you make? Did it affect PlanID at all?

    Hope this is helpful. check back and let us know what you find!


    Joy Eakins CDD, Inc.

    • Proposed as answer by JoyinKS Tuesday, May 01, 2012 5:50 PM
    • Marked as answer by Nick Vittum Tuesday, May 01, 2012 6:55 PM
    Tuesday, May 01, 2012 5:50 PM
  • Exactly which line generates the error?
    the line strWhere = "[PlanID] = " & Me.[PlanID] generates the error.

    Since the error is on that line, the problem has nothing to do with the report, or the query the report is based on. It has to do with the form.

    Maybe there is no field or control on the form named PlanID, or there is a control named PlanID but not bound to the PlanID field, yet there is a PlanID field in the form's recordsource, or PlanID appears more than once in the select statement of the recordsource.

    • Marked as answer by Nick Vittum Tuesday, May 01, 2012 6:54 PM
    Tuesday, May 01, 2012 6:36 PM

All replies

  • Hi Nick -

    If you want to know if this is the problem or not, comment out the line with PlanId and see if your code still functions correctly.  If it does work, then you have found your problem and can tackle it from there.

    Without looking at it in the context of the error, it's hard to know for sure if that is the problem, but that would help narrow it down.  Test and post your results. then we can try to help you some more.


    Joy Eakins CDD, Inc.

    Tuesday, May 01, 2012 4:41 PM
  • Does the query, by any chance, include the field PlanID more than once in the select statement?

    Tuesday, May 01, 2012 4:54 PM
  • Alphonse: thanks, I double-checked— but no, PlanID appears only once in the SELECT statement

    Joy: I followed your suggestion and commented out the  strWhere = "[PlanID] = " & Me.[PlanID] line.

    That caused the command button to work, but as I would expect, the report opens all records and so is useless. But as you suggested, this seems to verify that the error is in that line of code.

    I tried instead referencing PlanID this way:

    strWhere = Reports!Contract!PlanID = Me.PLanID

    That resulted in "Run-time error 2451: the report name'Contract' ... is mispelled or refers to a report that isn't open or doesn't exist."    Well, yes, it isn't open because the code doesn't tell it to open until the next line....? But it worked before.... I'm baffled.

    I tried moving the OpenReport line to before the  "strWhere = "[PlanID] = " & Me.[PlanID]" line. That opened the report with all records, and also gave me an error message (which I forgot to copy).



    —nick

    Tuesday, May 01, 2012 5:25 PM
  • Nick -

    OK...we know the issue is with that line of code.  The reason your ideas didn't work is that at this point in the code, you are just trying to build the WHERE clause BEFORE you open the report.  So it makes sense that it was bombing on that code you mentioned.

    The next question I would ask is - is it bombing in the code where I build strWhere or when I am opening the report.  One way to troubleshoot this is to try

    strWhere = "PlanID = " & 6  (obviously 6 would have to be a valid planID...use something valid from your tables.

    If the code doesn't work here, then you know the issue is in the report - when it tries to set the filter, it cannot. So you need to troubleshoot there.   You might try

    strWhere = "Reports!Contact!PlanID=" & me.PlanID or something like that.

    If the code does work here, then you know that the issue is that your form code cannot find Me.PlanID on the form.  In that case you need to check on the form and make sure there is still a text box or somethign called PlanID and that you can get to it (can you even do MsgBox me.[planID] and get a value?  What if you remove the brackets? I hate brackets and avoid at all cost).

    Last q if this doesn't work - what kind of structure change did you make? Did it affect PlanID at all?

    Hope this is helpful. check back and let us know what you find!


    Joy Eakins CDD, Inc.

    • Proposed as answer by JoyinKS Tuesday, May 01, 2012 5:50 PM
    • Marked as answer by Nick Vittum Tuesday, May 01, 2012 6:55 PM
    Tuesday, May 01, 2012 5:50 PM
  • Exactly which line generates the error?

    Have you tried a decompile and compact/repair?

    Please post the SQL of the query.

    Set a breakpoint on the openreport line and confirm the contents of strWhere.

    PlanID is a numeric field, isn't it?
    • Edited by Alphonse G Tuesday, May 01, 2012 5:56 PM
    Tuesday, May 01, 2012 5:55 PM
  • Thanks, Alphonse —

    Exactly which line generates the error?

    the line strWhere = "[PlanID] = " & Me.[PlanID] generates the error.

    Have you tried a decompile and compact/repair?

    Yes, I have tried this.

    Please post the SQL of the query.

    I’ve posted it below

    Set a breakpoint on the openreport line and confirm the contents of strWhere.

    Okay, now my ignorance is flashing: I don’t know what “Set a breakpoint” means

    PlanID is a numeric field, isn't it?

    Yes it is

    SELECT ServicePlans.PlanID, ServicePlans.StudentID, ServicePlans.[Active?], TblPayor.payor, ServicePlans.SchoolID, [School names query].Schoolname, ServicePlans.approval, ServicePlans.NEKLS_StuID, ServicePlans.Intake_date, ServicePlans.end_date, [Student name lookup query].Student, [Student name lookup query].Parent_Grdn, Students.Address1, Students.Address2, Students.Town, Students.State, Students.Zip, Students.Phone1, Students.phone2, Students.DOB, DateDiff("yyyy",[dob],Now()) AS Age, ServicePlans.Grade, ServicePlans.hrs_wk, ServicePlans.Rate, ServicePlans.mileage_approved, ServicePlans.mileage_rate, ServicePlans.[prep?], ServicePlans.prep_rate, ServicePlans.Subjects, Goals.goal
    
    FROM TblPayor INNER JOIN (([Student name lookup query] INNER JOIN Students ON [Student name lookup query].StudentID = Students.StudentID) INNER JOIN (Goals RIGHT JOIN ([School names query] INNER JOIN ServicePlans ON [School names query].SchoolID = ServicePlans.SchoolID) ON Goals.GoalID = ServicePlans.GoalID) ON Students.StudentID = ServicePlans.StudentID) ON TblPayor.code = ServicePlans.payor
    
    WHERE (((ServicePlans.[Active?])=True));


    —nick


    Tuesday, May 01, 2012 6:20 PM
  • Exactly which line generates the error?
    the line strWhere = "[PlanID] = " & Me.[PlanID] generates the error.

    Since the error is on that line, the problem has nothing to do with the report, or the query the report is based on. It has to do with the form.

    Maybe there is no field or control on the form named PlanID, or there is a control named PlanID but not bound to the PlanID field, yet there is a PlanID field in the form's recordsource, or PlanID appears more than once in the select statement of the recordsource.

    • Marked as answer by Nick Vittum Tuesday, May 01, 2012 6:54 PM
    Tuesday, May 01, 2012 6:36 PM
  • Eureka!!!!

    You weren't precisely right, but you made me realize what was wrong! When I made my changes, I'd put the info pertaining to the ServicePlans table into a subform. But the code was in the parent form, and it wasn't recognizing Me.[PlanID] from there. Perhaps I should repair the reference, but — just to be sure that was the problem — I put another invisible instance of it in the parent form.

    And it works.  Yea!

    Thanks so much to both of you!


    —nick

    Tuesday, May 01, 2012 6:54 PM
  • Glad you got it.

    You can simply reference the subform control with me!<subformcontrol>.Form!PlanID

    Tuesday, May 01, 2012 6:59 PM
  • Great!  Glad you got it!


    Joy Eakins CDD, Inc.

    Tuesday, May 01, 2012 7:00 PM