MS Access Run-time error 3999

Question

I've created an application that uses Linked Lists to Office 365 Sharepoint as a back end.

I was under the impression that this was designed in a way that Linked Lists should be able to handle themselves offline. But when I go offline and create a new "Job record" the ID field is created with a -1.

The Job List has a Lookup for Quote table on the ID field and the Quote List has a Lookup for the Job table on the ID field to create the relationship between the two.

But when I'm offline and create a new Job it gets an ID of -1 and I can't write that -1 to a new Quote under the Job. I get the following error

"Run-time error '3999'

You cannot reference rows created when you are disconnected from the server because this violates the lookup settings defined for this table or list. Please reconnect all tables with the server and try again"

I know when I go online the new Job with the ID of -1 will get a proper ID (The next available), is it possible to create new linked records while offline where any child records get updated when coming back online?

I'm using a DAO Recordset to create the new Quote under the Job

Dim db As DAO.Database
Dim rs As DAO.Recordset 
Dim strSQL as String

strSQL = "SELECT * FROM tblQuote" 
Set db = CurrentDB
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

With rs
    .AddNew
    !JobID = Me.ID
    .Update
    .Bookmark = .LastModified
    Me.QuoteID = !ID
End with

If I were online when I created the Job then went offline to create the Quote the system lets me Create the Quote and any new Quote Items. I can see all the new records in the tables but when I come back online to Sync, it does not recognize the new Quote as existing, and forces me to undo any changes so the new Quote and all the line items are discarded

Answer 1

I believe I figured this out.

It was because I had a lookup created in both directions. The Job had a Lookup to the QuoteID, and the Quote had a lookup to the JobID.

The Job lookup to the Quote was there because it wasn't a Form/subform and I needed a way to display the ID from the Job, and that was my first thought to do that.

What I needed to do to fix this is not even have a Column on the Job table to store the Quote, instead on the Job Form to have an unbound control that's populated at runtime displaying the QuoteID that has the matching JobID.

That way during Offline mode -1 ID values can be created, are linked properly, and then when going online the sync process updates them to proper ID's and they still remain linked.