Setting recordsource property in VBA Access 2007

[KMcHenry]

I'm trying to create a report in Access 2007. I cannot figure out how to set
the recordsource propery in code.

I either get #Name? if the property is not set at design or I get the
original dataset base on the value of recordsource at designed, not the value
that I set in the report_Open event.

Does anyone know what I need to do to be able to set the recordsource
property in code?

Thanks,

Kevin

 

[Marshall Barton]

I'm trying to create a report in Access 2007. I cannot figure out how to set
the recordsource propery in code.

I either get #Name? if the property is not set at design or I get the
original dataset base on the value of recordsource at designed, not the value
that I set in the report_Open event.

Does anyone know what I need to do to be able to set the recordsource
property in code?

The code is simply:

Me.RecordSource = "name of table/query or an SQL statement"

Note the quotes.

 

[KMcHenry]

:

The code is simply:

Me.RecordSource = "name of table/query or an SQL statement"

Note the quotes.

Thanks, but that doesn't work.

Report1
Option Compare Database

Private Sub Report_Open(Cancel As Integer)

Me.RecordSource = "SELECT Table1.* FROM Table1 WHERE ID = 2;"
End Sub

Module1
Option Compare Database
Dim rpt As Report

Public Function testreport()

Set rpt = New Report_Report1
rpt.Visible = True


End Function

Macro1
RunCode testreport()


When I run the macro I get

#Name? #Name? #Name?

 

[Marshall Barton]

Thanks, but that doesn't work.

Report1
Option Compare Database

Private Sub Report_Open(Cancel As Integer)

Me.RecordSource = "SELECT Table1.* FROM Table1 WHERE ID = 2;"
End Sub

Module1
Option Compare Database
Dim rpt As Report

Public Function testreport()
Set rpt = New Report_Report1
rpt.Visible = True
End Function

Macro1
RunCode testreport()

When I run the macro I get

#Name? #Name? #Name?

I tried the same kind of arrangement (A2003) and had no
problem.

Are you sure that the report text boxes are bound to fields
in the table and query? OTOH, I do not understand where the
#Name? comes from. If a report uses a control bound to a
non-existent record source field, I would expect the report
to prompt for a value for each of those names.

 

[KMcHenry]

I discovered the issue because of an existing application in 2003. When I
tried to run it in 2007, my reports no longer worked.

The code I posted is just a test scenario to isolate the issue. I'm sure
the controls are bound correctly since the reports works if the RecordSource
property is set in design mode.

 

[Marshall Barton]

My A2007 machine is disassembled so I can't test it there.
I suggest that you create a new A2007 database with just a
test data table and the rest of your posted test scenario to
see if it works in a clean db.

I really am concerned about you getting #Name? As I said
before, you should not get that in a report. Kind of makes
me wonder if something (the report?) is corrupted (I
seriously doubt that A2007 broke the way reports do things).

 

[KMcHenry]

Thanks again.

The test scenario I posted was in a clean database.

Here's a bit from Office Online
http://office.microsoft.com/en-us/access/HA011814471033.aspx

Access displays #Name? in a control when the name that you supplied as the
source of the control's value is not valid. You use the ControlSource
property to specify the source of the control's value. For example, you might
have misspelled the name, or the source might have been renamed or deleted.
You may also see #Name? in a control if you place an expression in the
control's ControlSource property and you insert a space before the equal sign
that starts the expression

I think the import thing to note is that I'm createing an instance of the
report in code.

My A2007 machine is disassembled so I can't test it there.
I suggest that you create a new A2007 database with just a
test data table and the rest of your posted test scenario to
see if it works in a clean db.

I really am concerned about you getting #Name? As I said
before, you should not get that in a report. Kind of makes
me wonder if something (the report?) is corrupted (I
seriously doubt that A2007 broke the way reports do things).
--
Marsh
MVP [MS Access]

I discovered the issue because of an existing application in 2003. When I
tried to run it in 2007, my reports no longer worked.

The code I posted is just a test scenario to isolate the issue. I'm sure
the controls are bound correctly since the reports works if the RecordSource
property is set in design mode.

.

 

[Marshall Barton]

In my experience, that article applies to forms, not
reports.

If the problem occurs in a new, clean db, then I am at a
loss. It sounds like you have already done a lot of
debugging to try to isolate the issue so I can't even think
of anything else to try. Sorry.
--
Marsh
MVP [MS Access]

The test scenario I posted was in a clean database.

Here's a bit from Office Online
http://office.microsoft.com/en-us/access/HA011814471033.aspx

Access displays #Name? in a control when the name that you supplied as the
source of the control's value is not valid. You use the ControlSource
property to specify the source of the control's value. For example, you might
have misspelled the name, or the source might have been renamed or deleted.
You may also see #Name? in a control if you place an expression in the
control's ControlSource property and you insert a space before the equal sign
that starts the expression

I think the import thing to note is that I'm createing an instance of the
report in code.

My A2007 machine is disassembled so I can't test it there.
I suggest that you create a new A2007 database with just a
test data table and the rest of your posted test scenario to
see if it works in a clean db.

I really am concerned about you getting #Name? As I said
before, you should not get that in a report. Kind of makes
me wonder if something (the report?) is corrupted (I
seriously doubt that A2007 broke the way reports do things).
--
Marsh
MVP [MS Access]

I discovered the issue because of an existing application in 2003. When I
tried to run it in 2007, my reports no longer worked.

The code I posted is just a test scenario to isolate the issue. I'm sure
the controls are bound correctly since the reports works if the RecordSource
property is set in design mode.

:

KMcHenry wrote:
:
The code is simply:

Me.RecordSource = "name of table/query or an SQL statement"

Note the quotes.


Thanks, but that doesn't work.

Report1
Option Compare Database

Private Sub Report_Open(Cancel As Integer)

Me.RecordSource = "SELECT Table1.* FROM Table1 WHERE ID = 2;"
End Sub

Module1
Option Compare Database
Dim rpt As Report

Public Function testreport()
Set rpt = New Report_Report1
rpt.Visible = True
End Function

Macro1
RunCode testreport()

When I run the macro I get

#Name? #Name? #Name?


I tried the same kind of arrangement (A2003) and had no
problem.

.