Records "lost" when OLE added to RecordSource

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is my form's RecordSource (it works fine)
SELECT tblProfiles.txtProfileID, tblProfiles.Version, tblProfiles.Type, tblProfiles.Description, tblProfiles.SKUSCCITF, tblProfiles.UPCUCCEAN, tblProfiles.OriginDate, tblProfiles.ApprovedDate, tblProfiles.ActiveDate, tblProfiles.InactiveDate, tblProfiles.RevisedDate, tblProfiles.RevisionHistory, tblProfiles.AllergenComments FROM tblProfiles WHERE (((tblProfiles.Type)="CP"));

Here it is with an OLE object field added. Now when I open the form, none of the records show
SELECT tblProfiles.txtProfileID, tblProfiles.Version, tblProfiles.Type, tblProfiles.Description, tblProfiles.SKUSCCITF, tblProfiles.UPCUCCEAN, tblProfiles.OriginDate, tblProfiles.ApprovedDate, tblProfiles.ActiveDate, tblProfiles.InactiveDate, tblProfiles.RevisedDate, tblProfiles.RevisionHistory, tblProfiles.AllergenComments, tblCPPhysicalAttributes.OLECupLidImage FROM tblProfiles INNER JOIN tblCPPhysicalAttributes ON tblProfiles.txtProfileID=tblCPPhysicalAttributes.txtProfileID WHERE (((tblProfiles.Type)="CP"))

Why would adding the OLE field to the RecordSource do this? How can I resolve it

Thanks!
 
JohnLute said:
This is my form's RecordSource (it works fine):
SELECT tblProfiles.txtProfileID, tblProfiles.Version,
tblProfiles.Type, tblProfiles.Description, tblProfiles.SKUSCCITF,
tblProfiles.UPCUCCEAN, tblProfiles.OriginDate,
tblProfiles.ApprovedDate, tblProfiles.ActiveDate,
tblProfiles.InactiveDate, tblProfiles.RevisedDate,
tblProfiles.RevisionHistory, tblProfiles.AllergenComments FROM
tblProfiles WHERE (((tblProfiles.Type)="CP"));

Here it is with an OLE object field added. Now when I open the form,
none of the records show.
SELECT tblProfiles.txtProfileID, tblProfiles.Version,
tblProfiles.Type, tblProfiles.Description, tblProfiles.SKUSCCITF,
tblProfiles.UPCUCCEAN, tblProfiles.OriginDate,
tblProfiles.ApprovedDate, tblProfiles.ActiveDate,
tblProfiles.InactiveDate, tblProfiles.RevisedDate,
tblProfiles.RevisionHistory, tblProfiles.AllergenComments,
tblCPPhysicalAttributes.OLECupLidImage FROM tblProfiles INNER JOIN
tblCPPhysicalAttributes ON
tblProfiles.txtProfileID=tblCPPhysicalAttributes.txtProfileID WHERE
(((tblProfiles.Type)="CP"));

Why would adding the OLE field to the RecordSource do this? How can I
resolve it?

Thanks!
Click to expand...

I doubt that the problem has anything to do with the OLE field itself.
But you've changed the recordsource query from a single-table query to
one that inner joins two tables. That means that the query will only
return records that have a match in both tables on the join field
(txtProfileID) -- that's the definition of an inner join. Are you
saying that you have matching records in both tblProfiles and
tblCPPhysicalAttributes, and the query still doesn't return any records?
That would be a matter for concern. But if your tblCPPhysicalAttributes
is empty or contains no records that match the CP records in
tblProfiles, then what you're seeing is expected.

If you want to see all the records from tblProfiles with type "CP",
regardless of whether they have matching records in
tblCPPhysicalAttributes, then change "INNER JOIN" to "LEFT JOIN" in the
query SQL.
 
Back
Top