PC Review


Reply
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average.

Setting recordsource property in VBA Access 2007

 
 
KMcHenry
Guest
Posts: n/a
 
      24th Feb 2010
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
 
Reply With Quote
 
 
 
 
Marshall Barton
Guest
Posts: n/a
 
      24th Feb 2010
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]
 
Reply With Quote
 
 
 
 
KMcHenry
Guest
Posts: n/a
 
      24th Feb 2010


"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?







 
Reply With Quote
 
Marshall Barton
Guest
Posts: n/a
 
      24th Feb 2010
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]
 
Reply With Quote
 
KMcHenry
Guest
Posts: n/a
 
      24th Feb 2010
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]
> .
>

 
Reply With Quote
 
Marshall Barton
Guest
Posts: n/a
 
      25th Feb 2010
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.

 
Reply With Quote
 
KMcHenry
Guest
Posts: n/a
 
      25th Feb 2010
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.

> .
>

 
Reply With Quote
 
Marshall Barton
Guest
Posts: n/a
 
      25th Feb 2010
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.

>> .
>>


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Report's recordsource = form's recordsource Johnny Bright Microsoft Access Reports 4 27th Nov 2007 11:21 PM
setting RecordSource vs. setting RecordSource and Filter =?Utf-8?B?VCBSYXkgSHVtcGhyZXk=?= Microsoft Access VBA Modules 7 30th Mar 2006 04:03 AM
Problem with Reports recordsource= Forms recordsource Alex Microsoft Access Reports 5 13th Nov 2004 11:50 PM
Repot Recordsource same as Form Recordsource Alex Microsoft Access 1 13th Nov 2004 09:59 AM
Problem with Reports recordsource= Forms recordsource Alex Microsoft Access 0 13th Nov 2004 09:54 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:58 PM.