Query not showing records on form

Question

Folks, I have an issue getting records to show on a form, for some (newly coded) versions of the underlying query (qryStackAss) but not others (legacy coded). The records are always present in the underlying query that the form is based on, whether they show on the form (as they should) or not. I can switch (and remake) the query from a combo box that I have added new values to the legacy values in the value list. The query always specifies the same fields from the same two tables joined one-to-many; only the criteria change. There are no filters on the form. I can open a recordset on all versions of the query that are made (whichever value is selected in the combo box), and it reports the correct amount of records. The form is requeried after each selection in the combo box. However, the records only show for the legacy values in the combo box, not for the values I just added. This makes no sense to me! The application was originally developed in Access 97 and has been updated through versions to the current (for me) 2010 version. I’m not sure which version the form was made in, but could be 97. Any ideas?

A version of qryStackAss that returns 11 records when run and shows 11 records on the form:

SELECT ASSERTIONS.selected, ASSERTIONS.assertion, SUBTOPICASS.staSubtopicID, SUBTOPICASS.staLevel1, SUBTOPICASS.staLevel2, SUBTOPICASS.staLevel3, ASSERTIONS.asNameID, ASSERTIONS.assID, SUBTOPICASS.staGroupType, ASSERTIONS.assProjectID
FROM ASSERTIONS INNER JOIN SUBTOPICASS ON ASSERTIONS.assID = SUBTOPICASS.staAssID
WHERE (((SUBTOPICASS.staSubtopicID)=16) AND ((ASSERTIONS.asNameID)=1) AND ((SUBTOPICASS.staGroupType)=2))
ORDER BY SUBTOPICASS.staLevel1, SUBTOPICASS.staLevel2, SUBTOPICASS.staLevel3;

A version of qryStackAss that returns 1 record when run but shows zero on the form:

SELECT ASSERTIONS.selected, ASSERTIONS.assertion, SUBTOPICASS.staSubtopicID, SUBTOPICASS.staLevel1, SUBTOPICASS.staLevel2, SUBTOPICASS.staLevel3, ASSERTIONS.asNameID, ASSERTIONS.assID, SUBTOPICASS.staGroupType, ASSERTIONS.assProjectID
FROM ASSERTIONS INNER JOIN SUBTOPICASS ON ASSERTIONS.assID = SUBTOPICASS.staAssID
WHERE (((ASSERTIONS.selected)=True) AND ((SUBTOPICASS.staGroupType)=7 Or (SUBTOPICASS.staGroupType)=10 Or (SUBTOPICASS.staGroupType)=8))
ORDER BY SUBTOPICASS.staLevel1, SUBTOPICASS.staLevel2, SUBTOPICASS.staLevel3;

Answer 1

With Q2 selected in the Navigation Pane, click on Create > Form, and have Access create an auto-form. Does it display the record? I bet it does.

Then carefully compare properties and possibly code behind the form, and you will find the culprit.

---

-Tom. Microsoft Access MVP

Answer 2

Thanks, Tom. You were right. The query did show on a newly created form. The culprit turned out to be that the form not showing the query is actually a subform, and asNameID was the child field linked to a master field on the main form (where the combo box resides). Of course, the subform only showed records when the master field matched. Duh!

Answer 3

Great! Glad you found it and reported back to the community.

---

-Tom. Microsoft Access MVP