Error when attempting to set subforms recordsource programatically...

S

Staats

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
 
A

Allen Browne

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.

Reply to group, rather than allenbrowne at mvps dot org.

Staats said:
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
Click to expand...
 
B

Brendan Reynolds

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 said:
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
Click to expand...
 
Top