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

Posted on 2006-03-31
Medium Priority
Last Modified: 2013-11-28
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?
Question by:andychilds
LVL 50

Expert Comment

ID: 16342847
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.


Author Comment

ID: 16342967
I have designed a seperate stored procedure to update the field in access.

How would I write the Trigger Code?

Author Comment

ID: 16343038
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 12

Expert Comment

ID: 16344164
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

Author Comment

ID: 16597519
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?

Accepted Solution

GranMod earned 0 total points
ID: 16782524
PAQed with points refunded (205)

Community Support Moderator

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question