Blindsided by SharePoint

[Chalkie42]

I have recently migrated a customer's back-end data to Sharepoint lists on their 365 account. Good news, everything works as normal or at least I though it did. My customer has now reported an issue which I have traced to the following:

• When the SharePoint lists become disconnected (such as when the internet connection is lost) and a new appointment is made in the appointments list, Access recognises the disconnected state and allocates a negative number to the autonumber field which I use as the 'Appointment ID'.
• Once connection is re-established, Sharepoint changes these negative numbers to positive ones during the sync process.

As the tables were designed some years ago, I didn't forsee that using the Appointment ID woud become a problem. The issue arises if the user also writes appoinment notes to the 'Notes' table/list. This is because the AppointmentID is recorded to identify the appointment to which the notes refer. So - after re-syncing the Appointment ID fiels in the Appointment List is updated but the Appointment ID in the notes list is not. Result - loss of integrity.


So I'm thinking that I can re-write my forms to create my own Appointment ID but that risks a user using the same identifier while disconnected so that's a non-starter. What I need is a way to update the Notes table after every sync to keep the Appointment IDs in tandem. Does anyone have any words of wisdom?

 

[NauticalGent]

When I was faced with a similar issue, I “forced” integrity by writing generic information into the sub forms. It was a hack, but it worked.

A bit of unsolicited advice: run away from SharePoint as fast as you can. MS has abandoned Access developers as far as Sharepoint goes. I have since moved to SQL Server and life has never been better...