I have an Access form with sub-report on it for preview. This way I have the print, save and email buttons "docked" to the bottom of the form window while scolling the report.

Form_Load includes code to set the subReport.SourceObject property, to show the selected report.

The Report_Load sub includes code to populate the labels' text (taken from a table) according to selected language.

rpt.Controls(rsContorlsTexts(0)).Caption = rsContorlsTexts(1)

Some of the textboxes also have text is inserted from the table, basically replacing the name of the text with it's value. For example: report's txtTitle.ControlSource is set to "="txtTitle " & [Title]" at design and the code replaces "txtTtile" with the text from the table.

with rpt
  .Controls(rsContorlsTexts(0)).ControlSource = Replace(.Controls(rsContorlsTexts(0)).ControlSource, rsContorlsTexts(0), rsContorlsTexts(1))
end with

I'm using textboxes because I need their CanGrow property which labels don't have.

After the code replaces the ControlSource of the first textbox, the code breaks with the error: 267:The expression you entered refers to an object that is closed or does not exist.

The same error raised also with unbounded textbox, so it's not the replace function:

.Controls(rsContorlsTexts(0)).ControlSource = "=""abc "" & [Title]"

Label's captions runs without errors. Same if I open the report directly - no errors.

Any ideas how to solve it before I give up on the docked buttons?

I have an Access form with sub-report on it for preview. This way I have the print, save and email buttons "docked" to the bottom of the form window while scolling the report.

Form_Load includes code to set the subReport.SourceObject property, to show the selected report.

The Report_Load sub includes code to populate the labels' text (taken from a table) according to selected language.

rpt.Controls(rsContorlsTexts(0)).Caption = rsContorlsTexts(1)

Some of the textboxes also have text is inserted from the table, basically replacing the name of the text with it's value. For example: report's txtTitle.ControlSource is set to "="txtTitle " & [Title]" at design and the code replaces "txtTtile" with the text from the table.

with rpt
  .Controls(rsContorlsTexts(0)).ControlSource = Replace(.Controls(rsContorlsTexts(0)).ControlSource, rsContorlsTexts(0), rsContorlsTexts(1))
end with

I'm using textboxes because I need their CanGrow property which labels don't have.

After the code replaces the ControlSource of the first textbox, the code breaks with the error: 267:The expression you entered refers to an object that is closed or does not exist.

The same error raised also with unbounded textbox, so it's not the replace function:

.Controls(rsContorlsTexts(0)).ControlSource = "=""abc "" & [Title]"

Label's captions runs without errors. Same if I open the report directly - no errors.

Any ideas how to solve it before I give up on the docked buttons?

• vba
• ms-access
• textbox
• report

1 Answer 1

SO requires enough code to recreate the problem. I can't recreate your issue with what you posted. However, have you tried stepping through the code and when the error occurs you will be able to see which line of code is causing the error? Also, if there is only one text box you're editing, why are you not naming the control and referring directly to it? Something like:

Set db = CurrentDb
Set rec = db.OpenRecordset("Select ReportName from tblReports where Rpt_Number = '" & Me.RptNumber & "'")

If rec.EOF = True Then
Else
    Me.ReportTitle.Text = rec("ReportName")
End If