Forum Discussion

deansmith19's avatar
deansmith19
Copper Contributor
Nov 04, 2021

Only existing records showing in form

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.

access
  • Karl_Donaubauer's avatar
    Karl_Donaubauer
    MVP
    Nov 04, 2021

    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

     

     

     

    • deansmith19's avatar
      deansmith19
      Copper Contributor
      Nov 10, 2021
      Thanks for your assistance Karl. Your suggestion worked. Yes, I meant Allow Additions.

      Much appreciated.
      Dean.

Resources