How can I get an MS Access form to update all fields in a view except one using INSTEAD OF UPDATE?

I have a view which has a base table and another view which returns a maxium value.

This view is used as the basis for a table in MS Access. When the max value field (IncurredAmount) is edited, an ODBC error occurs saying that the field cannot be updated.

Is there a way that i can use the INSTEAD OF UPDATE function in the view so that this field is not updated with all the others?
andychildsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
yes
in the trigger simply don't proppgate any update for the summary column....

but shouldn't you attempt to make the column non change-able in  access.

0
andychildsAuthor Commented:
I have designed a seperate stored procedure to update the field in access.

How would I write the Trigger Code?
0
andychildsAuthor Commented:
This is my view at the moment:

                SELECT dbo.Loss.*, dbo.[IncurredAmount-SelectLossIncurredAmount].IncurredAmount AS IncurredAmount
      FROM dbo.Loss

      INNER JOIN dbo.[IncurredAmount-SelectLossIncurredAmount]
      ON dbo.Loss.LossID = dbo.[IncurredAmount-SelectLossIncurredAmount].LossID

The field that I want to omit is IncurredAmount
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

fruhjCommented:
Have you considered making the form field read only so that it is not possible for the end user to edit that field?

If they prefer the datasheet view - you could create a form with the required attributes and set the properties to display it as a datasheet
0
andychildsAuthor Commented:
Problem Solved!

I have created a trigger which has solved the problem; it's all very easy when you know how! It's just a shame it took me so long to learn!

Thanks for all your contributions. Does anybody know how I can close this question?
0
GranModCommented:
PAQed with points refunded (205)

GranMod
Community Support Moderator
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.