Only existing records showing in form

%3CLINGO-SUB%20id%3D%22lingo-sub-2925181%22%20slang%3D%22en-US%22%3EOnly%20existing%20records%20showing%20in%20form%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2925181%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI%20have%20a%20form%20where%20the%20Data%20Entry%20option%20is%20set%20to%20Yes.%20Unfortunately%20though%2C%20the%20form%20only%20shows%20existing%20records%20and%20doesn't%20allow%20the%20option%20to%20add%20new%20ones.%26nbsp%3B%20The%20query%20on%20which%20the%20form%20is%20based%20is%20as%20follows%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESELECT%20dbo_PersonFormalAddress.FormalAddressAbbreviated%2C%20dbo_PersonTitle.TitleShortForm%2C%20dbo_Person.FirstName%2C%20dbo_Person.MiddleName%2C%20dbo_Person.LastName%2C%20dbo_Person.Suffix%2C%20dbo_Person.PreferredNameOrNickName%2C%20dbo_PersonType.PersonTypeID%2C%20dbo_Clergy.DateOfBirth%2C%20dbo_Clergy.DateOfOrdinationToPriesthood%2C%20dbo_Clergy.DateOfDeath%2C%20dbo_Clergy.HomeAddressLine1%2C%20dbo_Clergy.HomeAddressLine2%2C%20dbo_Clergy.HomeAddressSuburb%2C%20dbo_Clergy.HomeAddressPostcode%2C%20dbo_StateTerritory.StateTerritoryCode%20AS%20HomeAddressStateTerritory%2C%20dbo_Clergy.%5BPostalAddressSameAsHomeAddress%3F%5D%2C%20dbo_Clergy.PostalAddressLine1%2C%20dbo_Clergy.PostalAddressLine2%2C%20dbo_Clergy.PostalAddressSuburb%2C%20dbo_Clergy.PostalAddressPostcode%2C%20dbo_StateTerritory.StateTerritoryCode%20AS%20PostalAddressStateTerritory%2C%20dbo_Clergy.PersonalEmailAddress%2C%20dbo_Clergy.WorkEmailAddress%2C%20dbo_Clergy.MobileNumber%2C%20dbo_Clergy.HomePhoneNumber%2C%20dbo_Clergy.WorkPhoneNumber%2C%20dbo_Clergy.Allergies%2C%20dbo_CountryRegion.Name%20AS%20HomeAddressCountry%2C%20dbo_CountryRegion.Name%20AS%20PostalAddressCountry%2C%20dbo_Clergy.NextOfKinName%2C%20dbo_Clergy.NextOfKinPhone%2C%20dbo_Clergy.NextOfKinAddress%2C%20%5Bdbo_Clergy%20Query%20IncardinatedTo%5D.Name%20AS%20IncardinatedTo%2C%20dbo_Clergy.RetirementDate%2C%20dbo_Person.*%3CBR%20%2F%3EFROM%20(dbo_CountryRegion%20RIGHT%20JOIN%20(dbo_StateTerritory%20RIGHT%20JOIN%20(dbo_PersonType%20INNER%20JOIN%20(dbo_PersonTitle%20RIGHT%20JOIN%20(dbo_PersonFormalAddress%20RIGHT%20JOIN%20(dbo_Person%20INNER%20JOIN%20dbo_Clergy%20ON%20dbo_Person.PersonID%20%3D%20dbo_Clergy.Person)%20ON%20dbo_PersonFormalAddress.FormalAddressID%20%3D%20dbo_Person.PersonFormalAddress)%20ON%20dbo_PersonTitle.TitleID%20%3D%20dbo_Person.Title)%20ON%20dbo_PersonType.PersonTypeID%20%3D%20dbo_Person.PersonType)%20ON%20(dbo_StateTerritory.StateTerritoryID%20%3D%20dbo_Clergy.PostalAddressStateTerritory)%20AND%20(dbo_StateTerritory.StateTerritoryID%20%3D%20dbo_Clergy.HomeAddressStateTerritory))%20ON%20(dbo_CountryRegion.CountryRegionCode%20%3D%20dbo_Clergy.PostalAddressCountry)%20AND%20(dbo_CountryRegion.CountryRegionCode%20%3D%20dbo_Clergy.HomeAddressCountry))%20INNER%20JOIN%20%5Bdbo_Clergy%20Query%20IncardinatedTo%5D%20ON%20dbo_Clergy.ClergyID%20%3D%20%5Bdbo_Clergy%20Query%20IncardinatedTo%5D.ClergyID%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20anyone%20able%20to%20let%20me%20know%20what%20I%20have%20done%20wrong%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2925181%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eaccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2926183%22%20slang%3D%22en-US%22%3ERe%3A%20Only%20existing%20records%20showing%20in%20form%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2926183%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20write%3C%2FP%3E%0A%3CP%3E%26gt%3B%20Data%20Entry%20option%20is%20set%20to%20Yes%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20would%20mean%20that%20in%20this%20form%20you%20only%20can%20enter%20new%20records%2C%20not%20see%20any%20existing.%20Therefore%20I%20presume%20that%20you%20mean%20the%20Allow%20Additions%20property.%20Correct%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20that%20case%20control%20if%20the%20query%20is%20updatable.%20Queries%20with%20multiple%20joins%20often%20are%20not%2C%20e.g.%20because%20the%20join%20fields%20are%20not%20indexed%20enough%20restrictively.%20So%2C%20in%20case%20your%20query%20is%20not%20updatable%2C%20then%20in%20the%20query%20editor%20delete%20one%20table%20and%20control%20if%20this%20changes%20the%20updatability.%20If%20not%2C%20delete%20the%20next%20table%20etc.%20until%20you%20find%20the%20guilty%20join.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EServus%20%3CBR%20%2F%3EKarl%20%3CBR%20%2F%3E*********%20%3CBR%20%2F%3EAccess-Entwickler-Konferenz%3A%20%3CA%20class%3D%22moz-txt-link-freetext%22%20href%3D%22https%3A%2F%2Fwww.donkarl.com%2F%3FAEK%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.donkarl.com%2F%3FAEK%3C%2FA%3E%20%3CBR%20%2F%3EAccess%20DevCon%3A%20%3CA%20class%3D%22moz-txt-link-freetext%22%20href%3D%22http%3A%2F%2FAccessDevCon.com%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttp%3A%2F%2FAccessDevCon.com%3C%2FA%3E%20%3CBR%20%2F%3EAccess%20FAQ%3A%20%3CA%20class%3D%22moz-txt-link-freetext%22%20href%3D%22https%3A%2F%2Fwww.donkarl.com%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.donkarl.com%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi

