Records added by form GONE from underlying table!

[Charweb]

I have a continuous form that users can paste a list of serial numbers into.
The underlying data is a simple query that permits updates and insertions
into the data table. The serial numbers are validated through a BeforeUpdate
event, and if they don't meet the required format then CancelEvent is
invoked. After an erroneous serial number is pasted, the event for that
serial number is cancelled and the remaining non-erroneous serial numbers
continue to be pasted into the form.

When all have been pasted the form appears as if all the non-erroneous
records have been added, with autonumbers and everything. However the
underlying table has not accepted one record from the form. In fact, the
autonumbering of the table skips all those in the form. If there were no
errors in the paste, all records are inserted as expected.

Anybody have a clue?

Thanks!

 

[Sprinks]

Charweb,

This sounds like a bug, but may be a logical error in your code. If you
will post it, perhaps we can find the error.

Best regards.
Sprinks

 

[Charweb]

Wow that was a speedy response!

I am trying to stay away from "code" and am instead using canned commands in
Macros. Anyway I have just discovered a little thing I didn't mention is
causing this to happen. The macro was having a form open that explains the
error of the serial number (I've tried both before and after the CancelEvent
command), which seems to result in cancelling the whole paste event, even
though the form appears to be accepting all of the non-erroneous records
after each error. If I delete the OpenForm command then it works fine,
except that the user only gets notified by using the "Paste Errors" file
after the paste. This is fine if the user is actually pasting but the form
might also be used for hand-entering. I'm trying to come up with a
work-around!

Thanks!

 

[Sprinks]

Charweb,

I may not be able to help you, as I never use macros because they offer no
error-handling. I suspect this is at the root of your problem. Access is
likely not "listening" as you paste the other values.

By turning this into VBA, you can likely handle your error conditions
gracefully and continue processing new records. If you post your macro,
perhaps I or someone else can tell you how to translate it.

By the way, VBA is not conceptually hard, in fact, it is much more readable
and intuitive than macro commands. Once you learn the object model (objects,
properties, and methods), it is not difficult. A good place to start is
reading the code generated by the command button wizard.

By the way, many VBA methods, which operate on an object, are named the same
as the corresponding macro Action, so much of what you've learned using
macros will be transferable.

Hope that helps.
Sprinks