Problem pasting multiple records into a form

[Jeremy Saul]

I have a continuous form that I use to display and edit the fields of a
single table. When I add a record I need to add a record to a related table
to maintain consistency and relational integrity. To do this I have used the
After_Insert event procedure on the form to add the record I need to the
other table using a DAO recordset and the Add and Update methods.

All this works fine when I add a record. It also works fine if I copy and
paste a record as a new record. The problem comes if I copy and then paste
multiple records as new records. The Update method on the other
recordset/table fails with an error "data is being updated by another user"
(or something similar). It is as though their are multiple AfterUpdate
events running in parallel. In any event, how can I find the details from
the two records that are being pasted?

As I see it there are several possible solutions.
1. Find some way to serialise the pasting of the multiple records.
2. Restrict the paste operation to only allow a single record to be pasted
3. Prevent the form allowing the paste operation at all, yet still be able
to add new records individually from newly entered data.

Any advice on any of these possible solutions, or the problem in general
(like how to avoid it completely) would be appreciated.

Jeremy Saul

 

[Allen Browne]

Hi Jeremy

When you perform a multi-record paste, you should receive a message asking
whether you want to paste the nn records (unless you have turned SetWarnings
off). The message indicates that Access has a transaction pending. The
message runs *after* the Form_AfterInsert event runs for each record. This
would indicate that the transaction is pending at the time of the
Form_AfterInsert event.

We don't know how you are appending records to the other table, but if you
are executing something like this:
"INSERT INTO Table2 SELECT Table1.* FROM Table1 WHERE Table1.ID = " & Me.ID
& ";"
then the record you are trying to read from Table1 is not there yet.

If you are trying to create a *related* record in another table, that would
also fail, because the original is not committed yet.

I'm not sure what the best solution will be, but but hopefully that helps
you understand what's going on.

 

[Jeremy Saul]

Thanks, that does explain what is happening. The error message I get is
"Record cannot be read because it is locked by another user" which fits with
what you say about the pasted records not yet being saved. I was creating
the new records in the other table using VB code to open a recordset, use
the Add method to create the new record, filling in the fields and then
using the Update method to write the new record. The data I was using to
fill the new record included references to the current record of the main
table, so I guess that is why it was failing.

I have had a suggestion to paste the rows into a temporary table using code
and then process each row one at a time. I guess the issue then is how to
prevent the user simply pressing "Ctrl-V" to paste the rows directly back
into the table. I have seen some other tips which sets the Allow insert
property of the form to false except when a command button is pressed to
enter a new record. I guess I could use the same technique to copy and paste
records under my control while preventing use of the general copy/paste
commands.

Regards

Jeremy Saul