Subform not displaying data correctly

Posted on 2012-09-17
Last Modified: 2012-09-22
I have a subform based on a query that displays correctly when I open just the a form, however, When I attach the subform to the Mainform, those fields that are combos does not display the data correctly.

The Mainform is unbound and has no child/parent links.

What is causing the data not to be displayed when I place within the Mainform?  Having one of my brain freezes again.  Under an extreme deadline to fix this issue.

My original database corrupted and I had to recreate some of the forms.


Question by:Karen Schaefer
    LVL 29

    Expert Comment

    Can you post the control source for the combo box?

    Author Comment

    by:Karen Schaefer
    the control source or the row source

    Here is the row source

    SELECT tblOrgListing_lkup.OrgCtr, tblOrgListing_lkup.ManagerID, [LastName] & ", " & [FirstName] AS ManagerName, tblOrgListing_lkup.Org, tblOrgListing_lkup.UnitChief FROM tblOrgListing_lkup INNER JOIN tblEmployee ON tblOrgListing_lkup.ManagerID = tblEmployee.BEMS WHERE (((tblOrgListing_lkup.DoNotUse)=0)) ORDER BY [LastName] & ", " & [FirstName];

    Here is the query behind the subform
    SELECT tblCEDPublic_Temp.BEMSID, tblEmployee.LastName, tblEmployee.FirstName, tblEmployee.Mgr_OrgNo, BName(IIf(IsNull([REPORTS_TO_ID]),[Sponsor],[REPORTS_TO_ID])) AS MgrBEMS, IIf(Not IsNull([sponsor]),"X","") AS ContractLabor, tblCEDPublic_Temp.dept AS Insite_Dept, tblOrgListing_lkup.Org AS Current_Org, IIf(IsNull([org]) Or IsNull([Dept]) Or [Dept]<>[Org],"X","") AS OrgChanged, tblCEDPublic_Temp.BUILDING AS Insite_BldgNo, tblCEDPublic_Temp.STATUS, tblCEDPublic_Temp.STATUS_DATE, tblEmployee.StatCtr, IIf([Status]="H" Or ([status]="L"),"X","") AS Remove_Active, tblEmployee.LOA_StartDate, tblEmployee.LOA_EndDate, tblEmployee.XfrIn, tblEmployee.ServiceDt, tblEmployee.XfrOut, tblEmployee.Bldg_Primary AS Current_Bldg, IIf(IsNull([BUILDING]) Or IsNull([tblEmployee].[Bldg_Primary]),"X",IIf(Left([tblEmployee].[Bldg_Primary],Len([tblEmployee].[Bldg_Primary])-2)=[BUILDING],"","X")) AS Bldg_Chng, tblCEDPublic_Temp.ROOM AS Insite_Loc, tblEmployee.Col_Cub_Primary AS Current_Loc, IIf(IsNull([tblEmployee].[Col_Cub_Primary]) Or IsNull([room]) Or [room]<>[tblEmployee].[Col_Cub_Primary],"X","") AS Location_Chg, tblEmployee.Active, tblCEDPublic_Temp.FFMS AS Insite_MS, tblEmployee.MailStop AS Current_MS, IIf(IsNull([tblEmployee].[MailStop]) Or IsNull([FFMS]) Or [FFMS]<>[tblEmployee].[MailStop],"X","") AS MS_Chg, tblEmployee.NT_UserId, Date() AS LastEditDate, GetUserName() AS ModifiedBy
    FROM ((tblEmployee LEFT JOIN tblOrgListing_lkup ON tblEmployee.Mgr_OrgNo = tblOrgListing_lkup.OrgCtr) INNER JOIN tblCEDPublic_Temp ON tblEmployee.BEMS = tblCEDPublic_Temp.BEMSID) LEFT JOIN tblEmpStatus_lkup ON tblEmployee.StatCtr = tblEmpStatus_lkup.StatCtr
    WHERE (((IIf(IsNull([org]) Or IsNull([Dept]) Or [Dept]<>[Org],"X",""))="x") AND ((tblEmployee.Active)=-1)) OR (((IIf(IsNull([BUILDING]) Or IsNull([tblEmployee].[Bldg_Primary]),"X",IIf(Left([tblEmployee].[Bldg_Primary],Len([tblEmployee].[Bldg_Primary])-2)=[BUILDING],"","X")))="x")) OR (((IIf(IsNull([tblEmployee].[Col_Cub_Primary]) Or IsNull([room]) Or [room]<>[tblEmployee].[Col_Cub_Primary],"X",""))="x")) OR (((IIf(IsNull([tblEmployee].[MailStop]) Or IsNull([FFMS]) Or [FFMS]<>[tblEmployee].[MailStop],"X",""))="X"));

    Open in new window

    See attached for pictureProblem screenshot of issue
    LVL 29

    Expert Comment

    You're right, I meant RowSource.  However, I also misunderstood when you mentioned combo.  I thought you meant  a combo box on the form.  It looks like you are referring to a datasheet with fields that are LookUp fields in the table itself.  That being the case, I'm not going to be of much help.  Let's see if another expert can give you assistance.
    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    <<I have a subform based on a query that displays correctly when I open just the a form, however, When I attach the subform to the Mainform, those fields that are combos does not display the data correctly.>>

      Is this a continious sub-form?  If yes, then you need to make sure that all the controls are bound.  If they are not, they will not display the correct data (they will be the same for every row).


    Accepted Solution

    Thanks, for the suggestions, however, it was not a continuous subform, but datasheet,

    found the solution on my own - turns out I missed a couple of lookup fields in my tables.  They were preventing the data from being accessed.  I also, had my column count & format incorrect.

    Author Closing Comment

    by:Karen Schaefer
    see last comment - found solution elsewhere

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now