field returns a value if at least one matching record is found

[Guest]

I had posted this question in the Forms thread, but that was probably the
wrong place.

TblPartNumInfo
TblDefectRpt

My query for PartNumInfo brings together lots of different tables including
customers, contact info, status of jobs. A feature I need to add is a warning
if at least one defect report exists for a given part number. It would be
shown on the form as a conditional format of the text box where the part
number is shown.

So I would like to add a field to my query that would return some value
(like the part#ID) if at least one Defect Report exists for a part#. I
created a separate Select Query & joined it to my main Query on the Part#ID
field. Works great, but now the main query becomes uneditable.

Tried using domain aggregate directly on form, which works but makes the
form very sluggish, as it is a long list of part numbers. Any better way to
do this? A subquery? Haven't tried one of those yet & don't know how.

Thanks in advance!

 

[Guest]

Like your name. Check out my tag line.

If you are talking about forms, the best way would be a Defect Report
subform or subreport. That way the Defect would be on the form, yet the data
on the main form should remain editable.

 

[Guest]

Jerry- Thanks; that idea would work. Although it is important for the users
of this app to be able to quickly scan the list of Part#'s to see which one
has a defect. Putting this info on a subform would mean they would need to
click on each record before finding out.