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

    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

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

    How would I write the Trigger Code?

    Author Comment

    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
    LVL 12

    Expert Comment

    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

    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

    PAQed with points refunded (205)

    Community Support Moderator

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now