Assign rowsource/ recordsource of subform in MIcrosoft Access 2003.

I have a main form/subform (both unbound) where within the main form a user enters a room number and in the subform it lists all the equipment that goes with it.
I keep trying the following code but get an error attempting...Run-time error 438 object doesn't support this property - with the highlight on Me!sfrmEditInfo.RowSource = sSQL - I've tried RecordSource and everything else under the sun - How come it is so hard to change the recordsource of a subform?

sSQL = "SELECT DISTINCTROW PROJ_EQ.Equip, PROJ_EQ.Room_Number, PROJ_DPT.Dept_TOC, PROJ_RM.Dept_Code, tblequiptype.TypeSortID, PROJ_ME.Type, PROJ_ME.ASE, PROJ_INF.[Project Information], PROJ_EQ.Quantity, PROJ_EQ.Ex_Quantity, [Rm_Quantity]*[Quantity] AS totqty, ([newqty]*[Unitcost]) AS TotCOST, [totqty]-[EX_QUANTITY] AS newqty, PROJ_EQ.Alternate, PROJ_ME.Name, Actual_Cost(nz(PROJ_ME.ListPrice,0),PROJ_ME.ReqQuotePrice,nz(PROJ_ME.discount,1),PROJ_EQ.Alternate) AS Unitcost, PROJ_ME.Alt_Code, PROJ_ME.ListPrice, PROJ_ME.discount, PROJ_EQ.ID, PROJ_EQ.Remarks"
  sSQL = sSQL & " FROM (PROJ_INF INNER JOIN (PROJ_DPT INNER JOIN PROJ_RM ON PROJ_DPT.Dept_Code = PROJ_RM.Dept_Code) ON PROJ_INF.[Project Information] = PROJ_DPT.Proj_infid) INNER JOIN (ALTSORT INNER JOIN (tblequiptype INNER JOIN (PROJ_ME INNER JOIN PROJ_EQ ON PROJ_ME.Code = PROJ_EQ.Equip) ON tblequiptype.Type = PROJ_ME.Type) ON ALTSORT.Alternate = PROJ_EQ.Alternate) ON PROJ_RM.Room_Number = PROJ_EQ.Room_Number"
  sSQL = sSQL & " WHERE (((PROJ_EQ.Equip) Like fEquipCode()) And ((PROJ_EQ.Room_Number) Like '" & lstRms.Column(0) & "' And ((PROJ_RM.Dept_Code) Like fDeptID()) And ((PROJ_ME.Type) Like fEquipType()) And ((PROJ_ME.ASE) Like fASEItems()) And ((PROJ_INF.[Project Information]) Like fProjectID()) And ((PROJ_EQ.Alternate) Like fAltID()))"
  sSQL = sSQL & " ORDER BY PROJ_EQ.Equip, PROJ_EQ.Room_Number, PROJ_DPT.Dept_TOC, tblequiptype.TypeSortID, PROJ_EQ.Alternate"
  Me!sfrmEditInfo.RowSource = sSQL
  Me!sfrmEditInfo.Requery
  sfrmEditInfo.Visible = True
  DoEvents
LVL 1
stephenlecomptejrAsked:
Who is Participating?
 
ldunscombeConnect With a Mentor Commented:
Provided your sql works you can set the record source of your subform as follows.

Note: It uses the name of your subform CONTROL....  NOT the name of the subform itself.

MainForm.NameOfSubFormCONTROL.Form.Recordsource
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
>  sSQL = sSQL & " WHERE (((PROJ_EQ.Equip) Like fEquipCode()) And ((
For starters, whenever you use a function like fEquipCode() {whatever exactly that is}, you'll want to refer to it outside of the double-quote marks.  
Otherwise, SQL will translate it to literal text.

sSQL = sSQL & " WHERE (((PROJ_EQ.Equip) Like " & fEquipCode()) & "  And (( ... "

Not to mention if you use LIKE there should probably be some wildcards somewheter, otherwise it defeats the purpose of using LIKE.

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Also, what's the reason why you have this in VBA, and not as a saved/pre-compiled query?  
0
 
Malik1947Connect With a Mentor Commented:
subforms do not have a rowsource property.

Subforms can take a sourceobject property.

this can be set to a query.

0
 
stephenlecomptejrAuthor Commented:
Thanks for all the replies!
0
All Courses

From novice to tech pro — start learning today.