Recordset not updateable with Immediate IF / date function

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

Who is Participating?
Steve BinkConnect With a Mentor Commented:
>>> 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.
Steve BinkCommented:
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)
jpasquini_88Author Commented:

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


jpasquini_88Author Commented:

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

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.

All Courses

From novice to tech pro — start learning today.