This Record Set Is Not Updateable

[yrstruly]

My Access database has entered a confusing state, where some records have suddenly become non-updatable, despite no recent structural changes.


Here's what's happening:

• In the Plants table:
  
  • Go to Menu 5.1 at the top.
  • Select "Titan SG" (a cultivar).
  • Then click the Licence button in the bottom-left corner. Choose any or Sierra Gold.
  • The licence form opens, but doesn't allow updates for this and a few other records like it.
  
  
• In the Nursery Orders section (menu 3, 3,1) Choose any of the files in drop downs:
  
  • The same issue — some records suddenly can't be edited.

This was working before, and I havenÂ’t changed the relationships or form code in any way that IÂ’m aware of. No new joins or locks were added.
I tested the queries, it works in older db, but not in my current, b that i mean, it shows updateable in older db, but not in new.
Example is:

SELECT TblNursOrders.[Select], TblNursOrders.Nr, TblNursOrders.Anno, TblNursOrders.OrderDate, Plants_1.PlantType AS Fruit, TblNursOrders.PlantIDScion AS SCult, Plants_1.Ipno, Plants_1.Clone AS SClone, Plants_1.Phyto, IIf(        Not IsNull([Plants_1].[CommercialAgent]), [Plants_1].[CommercialAgent],
        IIf(
            Not IsNull([Plants_1].[Partnership]), [Plants_1].[Partnership],
            IIf(
                Not IsNull([Plants_1].[NucliusResponse]), [Plants_1].[NucliusResponse],
                IIf(
                    Not IsNull([Plants_1].[Origin]), [Plants_1].[Origin],
                    "O Gaats geen Agent"
                )
            )
        )
    ) AS Agent, TblNursOrders.PlantIDRoot AS RCult, Plants.Ipno, Plants.Clone AS RClone, Plants.Phyto, TblNursOrders.OrderAmount, TblNursOrders.OrderSize, TblNursOrders.OrderBy, TblNursOrders.Allocated, TblNursOrders.Contract, TblNursOrders.CommentNurs AS [Nurs Comment], TblNursOrders.rRL2Pay, TblNursOrders.sRL2Pay, TblNursOrders.Comment, TblNursOrders.Cerified, TblNursOrders.DeliveryNo, TblNursOrders.DeliveryDate, TblNursOrders.DeliveryQuantity, TblNursOrders.DeliverySizes, TblNursOrders.CertifiedType, TblNursOrders.SourceScion AS SSource, TblNursOrders.SourceRoot AS RSource, TblNursOrders.Nursery, TblNursOrders.NurseryConfirm AS Confirm, TblNursOrders.DeliveryNotePrinted, TblNursOrders.DeliveryType, TblNursOrders.Gebied, TblNursOrders.BevestigingsbriefGestuur, TblNursOrders.BevestigingsbriefOntvang, TblNursOrders.KontrakGestuur, TblNursOrders.KontrakOntvang, TblNursOrders.DepositobriefGestuur, TblNursOrders.DepositobriefOntvang, TblNursOrders.OrderID, TblNursOrders.CountOrder, TblNursOrders.TreePrice, TblNursOrders.PayableTo, Round(Nz(TblNursOrders.TreePrice,0) * Nz(TblNursOrders.Allocated,0), 2) AS TotalTreesPrice
FROM TblLicence AS TblLicence_1 RIGHT JOIN (((TblNursOrders INNER JOIN Plants ON TblNursOrders.PlantIDRoot = Plants.PlantID) INNER JOIN Plants AS Plants_1 ON TblNursOrders.PlantIDScion = Plants_1.PlantID) LEFT JOIN TblLicence ON Plants.PlantID = TblLicence.PlantID) ON TblLicence_1.PlantID = Plants_1.PlantID
WHERE TblNursOrders.Nr = [Forms]![frmNursOrder]![Nr]
    AND TblNursOrders.Anno = [Forms]![frmNursOrder]![Anno]
ORDER BY Plants_1.Clone, TblNursOrders.PlantIDRoot, TblNursOrders.OrderAmount DESC;

I checked if the tables are updateable and they are.
I have not changed Section 3's query lately. What could have caused this.
Login: Fritz, 11111.

DB https://drive.google.com/file/d/1U5N...ew?usp=sharing

[Micron]

It would be better if you just zipped a copy of your file and put it in a post rather than making helpers download from another site. Some won't. Some cannot.

[yrstruly]

It would be better if you just zipped a copy of your file and put it in a post rather than making helpers download from another site. Some won't. Some cannot.

I cant upload files.

[mike60smart]

You say in your instructions

In the Plants table
Go to record 5.1 at the top

Your current Plants table displays as shown in the attached.

How do you have a Record 5.1???

[Welshgasman]

It would be better if you just zipped a copy of your file and put it in a post rather than making helpers download from another site. Some won't. Some cannot.

Wouldn't upload anyway, it is 73MB

[yrstruly]

You say in your instructions

In the Plants table
Go to record 5.1 at the top

Your current Plants table displays as shown in the attached.

How do you have a Record 5.1???

Sorry, Menu i meant.

[mike60smart]

There is no Form named "Menu"???

[mike60smart]

Your Login Form has a Command Button which should open the Form named "Switchboard"

There is no Form named "Switchboard" ???
Edit *****
My apologies there is a Switchboard but unable to open it

[Micron]

Not sure why you can't upload here when you can upload to Google drive. Oh well, never mind.
WGM, it's 73MB when compacted and zipped? Then it has too much in it for a forum upload as you say. The usual fix is to import only what's needed into a new db and then post that instead.

EDIT - you mention 2 different db's. Is one not in a trusted location, because you're using a VBA function in the query (IIF)?
Maybe something here will help you figure it out. Up to you, but if you get a browser warning about an unsecure site, I would not worry about it given who owns it.
http://allenbrowne.com/ser-61.html

[mike60smart]

I have taken a quick look at the database and there are issues.

No relationships set in the BE file.

The Forms that I have looked at are dealing with Plants.

the Plants table has a Text Autonumber PK named PlantID and related tables with related tables linked on PlantID with a Text DataType FK

Tried to set relationships between these tables and get the following error message.

[moke123]

Cant get anywhere with your database. Freezing up and crashing.

[yrstruly]

I ran this code for frmLicence "SELECT DISTINCTROW TblLicence.*,
Plants.Cultivar,
Profile.Display
FROM
(TblLicence
INNER JOIN Profile ON TblLicence.Nr = Profile.Nr)
INNER JOIN Plants ON TblLicence.PlantID = Plants.PlantID;" it is updateable, i still need to bring in the calculation(Round(Nz(PP.[Royalty/Levy], 0) * 0.8, 2) AS IPOwnerShare,
Round(Nz(PP.[Royalty/Levy], 0) * 0.2, 2) AS SAPOShare).
I don't know why it does this, then it works fine, and then it stops working.

[Micron]

While be relationships are not mandatory, they are helpful. I have to wonder why you're using replication in this db? At least that's what those first 3 fields look like to me. You might have to fix your db schema, else you are likely going to continually struggle.

[yrstruly]

Please indicate of which replication you are refering to?

[mike60smart]

It looks like the field "sGUID" is a Replication Field