Run-Time Error 2465

Question

I'm getting a run-time 2465 error from Access 2016.  It says it cannot find the "label_17".  The section in bold and italicize is what Access highlights in yellow.  Please assist.  Here is the code:

Private Sub Report_Open(Cancel As Integer)
Dim SrcRs As DAO.Recordset
Set SrcRs = CurrentDb.OpenRecordset("Weekly_Value")

'Assign Columns
For i = 2 To 16
    Me.Controls("txt_" & i).ControlSource = ""
    Me.Controls("txt_" & i).Visible = False
    Me.Controls("TTL_" & i).Visible = False
    Me.Controls("label_" & i).Caption = "_"
    Me.Controls("label_" & i).Visible = False
DoEvents
Next
For i = 2 To SrcRs.Fields.Count - 1
Me.Controls("label_" & i).Caption = Right(SrcRs.Fields(i).Name, 6)
Me.Controls("label_" & i).Visible = True
DoEvents
Next
'Assign Fields
    For i = 0 To SrcRs.Fields.Count - 1
    Me.Controls("txt_" & i).ControlSource = CStr(SrcRs.Fields(i).Name)
    Me.Controls("txt_" & i).Visible = True
    If i > 0 Then
    TempVars.Add "fld_" & i, SrcRs.Fields(i).Name
    Me.Controls("TTL_" & i).Visible = True
    End If
    DoEvents
    Next
Me.RecordSource = "Weekly_Value"
'Assign Totals
End Sub

Answer 1

I'm getting a run-time error 2465. Access cannot find "label_17" in the expression.  Any assistance will be greatly appreciated.

Private Sub Report_Open(Cancel As Integer)
Dim SrcRs As DAO.Recordset
Set SrcRs = CurrentDb.OpenRecordset("Weekly_Value")

'Assign Columns
For i = 2 To 16
    Me.Controls("txt_" & i).ControlSource = ""
    Me.Controls("txt_" & i).Visible = False
    Me.Controls("TTL_" & i).Visible = False
    Me.Controls("label_" & i).Caption = "_"
    Me.Controls("label_" & i).Visible = False
DoEvents
Next
For i = 2 To SrcRs.Fields.Count - 1
Me.Controls("label_" & i).Caption = Right(SrcRs.Fields(i).Name, 6)
Me.Controls("label_" & i).Visible = True
DoEvents
Next
'Assign Fields
    For i = 0 To SrcRs.Fields.Count - 1
    Me.Controls("txt_" & i).ControlSource = CStr(SrcRs.Fields(i).Name)
    Me.Controls("txt_" & i).Visible = True
    If i > 0 Then
    TempVars.Add "fld_" & i, SrcRs.Fields(i).Name
    Me.Controls("TTL_" & i).Visible = True
    End If
    DoEvents
    Next
Me.RecordSource = "Weekly_Value"
'Assign Totals
End Sub

Answer 2

On which line?

You iteratate For i = 2 To 16 so I'll assume the issue is originating from the Me.Controls("label_" & i).Caption = Right(SrcRs.Fields(i).Name, 6) line of code.  This would therefore indicate that SrcRs.Fields.Count - 1 > 16.  So how many columns are in your table/query Weekly_Value?

---

Daniel Pineault, 2010-2016 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 3

Thank you for your response.  The line that is highlight is as follows:

Me.Controls("label_" & i).Caption = Right(SrcRs.Fields(i).Name, 6)

There are 17 columns.

Answer 4

Looks like this is a duplicate post of this thread.

Good luck!

 

Answer 5

Hi Chrissy,

And you're absolutely certain there is a label named "label_17" on your report, correct? Could there be a typo?

Answer 6

There is no label_17 on the report. 

Answer 7

There is no label_17 on the report. 

Ok, it explains the error. Now, what we need to find is the logic error causing it. For instance, you're using this in your loop:

For i = 2 To SrcRs.Fields.Count - 1

Have you verified the number of fields returned by SrcRs?

Just a thought...

Answer 8

No I have not.  What would be the best way to determine that?  The user will enter the dates they want to select, if I select a range that is within 16 weeks I do not get an error, however anything more than 16 weeks I get this run-time error.

Answer 9

You might be able to simply use a constant number: 16

If the user can select a date range less than 16 weeks, then you could add a test to, I assume, hide the unused columns.

For example:

For i = 2 To 16

   If SrcRs.Fields.Count >= i Then

      Me.Controls("label_" & i).Caption = Right(SrcRs.Fields(i).Name,6)

   Else

      Me.Controls("label_" & i).Visible = False

   End If

Next

(untested)

Hope it helps...

Answer 10

I will try it out and advise.  Yes with the monthly, quarterly and yearly I was able to hide the unused columns.

Answer 11

Hi Chrissy Anne,

I can see that you had created 2 threads for the same issue.

so I merge the both threads to avoid the duplication.

so did you try to implement the suggestion provided by the other community member?

if yes, let us know about the current status of your issue?

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].