Recordset not updateable with Immediate IF / date function

Posted on 2005-04-12
Last Modified: 2012-08-14
Hi all,

Does anyone have a clue as to why this line of code (entered by another programmer, gone) would error out with 'Recordset not updateable'?
The first line (SpecialProjects=......) will produce the error in the Immediate window.    The back end tables are off a SQL Server.   I have a suspicion it has something to do with dates being handled differently between Access and SQL Server.

I have the whole program but this is the routine being called.    
Even a point in the right direction would be appreciated!   Thanks



Public Sub LoadTimes()
    'TLH 03/31/2005
    SpecialProjects = IIf(IsDate(dtmSpecialProjects) = True, dtmSpecialProjects, #12:00:00 AM#)
    Administrative = IIf(IsDate(dtmAdministrative) = True, dtmAdministrative, #12:00:00 AM#)
    ABT = IIf(IsDate(dtmABT) = True, dtmABT, #12:00:00 AM#)
    TotalNonMeasured = IIf(IsDate(dtmSpecialProjects) = True, dtmSpecialProjects, 0) + IIf(IsDate(dtmAdministrative) = True, dtmAdministrative, 0) + IIf(IsDate(dtmABT) = True, dtmABT, 0)
    ComputerDownTime = IIf(IsDate(dtmComputerDownTime) = True, dtmComputerDownTime, #12:00:00 AM#)
    Overtime = IIf(IsDate(dtmOvertime) = True, dtmOvertime, #12:00:00 AM#)
End Sub

The resulting error:

Run-time error '-2147352567 (80020009)':
This Recordset is not updateable

Question by:jpasquini_88
    LVL 50

    Expert Comment

    by:Steve Bink
    Does dtmSpecialProjects reference a field in the query?  Is SpecialProjects referencing a field in the query?  Try this replacement:

    SpecialProjects = Iif(IsDate(dtmSpecialProjects), dtmSpecialProjects, 0)

    Author Comment


    SpecialProjects I found is a data cell on a form in the database, called 'frmSupsDailyProduction'.     The data field SpecialProjects has an input mask of '0:00;0;_'.    dtmSpecialProjects is listed as the control source for this field.  

    The routine being called is 'Forms![frmSupsDailyProduction].LoadTimes'

    I'm trying to trace back which table this field came from, there are at least two huge queries with several outer and inner joins.   I'm wondering how the default (NULL) date '#12:00:00 AM#' gets translated into that input mask, and if it makes any difference between SQL and MS Access.    It looks to me like it's trying to put a 12:00 date in if there is no date, but it simply refuses to update................still plugging away



    Author Comment


    After working with this further I found the following SELECT statement which appears to be the culprit.   It looks like indeed the recordset that it pulls is not updateable.    If I execute this in data view I get a set of rows which I can't edit.

    SELECT tblDailyProduction.*, [tlkpEmployee.strEmpLName] & ", " & [tlkpEmployee.strEmpFName] AS EmpName
    FROM tblDailyProduction INNER JOIN tlkpEmployee ON tblDailyProduction.strWorkerID = tlkpEmployee.strWorkerId
    WHERE (((tblDailyProduction.blnAbsentEmp)=False));

    All of these tables appear to be pulling from a SQL Server database, KC_Test.    I wonder if the query itself is the problem here, though I fear too much tinkering.   This is part of a vast web of Accesssanity, written by at least 10 different temporary (disposable??) contract employees like me over several years..........  
    LVL 50

    Accepted Solution

    >>> FROM tblDailyProduction INNER JOIN tlkpEmployee ON tblDailyProduction.strWorkerID = tlkpEmployee.strWorkerId

    What is the nature of that relationship?  If it is one-to-many, you cannot edit the 'one' side of the record, though you MAY be able to change data in the 'many' side while in datasheet view.  As a general rule, queries with one-many JOIN clauses are not updatable.

    Author Comment

    Thanks for the help!    You pointed me on the right path.    We ended up changing this to query the tlbDailyProduction and tlkpEmployee tables separately, and the recordset became updateable, which fixed everything down the line.    Misleading how this translated down to a date error.    I award you points!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    794 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

    16 Experts available now in Live!

    Get 1:1 Help Now