I have a subform that uses a query to pull in quotation info.
The query only pulls in the info relevant to a revision number that the user
has selected on another form. In this way the user can call up the first
([RevNo] = 0) submission, the second ([RevNo] = 1) and so on.

What I'm trying to do is display on the subform the highest revision number
relevant to that quotation so that the user can see if he's working with the
latest info.

I've tried using a Totals query with Max and I've tried DMax as shown below.
The problem is that it always returns the highest revision present in the
table tblProposalDetails rather than the highest revision number for the
quotation in question.

The SQL is:-

SELECT tblProposalDetails.ItemNo, tblProducts.Code, tblProducts.ProdName,
tblProposalDetails.UnitPrice, tblProposalDetails.Quantity,
tblProposalMaster.ProjectID, tblProposalDetails.PropSectID,
tblProposalDetails.ProductID, tblProposalDetails.ID,
tblProposalDetails.ItemRevNo, tblProposalMaster.RevNo,
DMax("ItemRevNo","tblProposalDetails") AS MaxRevNo
FROM (tblProposalMaster INNER JOIN tblProposalSections ON
tblProposalMaster.ProjectID = tblProposalSections.ProjectID) INNER JOIN
((tblSupply RIGHT JOIN (tblProducts INNER JOIN tblProposalDetails ON
tblProducts.ProductID = tblProposalDetails.ProductID) ON tblSupply.SupplyID =
tblProposalDetails.Supply) LEFT JOIN tblProductPriceValidity ON
tblProducts.PriceValidity = tblProductPriceValidity.PriceValidID) ON
tblProposalSections.PropSectID = tblProposalDetails.PropSectID
WHERE (((tblProposalDetails.ItemRevNo)=[RevNo]))
ORDER BY tblProposalDetails.ItemNo, tblProposalDetails.ID;

I can see that DMax("ItemRevNo","tblProposalDetails") isn't helping. How can
I restrict what it's looking at?

Any help gratefully received. Thanks.

Andy