Welcome to SPN

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

Sign Up Now!

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
    >
     

Share This Page