Problem with events not starting in datasheet view

[darren.nellies]

Hi guys,

I really hope somebody can help me.

I am having a problem with some coding on some of my forms. I will try
and give as much detail as possible.

I have a parent form (unbound) which holds a host of unbound text
boxes/combo boxes that I use for selection criteria to filter a
subform.

The subform is based on an SQL query that uses the values of the
unbound fields in the parent form as parameters to filter the table
fields.

The subform is in datasheet view.

Several fields within the datasheet view have BeforeUpdate and
AfterUpdate events as well as AfterInstert events at the form level to
manage several update queries that run to fill certain fields based on
entered values.

The form seems to work perfectly if the data is entered manually.
However, if the data is copy and pasted into the datasheet from Excel,
it applies the values into the fields as expected but does not launch
any of the form events. This means that the form does not perform any
of the update queries.

Does anyone have any idea why none of the events are launched? This is
causing me a whole range of problems.

By the way, I am using Access 2003 SP2.

Thanks

Darren

 

[Darren Nellies]

Good morning everybody,

I am still having this problem. Does anybody have a solution that they
can help me with?

Thanks

Darren

 

[John W. Vinson]

The form seems to work perfectly if the data is entered manually.
However, if the data is copy and pasted into the datasheet from Excel,
it applies the values into the fields as expected but does not launch
any of the form events. This means that the form does not perform any
of the update queries.

Does anyone have any idea why none of the events are launched? This is
causing me a whole range of problems.

Form and Control AfterUpdate, BeforeUpdate, etc. events do not fire unless the
user actually uses the form. Adding data to a table with code or by copy and
paste does not fire these events (by design).

Rather than copying and pasting, consider using VBA code from (say) a command
button event to use the TransferSpreadsheet method to migrate data from the
spreadsheet into the tables. This code could explicitly execute the needed
queries.