DCount based on Query

[Design by Sue]

At least that is what I am thinking is needed. Here's the problem:

On my main form, there are 3 combo boxes, Company, Shape and Size. User
selected one for each. On the subform is a search button that runs the query
and displays the results. All works but I would like to add a message that
states no matching criteria if that is true. At this point, if there is no
matches, nothing happens which leaves the user unsure.

A DCount based on the query would make sense, but is there a way to write
the SQL to reference the existing query, or do I have to rewrite the query
code into the SQL (dang that sound confusing, I hope you understand). I am
not an expert at SQL and the query is rather complex, so if I can reference
the actual query in the code that would be much easier.

FYI here is the Query Code so you can see why rewriting it (for me at least
) would be a challenge

Thanks in advance for any assistance

Sue




SELECT CompanyPartTbl.CompanyName, InfoTbl.Type, InfoTbl.Size,
InfoTbl.Shape, PartSuffixTbl.PartNumber, PartSuffixTbl.Suffix,
IIf([Line]="STOCK",[Shelf],[Line]) AS WhereIsIt,
Format(PartSuffixTbl!PartNumber,"00000") & "-" & [Suffix] AS Expr1,
EmployeeTbl.EmpName, PartSuffixTbl.Move
FROM (InfoTbl INNER JOIN CompanyPartTbl ON
InfoTbl.PartNumber=CompanyPartTbl.PartNumber) INNER JOIN (EmployeeTbl INNER
JOIN PartSuffixTbl ON EmployeeTbl.EmpID=PartSuffixTbl.Mechanic) ON
InfoTbl.PartNumber=PartSuffixTbl.PartNumber
WHERE (((CompanyPartTbl.CompanyName)=Forms!LocationFrm!CompanyChoice) And
((InfoTbl.Size)=Forms!LocationFrm!SizeChoice) And
((InfoTbl.Shape)=Forms!LocationFrm!ShapeChoice));

 

[C Hayes]

I'm assume your button click event is where the magic happens on filtering
the form

if so

add a last line to your button click event

Dim rs As Recordset
Set rs = Me.RecordsetClone
if rs.RecordCount<1 then
msgbox "there are no records to display for this search"
End if
set rs = nothing