Re: Records are not saved after Paste

I made the Data form a subform of the Main form, I then change the
recorset of the Data form to a table reference not a query.

I opened the Main form with the Data form as a subform.
I pasted the data. Confirmation box shows, I say "no".
I paste again, confirmation, I say "no".
I keep pasting this way and answering "no", I still get the
confirmation box after paste.
(BTW, anytime I answer "yes" the data goes in as expected, even at the
table level.)
That's good so far...

After I say "no" to the confirmation paste....
I click a button on the Main form which changes the source object for
the subform that had the Data subform. The other form opens in the
subform control.
I click a button that changes the source object for the subform back
to the Data form.

I paste the data, NO CONFIRMATION, all data VISIBLE, editing allowed.
No filtering allowed without error, as stated above.

I then click a button, as before, to change to another source
object...
I then come back to the Data form. ALL DATA STILL VISIBLE!

Before I had made any changes, when I did the above procedure, the
data would not be in the Data form when I returned to it, which
actually would, at the very least, tip off the user that something was
wrong.

Now, the data is there, visibly, BUT.....is it really?

Leaving the Main form open, I opened the table directly.
Guess what? NO DATA!

I try to edit data in the table, I get the "session locked" error as
stated above.

The only thing that will free up the table for edits is to close the
Main form or send it
to design view.

So, I eliminated the Frame form, change the source of the data to just
a pure table.
Now, the data remains, upon changing the subform source object with a
button on the Main form, but the data is still not in the table, and
editing at the table level is locked.

I suspect that there is something about the Main form.
There is no code running behind the form, related to data entry, just
button clicks.

I will do some experiments with a new blank form, put in a subform and
point it to the Data form.
I will also, make a copy of the Main form and slowly eliminate all
features, etc., until the bad behavoir stops.

HTH, (Salad)

Salad wrote:
squeezebot wrote:

6:05 PM 3/20/2009

Records are not saved after Paste

Senario:
Microsoft Access 2003, Windows XP SP3

Copying data from an Excel spread***
Highlight data cells only
Issue a copy command, ctrl-C or other

Switch to MS-Access application

MS-Access Pasting Data
Paste data to corresponding columns of a subform Data*** view
Destination form is a data*** subform of a subform of a main form
3 forms total: Main Form --> Frame Form --> Data Form --> Query on
Table
Data Form is in data*** view

Highlight the destination cells (start at new record)
All cells contiguous, data types match source

After paste operation there are no errors during the paste.
There is NO confirmation of pasted records, For Example:

"You are about to paste ## record(s)."
"Are you sure you want to paste these records?"

Navigation in the data***
Records navigate as usual
Record editing as usual
Record deletion as usual (with warnings)
Record addition as usual

Symptoms:

First tip off something was wrong:
A report button on the form, indicates "No Records"
The records are visible in the Data form data***.

Filtering of columns using a custom filter, which is part of the
Frame form returns the following error:

"Error # 2074"
"The operation is not supported within transactions."

After the paste, the confirmation box does not show, but the records
are visibly pasted.

Behavior:
This is intermittent. Sometimes it saves the records, sometimes it
does not.
Upon opening the application (Main form), the first paste works with
confirmation box.
Subsequent pasting, whether copying from Excel or Access datasheets,
does not work.

Pasting of small chunks of data from other access datasheets or
within the same data***
does not save either.

Main Form - Symptoms Occur, pasting to other subforms does not work
either
The following forms save pasted data as usual, when opened
individually.
Frame Form --> Data Form - Confirmation box displayed, Symptoms Do
not occur
Data Form --> Confirmation box displayed, Query on Table Symptoms Do
not occur
Table - Confirmation box displayed, Symptoms Do not occur

Overall - If no paste confirmation box shows, the data is not saved.
That is, there was no case where the confirmation box showed and
the data was not saved, after clicking "yes" of course.

Attempted work arounds:
Deleting all code behind the subform and controls. (HasModule=No)
Symptoms still occur.
The Main form has a timer (=1000), to update a label with the date
and time.
This code was turned deleted. Symptoms still occur.

Opening table (linked) directly, shows no data and any data entered
causes this error:
"Could not update; currently locked by another session on this
machine."

Reopening the Main form works, but only on first paste. Subsequent
pastes fail.
As stated above, pasting to the subforms and directly to the table
and query work.
Reopening the application. For users, this works, but only on first
paste.

Synopsis:
Since this happens on different datasheets opened on the Main form,
it does NOT
seem to be because of a particular recordset.
That is, the action is the same: Pasting copied data
The behavior is the same for different recordsets: As Described above

Although, it's possible that the forms, form queries, or tables have
some
similar design that is causing this behavior.

I think Access thinks that it is still in the middle of a transaction
and will not
allow any data entry to the raw table until it is complete.
That is, Access wont release the recordset until this "transaction"
is complete.

It seems to be related to the Main form in the application, since the
symptoms did
not arise pasting in the Frame form, Data form, table or query alone.


Is this a known bug in Access or logical error in setting up the
properties for the various
database objects or perhaps a common programming over sight?

Is there a way to tell Access to stop/release any transactions
currently in progress?
(Maybe I could put a "stop transaction" somewhere...I dont know, I'm
lost.

Thanks in advance.

I haven't attempted to do what you are attempting to do. That said...

If you open the sub table, are there any records that were appended?
But with a blank link field?

Let's says you have an order record. Order 10. You paste the details
of the order. But the columns don't have the Order # 10. What could
the records be linked to? Could that be happening?
.