#Name? error in field values when subfrm linked to frm


Susan H

When I open my subfrm in datasheet view, I can see values in all of the fields. When I link my subform to my form, I can only see the value in zpreqryAddressMaxDate.AddressID, and "#Name?" in all of the other fields. Can anyone tell me why?

The subfrm is linked to the frm by UniqueID (subfrm contains tblAddress.UniqueID, frm contains tblYouth.UniqueID.) Relationships are set as 1 to Many where a youth in tblYouth can have many addresses in tblAddress, and UniqueID is a foreign key in tblAddress. tblAddress has a compound index of Unique ID and AddressID, and AddressID is the primary key.

Query 3 calls Query 2 calls Query 1

Query 1
SELECT tblAddress.UniqueID, Max(tblAddress.AdrBeginDate) AS MaxOfAdrBeginDate
FROM tblAddress
GROUP BY tblAddress.UniqueID
ORDER BY tblAddress.UniqueID;

Query 2
SELECT tblAddress.AddressID
FROM utlqryMaxAdrBeginDate LEFT JOIN tblAddress ON (utlqryMaxAdrBeginDate.MaxOfAdrBeginDate = tblAddress.AdrBeginDate) AND (utlqryMaxAdrBeginDate.UniqueID = tblAddress.UniqueID);

Query 3
SELECT tblAddress.UniqueID, tblAddress.AddressID, tblAddress.AdrBeginDate, tblAddress.AdrEndDate, tblAddress.Address1, tblAddress.Address2, tblAddress..City, tblAddress.State, tblAddress.ZIP, tblAddress.AddressUpdateDate, tblAddress.CountyJuris, tblAddress.CountyRes, tblAddress.RegionJuris, tblAddress.RegionRes, tblAddress.DistrictJuris, tblAddress.DistrictRes, tblAddress.ResidenceType, tblAddress.GroupHomeID, *
FROM tblAddress RIGHT JOIN zpreqryAddressMaxDate ON tblAddress.AddressID = zpreqryAddressMaxDate.AddressID
WHERE (((tblAddress.AddressID)=[zpreqryAddressMaxDate].[AddressID]));

finally, the fiels will simply be viewed by the user in the subform, no updating or deleting.

Thanks! Hope that's not too much explanation.

P.S. one add-on question: is there any way to include all of three queries (nested) in the SQL view of one query, rather than 3 separate queries that call each other?


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question