Blank shows in combo when there is an excluded record

[Peter Stone]

Access 2003
Novice

The following query is on a combo on a subform.

The query displays perfectly correctly in Datasheet view, but on the
occasions when there is an excluded record in tblDest (e.g. with GeogID = 2),
a blank appears in the combo. The correct record is there, but I have to
scroll in the combo to view it.

How can I fix this? Do I need to modify the SQL, my db design, or the combo?

There are 4 tables shown on the grid: tblName; tjnNameDest; tblDest;
tjnGeogDest the two tables prefixed tjn are to create many-to-many
relationships.

SELECT tblDest.DestID, tblName.Name, tblName.NmTypeID, tjnDestGeog.GeogID
FROM (tblDest INNER JOIN tjnDestGeog ON tblDest.DestID = tjnDestGeog.DestID)
INNER JOIN (tblName INNER JOIN tjnDestNm ON tblName.NameID =
tjnDestNm.NameID) ON tblDest.DestID = tjnDestNm.DestID
WHERE (((tblName.NmTypeID)=1) AND ((tjnDestGeog.GeogID)>3 And
(tjnDestGeog.GeogID)<6))
ORDER BY tblName.Name;

Thanks

 

[KARL DEWEY]

Why are you using a combo in this fashion? Seems to me the query should be
for a subform.

As I know Access a combo is used to display a list that you wish to pick
from to insert into a field of another table/record and then display after
the pick.

 

[Peter Stone]

Thanks

The combo IS on a subform and inserts the DestID into another link table (a
self join). Everything works OK. The datasheet of the query displays exactly
the records I want to fromin the combo. It's just that on about 25% of the
records in the query, there is an excluded record (e.g., GeogID = 2 (see the
Where part of the statement)).

In these cases, the combo displays blank instead of showing the current
record. If I then scroll in the combo the current record shows. I want it to
display correctly instead of having to scroll.