[AccessD] Access and SQL Error Adding Records on Continuous form

[David Emerson]

I finally found someone else who had the same problem:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8089729d-402a-43cc-
ae22-f3273058e298/ado-insert-causes-errors-in-previous-record?forum=accessde
v

I tried his workaround but it didn't work for me.

Perhaps Shane Groff may have some insight as to whether Microsoft is aware
of it?  Being able to use a stored procedure to filter the records for a
screen is really helpful when there are tens of thousands of records and
only a handful are actually required.

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand



-----Original Message-----
From: AccessD <accessd-bounces+newsgrps=dalyn.co.nz at databaseadvisors.com> On
Behalf Of David Emerson
Sent: Wednesday, 12 May 2021 9:00 pm
To: 'Access Developers discussion and problem solving'
<accessd at databaseadvisors.com>
Subject: [AccessD] Access and SQL Error Adding Records on Continuous form

Hi Listers,

This is a problem I have encountered since Access 2013.  It doesn't happen
in Access 2010.

It relates to adding new records in continuous forms where the form
recordset is an ADODB recordset from an SQL database.  The code examples
originated from Juan Soto.  I have the ODBC Driver 17 for SQL Server, and
OLE DB Driver 18 for SQL Server drivers installed.  The behaviour also
occurs on all client sites where I used the technique.

When a couple of records already exist then adding a new record by going
into a blank record at the end of the form results in the record before the
newly inserted one having #Error showing in all the fields.  The data isn't
affected and shows in the record once the new record has been saved.

I have identified the following conditions that cause the problem:
1) Check boxes and Combo Boxes set it off when they are changed.
2) Check boxes on a form sets it off unless SQL Allow Nulls is ticked.
3) If any control has a default value then it is set off when any field is
edited.
4) A validation rule in a screen control sets it off when the control exited
(even if the field is correct).

However, these are all for the initial insert.  Once a record has been
entered then it edits fine without affecting the appearance of the previous
record.

To illustrate the problem I have put together a sample.  It can be
downloaded from here:
https://www.dropbox.com/s/vm0tlqin68gde6o/SQLErrorTest.zip?dl=0

The zip file has two files:
1) SQLErrorTest.accdb: This is an Access database with one form.  The form
vba has all the code necessary to link it to the SQL database and get the
recordset.  The Form_Open sub needs to be updated with the name of your SQL
instance where the SQL database is restored to.
2) SQLErrorTest.bak: This is an SQL backup in SQL2017 format.  It has one
table and a stored procedure for getting the records.

I would be very grateful if anyone is able to tell me why I am getting the
problem, and more importantly, how I can prevent it.

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand



--
AccessD mailing list
AccessD at databaseadvisors.com
https://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com