I have a query from which I create a recordset which I then use to build an email (requesting payments be sent). If the email is confirmed sent, I use rs.edit / .update in VBA to mark the needed changes which will then remove the record from the request queue. This has been working well for quite some time. For a new project, I had to revise the database to allow for multiple addresses (one of which is marked primary) which is included in the recordset. However, in doing so, the recordset is now Read-Only and therefore cannot be updated.
I looked at Allen Browne's "Why is my query read-only?" (http://allenbrowne.com/ser-61.html), but I cannot identify the source of the problem. I've done a workaround by creating a new query just to allow for the update, but I'd like to learn what is the source of the issue, so that I can avoid it in the future.
Working/Editable Query's SQL:
Read-Only Query's SQL:
The difference is the JOIN "tblCountryRegions ON tblSubjectAddresses.RegionID_FK = tblCountryRegions.RegionID) INNER JOIN tblCountries ON tblSubjectAddresses.CountryID_FK = tblCountries.CountryID) ON tblSubjects.SubjectID = tblSubjectAddresses.TravelerID_FK" If I remove this, it becomes editable again. I just don't know why. And yes, SubjectID and TravelerID_FK are the one-to-many fields in the Relationship manager (I just didn't bother to rename TravelerID_FK to SubjectID_FK.)
I looked at Allen Browne's "Why is my query read-only?" (http://allenbrowne.com/ser-61.html), but I cannot identify the source of the problem. I've done a workaround by creating a new query just to allow for the update, but I'd like to learn what is the source of the issue, so that I can avoid it in the future.
Working/Editable Query's SQL:
Code:
SELECT tblSubjTravelerProfiles.SubjID_FK, tblStudies.Nickname, tblSubjectBookings.SubjBookingID, tblSubjTravelerProfiles.FirstName, tblSubjTravelerProfiles.LastName, tblSubjTravelerProfiles.HomeAddress, tblSubjTravelerProfiles.City, tblSubjTravelerProfiles.PostalCode, tblCountryRegions.DisplayedRegion, tblCountries.CountryAbbreviation, tblSubjTravelerProfiles.EmailAddress, tblBookedItems.ConfNumber, tblItinReimburements.ReimbItemID, tblItinReimburements.TravelItemID_FK, tblItinReimburements.DateOfExp, tblItinReimburements.Description, tblItinReimburements.Units, tblItinReimburements.UnitCost, [Units]*[UnitCost] AS Total, tblItinReimburements.Requested, tblItinReimburements.Sent
FROM tblStudies INNER JOIN (((tblSubjTravelerProfiles INNER JOIN tblCountries ON tblSubjTravelerProfiles.CountryID_FK = tblCountries.CountryID) INNER JOIN tblCountryRegions ON tblSubjTravelerProfiles.RegionID_FK = tblCountryRegions.RegionID) INNER JOIN (((tblStudyVisitSchedule INNER JOIN tblSubjectBookings ON tblStudyVisitSchedule.StudyVisitID = tblSubjectBookings.StudyVisitID_FK) INNER JOIN tblBookedItems ON tblSubjectBookings.SubjBookingID = tblBookedItems.SubjBookingID_FK) INNER JOIN tblItinReimburements ON tblBookedItems.TravelItemID = tblItinReimburements.TravelItemID_FK) ON tblSubjTravelerProfiles.TravelerID = tblSubjectBookings.TravelerID_FK) ON tblStudies.StudyID = tblStudyVisitSchedule.StudyID_FK
WHERE (((tblSubjTravelerProfiles.SubjID_FK)=[Reports]![rptUnwrittenChecks]![txtSubjectID]) AND ((tblItinReimburements.DateOfExp)<=Now()) AND ((tblItinReimburements.UnitCost)<>0) AND ((tblItinReimburements.Sent) Is Null))
ORDER BY tblItinReimburements.DateOfExp, tblItinReimburements.Description;
Read-Only Query's SQL:
Code:
SELECT tblSubjTravelerProfiles.SubjID_FK, tblStudies.Nickname, tblSubjectBookings.SubjBookingID, tblSubjTravelerProfiles.FirstName, tblSubjTravelerProfiles.LastName, tblSubjectAddresses.Primary, [Address1] & IIf([Address2]<>"","; " & [Address2],"") AS PrimAddress, tblSubjectAddresses.City, tblSubjectAddresses.PostCode, tblCountryRegions.DisplayedRegion, tblCountries.CountryAbbreviation, tblSubjTravelerProfiles.EmailAddress, tblBookedItems.ConfNumber, tblItinReimburements.ReimbItemID, tblItinReimburements.TravelItemID_FK, tblItinReimburements.DateOfExp, tblItinReimburements.Description, tblItinReimburements.Units, tblItinReimburements.UnitCost, [Units]*[UnitCost] AS Total, tblItinReimburements.Requested, tblItinReimburements.Sent
FROM ((tblSubjects INNER JOIN tblSubjTravelerProfiles ON tblSubjects.SubjectID = tblSubjTravelerProfiles.SubjID_FK) INNER JOIN (((tblStudyVisitSchedule INNER JOIN tblStudies ON tblStudyVisitSchedule.StudyID_FK = tblStudies.StudyID) INNER JOIN tblSubjectBookings ON tblStudyVisitSchedule.StudyVisitID = tblSubjectBookings.StudyVisitID_FK) INNER JOIN (tblBookedItems INNER JOIN tblItinReimburements ON tblBookedItems.TravelItemID = tblItinReimburements.TravelItemID_FK) ON tblSubjectBookings.SubjBookingID = tblBookedItems.SubjBookingID_FK) ON tblSubjTravelerProfiles.TravelerID = tblSubjectBookings.TravelerID_FK) INNER JOIN ((tblSubjectAddresses INNER JOIN tblCountryRegions ON tblSubjectAddresses.RegionID_FK = tblCountryRegions.RegionID) INNER JOIN tblCountries ON tblSubjectAddresses.CountryID_FK = tblCountries.CountryID) ON tblSubjects.SubjectID = tblSubjectAddresses.TravelerID_FK
WHERE (((tblSubjTravelerProfiles.SubjID_FK)=[Reports]![rptUnwrittenChecks]![txtSubjectID]) AND ((tblSubjectAddresses.Primary)=True) AND ((tblItinReimburements.DateOfExp)<=Now()) AND ((tblItinReimburements.UnitCost)<>0) AND ((tblItinReimburements.Sent) Is Null))
ORDER BY tblItinReimburements.DateOfExp, tblItinReimburements.Description;
The difference is the JOIN "tblCountryRegions ON tblSubjectAddresses.RegionID_FK = tblCountryRegions.RegionID) INNER JOIN tblCountries ON tblSubjectAddresses.CountryID_FK = tblCountries.CountryID) ON tblSubjects.SubjectID = tblSubjectAddresses.TravelerID_FK" If I remove this, it becomes editable again. I just don't know why. And yes, SubjectID and TravelerID_FK are the one-to-many fields in the Relationship manager (I just didn't bother to rename TravelerID_FK to SubjectID_FK.)