We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

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

Medium Priority
251 Views
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?
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2011

Commented:
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.

Author

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

How would I write the Trigger Code?

Author

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

Commented:
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

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?
Commented:
PAQed with points refunded (205)

GranMod
Community Support Moderator

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.