Can't return all records

  • Thread starter Thread starter BruceM
  • Start date Start date
B

BruceM

I have a database for keeping track of recommendations. Each recommendation
has four sections: Recommendation, Response, Follow-Up, and Final Approval.
Data entry is by means of a form.
Considering just the Response section, after completing a response the
employee who did so selects his or her name from a combo box. The combo box
row source is a query (qryResponseSignature) based on a table (tblEmployee)
that contains EmployeeName, etc., and a text field that is a path to a
network location at which is located a graphics file of the person's
signature. The reason for the link to the graphics file is that the report
of the Recommendation contains this facsimile signature. The combo box's
bound column is ResponseSignatureID, which is a Number field.
In order for the signature to appear on the report, I have made a query
(qryRecommendation) that includes tblRecommendation and the row source query
for the combo boxes. I have established a relationship between
ResponseSignatureID (the combo box record source in tblRecommemdation) and
the EmployeeID field from qryResponseSignature.
By basing the report on qryRecommendation I am able to have the facsimile
signature appear on the report. The way this happens, in case it matters,
is that there is on the report a hidden text box (txtResponseSignature)
bound to the text field that is the network path to the "signature". The
report's Print event contains:
Me.imgResponseSignature.Picture = Me.txtResponseSignature
(imgResponseSignature is an image control on the report).
This works fine as long as the response has been completed. However, I need
to see any recommendation, regardless of whether it is completed. If
ResponseSignatureID is blank, the record is not included in the query. I
need it to be included, but cannot figure out how.
FWIW, this situation is repeated for the Follow-Up and Final Approva
sections. I have left them out of my question since the solution to the
problem can be extended to those sections.
 
Dear Bruce:

I take it txtResponseSignature is in the query for the report's control
source.

Either use a LEFT JOIN to that table (allowing the query to provide
everything else even if the row in the signature table is missing) or make
the control a calculated one based on a DLOOKUP().

Tom Ellison
 
My eyes glazed over before finishing your detailed explanation and I ususally
complain about not enough information!

You have an Inner Join between the two tables. What you need is a Left Join
that will allow all records to show in one table even if there isn't a
matching record in another. I think that the problem might be in
qryRecommendation. Open it in design mode and double-click on the line
between the tables. If you hit the line JUST right, it should open a dialog
box. Check the second entry and run the query. If that isn't right, check the
third box. If that doesn't do it, it might be in a different query. Or I
could be completely wrong.
 
Thanks for the reply. txtResponseSignature is a text box bound to
Signature, which is a field in tblEmployee. Signature is also a field in
qryResponse (which is based on tblEmployee). More in the reply to the other
response to my question.
 
Didn't mean to cause glazing over. It's a rather unusual situation, which
tends to require additional explanation.
I tried a left join, which produced all of the records when I ran the query.
However, there was a problem with the report. When I tried to view an
incomplete record (one in which the response not completed) I received a
Type Mismatch error on the Me.imgResponseSignature.Picture =
Me.txtResponseSignature. Once your reply confirmed that left join was the
way to go (I had tried it, but abandoned that course when I got the error
message) I realized that a type mismatch error can be the result of a null
value, so I changed the statement to:

Me.imgResponseSignature.Picture = Nz(Me.txtResponseSignature)

Now all is well. I should have realized sooner that once I got the query to
return all of the records the problem was no longer in the query, but in the
code behond the report. Thanks for taking the time to make your way through
my explanation and then to reply.
 
Back
Top