Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

Error when attempting to set subforms recordsource programatically...

Discussion in 'Information Technology' started by Staats, Jul 28, 2006.

  1. Staats

    Staats
    Expand Collapse
    Guest

    Good Morning! I'm having problems setting the recordsource of a
    subform. The subform is not linked to the main form - actually, the
    main form has no recordset. The main form has controls to manipulate
    the subform. The subform is veiwed as a datasheet. Here is the code
    that I am using:

    Dim strSqlStmt As String
    Dim frmSiteReport As Form
    Dim frmSiteReportSub As Form

    strSqlStmt = "SELECT tblAWAtrWIm.ATR, tblAWAtrWIm.DIR,
    tblAWAtrWIm.Dist, " & _
    "tblAWAtrWIm.CO, tblAWAtrWIm.RTE, tblAWAtrWIm.LOG, tblAWAtrWIm.FC, " &
    _
    "tblAWAtrWIm.Lanes, tblAWAtrWIm.Type, tblAWAtrWIm.PROGRAM,
    tblAWAtrWIm.Location, " & _
    "tblAWAtrWIm.Comments, tblMntWorkOrders.WorkOrder,
    tblMntWorkOrders.dateinitial, " & _
    "tblMntWorkOrders.dateVisit, tblMntWorkOrders.dateOfficial, " & _
    "tblMntWorkOrders.Contract, tblMntWorkOrders.dateCompleted,
    tblMntStatus.StatusText, " & _
    "TblTMCounties.Region06, TblTMCounties.Region08 FROM (TblTMCounties
    RIGHT JOIN " & _
    "(tblMntStatus RIGHT JOIN (tblAWAtrWIm LEFT JOIN tblMntWorkOrders ON "
    & _
    "tblAWAtrWIm.ATR = tblMntWorkOrders.Site) ON tblMntStatus.Status = " &
    _
    "tblMntWorkOrders.Status) ON TblTMCounties.Abreviation =
    tblAWAtrWIm.CO) " & _
    "WHERE ( TblTMCounties.Region06 = 1 ) AND ( tblMntWorkOrders.contract
    = '06' " & _
    "OR tblMntWorkOrders.contract is NULL) AND ( tblAWAtrWim.Type =
    'Down' OR " & _
    "tblAWAtrWim.Type = 'out' OR tblAWAtrWim.Type = 'VOL' OR
    tblAWAtrWim.Type = " & _
    "'LEN' OR tblAWAtrWim.Type = 'CLS' OR tblAWAtrWim.Type = 'WIM' ) ORDER
    BY " & _
    "tblAWAtrWIm.Dist , tblAWAtrWIm.CO, tblAWAtrWIm.RTE, tblAWAtrWIm.LOG"

    DoCmd.OpenForm "frmMntSiteReportMain", acNormal, , acFormEdit,
    acWindowNormal

    Set frmSiteReport = Forms!frmmntsitereportmain.Form
    frmSiteReport.SetFocus
    frmSiteReport![frmMntSiteReport].LinkChildFields = ""
    frmSiteReport![frmMntSiteReport].LinkMasterFields = ""

    With frmSiteReport![frmMntSiteReport]
    Debug.Print .Name
    ..RecordSource = strsqlstmt

    End With

    Even using a string and not a variable, I cannot set the recordsource.
    The SQL is valid - it works using a standard query... And I can set the
    recordsouce of another form using the sqlcode above. The error code is
    428 - Run time error - object doesn't support this method or property.

    Correct me if I'm wrong, but does that mean the subform does not
    support setting the recordsource? The debug.print statement was just to
    see if the sub form supported any methods, and it does. The error is on
    the .recordsource statement...

    Thanks in advance for the help!
    ~Garrett
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    There is a difference between the subform control and the form in the
    subform contorl.

    The subform control has no RecordSource. It has a SourceObject (the name of
    the form it contains.)

    Use the Form property of the subform control to refer to the form it
    contains:
    Forms!frmmntsitereportmain![frmMntSiteReport].Form.RecordSource =
    strSqlStmt

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Staats" <gstaats@gmail.com> wrote in message
    news:1151069857.382770.80300@g10g2000cwb.googlegroups.com...
    > Good Morning! I'm having problems setting the recordsource of a
    > subform. The subform is not linked to the main form - actually, the
    > main form has no recordset. The main form has controls to manipulate
    > the subform. The subform is veiwed as a datasheet. Here is the code
    > that I am using:
    >
    > Dim strSqlStmt As String
    > Dim frmSiteReport As Form
    > Dim frmSiteReportSub As Form
    >
    > strSqlStmt = "SELECT tblAWAtrWIm.ATR, tblAWAtrWIm.DIR,
    > tblAWAtrWIm.Dist, " & _
    > "tblAWAtrWIm.CO, tblAWAtrWIm.RTE, tblAWAtrWIm.LOG, tblAWAtrWIm.FC, " &
    > _
    > "tblAWAtrWIm.Lanes, tblAWAtrWIm.Type, tblAWAtrWIm.PROGRAM,
    > tblAWAtrWIm.Location, " & _
    > "tblAWAtrWIm.Comments, tblMntWorkOrders.WorkOrder,
    > tblMntWorkOrders.dateinitial, " & _
    > "tblMntWorkOrders.dateVisit, tblMntWorkOrders.dateOfficial, " & _
    > "tblMntWorkOrders.Contract, tblMntWorkOrders.dateCompleted,
    > tblMntStatus.StatusText, " & _
    > "TblTMCounties.Region06, TblTMCounties.Region08 FROM (TblTMCounties
    > RIGHT JOIN " & _
    > "(tblMntStatus RIGHT JOIN (tblAWAtrWIm LEFT JOIN tblMntWorkOrders ON "
    > & _
    > "tblAWAtrWIm.ATR = tblMntWorkOrders.Site) ON tblMntStatus.Status = " &
    > _
    > "tblMntWorkOrders.Status) ON TblTMCounties.Abreviation =
    > tblAWAtrWIm.CO) " & _
    > "WHERE ( TblTMCounties.Region06 = 1 ) AND ( tblMntWorkOrders.contract
    > = '06' " & _
    > "OR tblMntWorkOrders.contract is NULL) AND ( tblAWAtrWim.Type =
    > 'Down' OR " & _
    > "tblAWAtrWim.Type = 'out' OR tblAWAtrWim.Type = 'VOL' OR
    > tblAWAtrWim.Type = " & _
    > "'LEN' OR tblAWAtrWim.Type = 'CLS' OR tblAWAtrWim.Type = 'WIM' ) ORDER
    > BY " & _
    > "tblAWAtrWIm.Dist , tblAWAtrWIm.CO, tblAWAtrWIm.RTE, tblAWAtrWIm.LOG"
    >
    > DoCmd.OpenForm "frmMntSiteReportMain", acNormal, , acFormEdit,
    > acWindowNormal
    >
    > Set frmSiteReport = Forms!frmmntsitereportmain.Form
    > frmSiteReport.SetFocus
    > frmSiteReport![frmMntSiteReport].LinkChildFields = ""
    > frmSiteReport![frmMntSiteReport].LinkMasterFields = ""
    >
    > With frmSiteReport![frmMntSiteReport]
    > Debug.Print .Name
    > .RecordSource = strsqlstmt
    >
    > End With
    >
    > Even using a string and not a variable, I cannot set the recordsource.
    > The SQL is valid - it works using a standard query... And I can set the
    > recordsouce of another form using the sqlcode above. The error code is
    > 428 - Run time error - object doesn't support this method or property.
    >
    > Correct me if I'm wrong, but does that mean the subform does not
    > support setting the recordsource? The debug.print statement was just to
    > see if the sub form supported any methods, and it does. The error is on
    > the .recordsource statement...
    >
    > Thanks in advance for the help!
    > ~Garrett
     
  4. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    Try changing 'With frmSiteReport![frmMntSiteReport]' to 'With
    frmSiteReport![frmMntSiteReport].Form'

    It looks to me like you're getting a reference to the subform *control*
    instead of to the form contained in the control.

    --
    Brendan Reynolds
    Access MVP


    "Staats" <gstaats@gmail.com> wrote in message
    news:1151069857.382770.80300@g10g2000cwb.googlegroups.com...
    > Good Morning! I'm having problems setting the recordsource of a
    > subform. The subform is not linked to the main form - actually, the
    > main form has no recordset. The main form has controls to manipulate
    > the subform. The subform is veiwed as a datasheet. Here is the code
    > that I am using:
    >
    > Dim strSqlStmt As String
    > Dim frmSiteReport As Form
    > Dim frmSiteReportSub As Form
    >
    > strSqlStmt = "SELECT tblAWAtrWIm.ATR, tblAWAtrWIm.DIR,
    > tblAWAtrWIm.Dist, " & _
    > "tblAWAtrWIm.CO, tblAWAtrWIm.RTE, tblAWAtrWIm.LOG, tblAWAtrWIm.FC, " &
    > _
    > "tblAWAtrWIm.Lanes, tblAWAtrWIm.Type, tblAWAtrWIm.PROGRAM,
    > tblAWAtrWIm.Location, " & _
    > "tblAWAtrWIm.Comments, tblMntWorkOrders.WorkOrder,
    > tblMntWorkOrders.dateinitial, " & _
    > "tblMntWorkOrders.dateVisit, tblMntWorkOrders.dateOfficial, " & _
    > "tblMntWorkOrders.Contract, tblMntWorkOrders.dateCompleted,
    > tblMntStatus.StatusText, " & _
    > "TblTMCounties.Region06, TblTMCounties.Region08 FROM (TblTMCounties
    > RIGHT JOIN " & _
    > "(tblMntStatus RIGHT JOIN (tblAWAtrWIm LEFT JOIN tblMntWorkOrders ON "
    > & _
    > "tblAWAtrWIm.ATR = tblMntWorkOrders.Site) ON tblMntStatus.Status = " &
    > _
    > "tblMntWorkOrders.Status) ON TblTMCounties.Abreviation =
    > tblAWAtrWIm.CO) " & _
    > "WHERE ( TblTMCounties.Region06 = 1 ) AND ( tblMntWorkOrders.contract
    > = '06' " & _
    > "OR tblMntWorkOrders.contract is NULL) AND ( tblAWAtrWim.Type =
    > 'Down' OR " & _
    > "tblAWAtrWim.Type = 'out' OR tblAWAtrWim.Type = 'VOL' OR
    > tblAWAtrWim.Type = " & _
    > "'LEN' OR tblAWAtrWim.Type = 'CLS' OR tblAWAtrWim.Type = 'WIM' ) ORDER
    > BY " & _
    > "tblAWAtrWIm.Dist , tblAWAtrWIm.CO, tblAWAtrWIm.RTE, tblAWAtrWIm.LOG"
    >
    > DoCmd.OpenForm "frmMntSiteReportMain", acNormal, , acFormEdit,
    > acWindowNormal
    >
    > Set frmSiteReport = Forms!frmmntsitereportmain.Form
    > frmSiteReport.SetFocus
    > frmSiteReport![frmMntSiteReport].LinkChildFields = ""
    > frmSiteReport![frmMntSiteReport].LinkMasterFields = ""
    >
    > With frmSiteReport![frmMntSiteReport]
    > Debug.Print .Name
    > .RecordSource = strsqlstmt
    >
    > End With
    >
    > Even using a string and not a variable, I cannot set the recordsource.
    > The SQL is valid - it works using a standard query... And I can set the
    > recordsouce of another form using the sqlcode above. The error code is
    > 428 - Run time error - object doesn't support this method or property.
    >
    > Correct me if I'm wrong, but does that mean the subform does not
    > support setting the recordsource? The debug.print statement was just to
    > see if the sub form supported any methods, and it does. The error is on
    > the .recordsource statement...
    >
    > Thanks in advance for the help!
    > ~Garrett
    >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page