Setting recordsource property in VBA Access 2007

Discussion in 'Microsoft Access VBA Modules' started by KMcHenry, Feb 24, 2010.

  1. KMcHenry

    KMcHenry Guest

    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, Feb 24, 2010
    #1
    1. Advertisements

  2. 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, Feb 24, 2010
    #2
    1. Advertisements

  3. KMcHenry

    KMcHenry Guest

    "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, Feb 24, 2010
    #3
  4. 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]
     
    Marshall Barton, Feb 24, 2010
    #4
  5. KMcHenry

    KMcHenry Guest

    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]
    > .
    >
     
    KMcHenry, Feb 24, 2010
    #5
  6. 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.
     
    Marshall Barton, Feb 25, 2010
    #6
  7. KMcHenry

    KMcHenry Guest

    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.

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

    > .
    >
     
    KMcHenry, Feb 25, 2010
    #7
  8. 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/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.
    >
    >"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.

    >> .
    >>
     
    Marshall Barton, Feb 25, 2010
    #8
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Frank

    Problem with ADO datacontrol RecordSource property

    Frank, Aug 26, 2003, in forum: Microsoft Access VBA Modules
    Replies:
    0
    Views:
    201
    Frank
    Aug 26, 2003
  2. paul a via AccessMonster.com

    using @ (such as ) in recordsource property

    paul a via AccessMonster.com, Aug 26, 2005, in forum: Microsoft Access VBA Modules
    Replies:
    3
    Views:
    158
    Dirk Goldgar
    Aug 26, 2005
  3. Guest

    setting RecordSource vs. setting RecordSource and Filter

    Guest, Mar 28, 2006, in forum: Microsoft Access VBA Modules
    Replies:
    7
    Views:
    418
    Guest
    Mar 30, 2006
  4. Guest

    Can't change subform recordsource in Access 2007

    Guest, Jun 21, 2007, in forum: Microsoft Access VBA Modules
    Replies:
    0
    Views:
    214
    Guest
    Jun 21, 2007
  5. Ben

    Dynamically changing a report's recordsource property

    Ben, Feb 23, 2009, in forum: Microsoft Access VBA Modules
    Replies:
    6
    Views:
    3,797
    Marshall Barton
    Feb 24, 2009
Loading...

Share This Page