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

KMcHenry wrote:

>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.

--
Marsh
MVP [MS Access]

"Marshall Barton" wrote:


>
>
> The code is simply:
>
> Me.RecordSource = "name of table/query or an SQL statement"
>
> Note the quotes.
>
> --
> Marsh
> MVP [MS Access]
> .
>

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?

KMcHenry wrote:
>"Marshall Barton" 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.

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.

--
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" wrote:

> KMcHenry wrote:
> >"Marshall Barton" 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.
>
> 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.
>
> --
> Marsh
> MVP [MS Access]
> .
>

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]


KMcHenry wrote:
>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" wrote:
>
>> KMcHenry wrote:
>> >"Marshall Barton" 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.

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/ac...814471033.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.

"Marshall Barton" wrote:

> 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]
>
>
> KMcHenry wrote:
> >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" wrote:
> >
> >> KMcHenry wrote:
> >> >"Marshall Barton" 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.
> .
>

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]


KMcHenry wrote:
>The test scenario I posted was in a clean database.
>
>Here's a bit from Office Online
>http://office.microsoft.com/en-us/ac...814471033.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.
>
>"Marshall Barton" wrote:
>
>> 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]
>>
>>
>> KMcHenry wrote:
>> >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" wrote:
>> >
>> >> KMcHenry wrote:
>> >> >"Marshall Barton" 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.
>> .
>>