View cannot be updated because modification affects multiple base tables

[Shaimaa T]

Hi all,

I am trying to trap the database error "View cannot be updated because modification affects multiple base tables" in the Form OnError event to display a user-friendly message instead of the above.

The above can happen in the scenario of inserting/updating several fields of different tables at one time, likewise what I am facing now is the scenario of the user copying an entire record and pasting it.

I tried
Case 4405
MsgBox "Can not update the record. it is related to base tables"
Response = acDataErrContinue

But it doesn't work. How can I know the case number and solve all the scenarios of multiple insert/update attempts?

Also, are there any better solution than this? I first tried to detach the SQL statements out of the views and make it one select statement so that I can normally multiple update/insert(as this problem happens with views only) but since CTE is not supported in Access , I failed to do so.

Any help with the above and/or ideas?

 

[Shaimaa T]

I trapped it using

Case 3155
MsgBox "Can not update the record. it is related to base tables"
Response = acDataErrContinue

But how can I "undo " the pasted record ?

 

[spikepl]

Me.undo probably.

But since your issue seems structural rather than related to specific records, why do you allow pasting in the first place?

 

[Shaimaa T]

pasting can be done in the datasheet by doing ctrl+v and/or right click context menu and choose paste.

How can I disable this?

 

[vbaInet]

pasting can be done in the datasheet by doing ctrl+v and/or right click context menu and choose paste.

How can I disable this?

You forget that we can see all the threads that people post.

It's a waste of our time if you're asking the same question in multiple threads.
http://www.access-programmers.co.uk/forums/showthread.php?t=267828&page=1