[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

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

jpasquini_88  

>>>

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

>>>
0
jpasquini_88
Asked:
jpasquini_88
  • 3
  • 2
1 Solution
 
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)
0
 
jpasquini_88Author Commented:
routinet,

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_88

0
 
jpasquini_88Author Commented:
Update!

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..........  
0
 
Steve BinkCommented:
>>> 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.
0
 
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!


jpasquini_88
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now