MS Access 2013 with Azure backend, can't handle error

[lguzman]

Thank you for reading,

I have a front-end in MS Access 2013 linked to a SQL in SharePoint.

I have a table "tbl_Pipeline" in SQL Server connected to the front-end with read-write permission. When a new record is created it works well. The problem comes when I want to quit the process without committing the record.

The fields are:
tbl_Pipeline.ID (auto-number)
tbl_Pipeline.date (default date())
tbl_Pipeline.Description (short text)
tbl_Pipeline.amount (floating)

If the user tries to leave the process (e.g. going to a previous record), it displays the following msg:

OBDC--call failed.
[Microsoft][SQL Server Native Client 11.0]Cannot insert the value NULL into column 'Description', table 'XXXX.tbl_Pipeline';column does not allow nulls.INSERT fails.(#515)[Microsoft][SQL Server Native Client 11.0][SQL Server]The statement has been terminated.(#3621).

I assume the errors is happening because the remote table is somehow receiving data (default values), then when the user leaves without completing the entry the NULL error comes up.

I want to catch the error before it pops up so that I can handle it, but since it's not on the MS Access side I don't "see" it (errors.count gives zero).

I'll appreciate all suggestions because I've exhausted my ideas.

Thank you
Leonel

 

[Frothingslosh]

That's because when you try to quit for form and the record is created but not saved, Access will attempt to save the record. (In a nutshell, bound forms change data in real-time, so it's not 'somehow receiving the data', but rather working as intended.

In this case, you're falling afoul of the Allow Null constraints in to underlying table. What you'll want to do in this situation is to undo any changes as part of the close routine. Personally, I would normally place a check in the form's On Close event that checks if the form is dirty, and if so, ask the user if they want to save their changes. If they say yes, it's safest to just cancel the close and let them do the save themselves. If they say no, then run the Undo command before allowing the close to complete.

My understanding is that if you're going through SharePoint, then you're limited to macros, and I don't normally use them (I use VBA wherever possible for better control and adaptability), so I won't be of much use there. I do know that all the commands I mentioned are available as macros, so it can certainly be done.

 

[lguzman]

Thank you very much.

After much research I found a solution, but I'm a newbie here and the site doesn't let me post the link.

Best