I have a form where the Data Entry option is set to Yes. Unfortunately though, the form only shows existing records and doesn't allow the option to add new ones.  The query on which the form is based is as follows:

 

SELECT dbo_PersonFormalAddress.FormalAddressAbbreviated, dbo_PersonTitle.TitleShortForm, dbo_Person.FirstName, dbo_Person.MiddleName, dbo_Person.LastName, dbo_Person.Suffix, dbo_Person.PreferredNameOrNickName, dbo_PersonType.PersonTypeID, dbo_Clergy.DateOfBirth, dbo_Clergy.DateOfOrdinationToPriesthood, dbo_Clergy.DateOfDeath, dbo_Clergy.HomeAddressLine1, dbo_Clergy.HomeAddressLine2, dbo_Clergy.HomeAddressSuburb, dbo_Clergy.HomeAddressPostcode, dbo_StateTerritory.StateTerritoryCode AS HomeAddressStateTerritory, dbo_Clergy.[PostalAddressSameAsHomeAddress?], dbo_Clergy.PostalAddressLine1, dbo_Clergy.PostalAddressLine2, dbo_Clergy.PostalAddressSuburb, dbo_Clergy.PostalAddressPostcode, dbo_StateTerritory.StateTerritoryCode AS PostalAddressStateTerritory, dbo_Clergy.PersonalEmailAddress, dbo_Clergy.WorkEmailAddress, dbo_Clergy.MobileNumber, dbo_Clergy.HomePhoneNumber, dbo_Clergy.WorkPhoneNumber, dbo_Clergy.Allergies, dbo_CountryRegion.Name AS HomeAddressCountry, dbo_CountryRegion.Name AS PostalAddressCountry, dbo_Clergy.NextOfKinName, dbo_Clergy.NextOfKinPhone, dbo_Clergy.NextOfKinAddress, [dbo_Clergy Query IncardinatedTo].Name AS IncardinatedTo, dbo_Clergy.RetirementDate, dbo_Person.*
FROM (dbo_CountryRegion RIGHT JOIN (dbo_StateTerritory RIGHT JOIN (dbo_PersonType INNER JOIN (dbo_PersonTitle RIGHT JOIN (dbo_PersonFormalAddress RIGHT JOIN (dbo_Person INNER JOIN dbo_Clergy ON dbo_Person.PersonID = dbo_Clergy.Person) ON dbo_PersonFormalAddress.FormalAddressID = dbo_Person.PersonFormalAddress) ON dbo_PersonTitle.TitleID = dbo_Person.Title) ON dbo_PersonType.PersonTypeID = dbo_Person.PersonType) ON (dbo_StateTerritory.StateTerritoryID = dbo_Clergy.PostalAddressStateTerritory) AND (dbo_StateTerritory.StateTerritoryID = dbo_Clergy.HomeAddressStateTerritory)) ON (dbo_CountryRegion.CountryRegionCode = dbo_Clergy.PostalAddressCountry) AND (dbo_CountryRegion.CountryRegionCode = dbo_Clergy.HomeAddressCountry)) INNER JOIN [dbo_Clergy Query IncardinatedTo] ON dbo_Clergy.ClergyID = [dbo_Clergy Query IncardinatedTo].ClergyID;

 

Is anyone able to let me know what I have done wrong?

 

Thanks.

2 Replies

Hi,

 

You write

> Data Entry option is set to Yes

 

This would mean that in this form you only can enter new records, not see any existing. Therefore I presume that you mean the Allow Additions property. Correct?

 

In that case control if the query is updatable. Queries with multiple joins often are not, e.g. because the join fields are not indexed enough restrictively. So, in case your query is not updatable, then in the query editor delete one table and control if this changes the updatability. If not, delete the next table etc. until you find the guilty join.

 

Servus
Karl
*********
Access-Entwickler-Konferenz: https://www.donkarl.com/?AEK
Access DevCon: http://AccessDevCon.com
Access FAQ: https://www.donkarl.com

 

 

 

Thanks for your assistance Karl. Your suggestion worked. Yes, I meant Allow Additions.

Much appreciated.
Dean.