D
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));
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));