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(,1),PROJ_EQ.Alternate) AS Unitcost, PROJ_ME.Alt_Code, PROJ_ME.ListPrice,, 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
  sfrmEditInfo.Visible = True
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft 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.

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?  
subforms do not have a rowsource property.

Subforms can take a sourceobject property.

this can be set to a query.

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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stephenlecomptejrAuthor Commented:
Thanks for all the replies!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.