UPDATE in presence of "Required" field

[The_Doc_Man]

Here's the scenario. I'm asking if anyone has seen this before or has any comments on what is happening. I'm hoping for suggestions.

We have an ACCESS 2007 front-end with a 2003 back-end, doing it this way for issues with digital signatures. (AC2007 backend does not support digital signatures.)

An update query is triggering a trap that makes no sense to me and I'm tearing out what little is left of my hair on this one. Before anyone asks, the database in question cannot be posted as it contains Sensitive information about U.S. Government computers and networks, and it would take me a LONG time to sanitize it and then still assure that the problem occurs.

We have two tables in question:

tIAV - contains fields such as ActID (autonumber pk), ActName (text), a bunch of other things, and ActIssueDate (date field - not a date-formatted text field, but a true date data type) that records when the original IAV entry was made. [ActIssueDate] is guaranteed to not be null, blank, or zero because of other code on the form that sanitizes it on original data entry. I have taken extreme measures to assure you can't navigate away and do an implied save, you can't close the form without cleaning it up, and a bunch of other things to assure that you correct your data before you commit it.

tSRVIAV - contains fields such as SrvID (long FK to tSRV, not relevant here), ActID (long FK to tIAV, very relevant here), SAStsDate ("true" date field), SADue ("true" date field), and some other things not relevant to the problem.

Table tSRVIAV is a junction table representing a many/many relationship between the two tables, tSRV and tIAV. It is theoretically possible that it would be sparse if a particular SRV/IAV combination does not occur, and in fact we know that older SRV entries that represent decommissioned servers do not have newer IAV entries represented in this table. We don't think this fact is relevant, but I'm trying to anticipate likely questions here.

We use a query, qIAVjSRVIAV that does a SELECT of many fields

"FROM tIAV LEFT JOIN tSRVIAV ON tIAV.ActID = tSRVIAV.ActID;"

which makes this query an explicit statement of the relationship between the tIAV and tSRVIAV tables. When we are processing the tSRVIAV entries only with respect to the tIAV entries, this is the query we use - and this query is central to the problem.

The offending code is in a VBA subroutine and the query says

"UPDATE qIAVjSRVIAV SET [SAStsDate] = [ActIssueDate] WHERE " & sCriteria & " ; "

The sCriteria string merely selects a subset of that join based on the status date being null or before the issue date of the IAV in question. We are, in essence, assuring that when this scan is done, the dates in tSRVIAV will be consistent with the entry dates in tIAV. There is an obscure pathway in which sometimes the user enters something incorrectly and we can't catch it until we run this scan, which is why this code exists in the first place.

OK, now here is the observed trap message, for which my trap handler fills in the name of the open form and the name of the person running the form, but the rest of the message is exactly as is returned by Access.

"User: Richard W Hunt Error trap in SupvOvrVu: IAVSRVSanity, #3314 - You must enter a value in the 'tSRVIAV.SADue' field."

But... this is trap occurred while running an update that isn't touching the SADue field at all. When I open the qIAVjSRVIAV query and scan through the recordset, there are no nulls in that [SADue] field. The only relevant thing I have found so far is that for table tSRVIAV, the [SADue] flag is marked as "Required" - but ...

1. This is not an INSERT INTO, it is an UPDATE for a record that does not have a null for [SADue]

2. The update does not involve the [SADue] field anyway.

Has anyone ever seen anything like this? I think this is incorrect behavior but I'd like a second opinion.

 

[CJ_London]

Using subqueries with update queries in Access usually won't work - and can produce misleading error codes/descriptions so the error you are getting may not be pointing you in the right direction

if you google 'access update query with subquery' you will find lots of links where this causes an issue, and some possible solutions

 

[The_Doc_Man]

Thanks, will take a look.

However, the oddball part of this is that this morning is the first time this code has ever twitched on me, and the particular code segment is unchanged from over a year ago.

As a further comment, I will add that I made a copy of the BE and brought it over to my developer environment. I did a compact and repair, so I know I don't have any damaged records, ... but the problem still manifests itself.

I'm also not sure about why sub-queries don't work here, because if I understand the concept of a sub-query, I could probably have done the explicit JOIN in a direct one-layer query (because it is a simple case) rather than use qIAVjSRVIAV as the basis of the operation.

Nonetheless, I thank you for the suggestion and will look into it.

 

[CJ_London]

the particular code segment is unchanged from over a year ago.

If it has been working for this length of time, this does imply something else has changed. Perhaps a table design change or something in your data. It may not be damaged data, just data 'out of range' e.g. a null where there shouldn't be one or a 'future date' perhaps. Maybe your sanitisation has missed a trick?

Moving subqueries back into another query won't actually solve the problem unless it enables you to remove the subquery. The query engine will attempt to optimise the whole 'nest' as one query so will still 'see' the subquery

 

[vbaInet]

The sCriteria string merely selects a subset of that join based on the status date being null or before the issue date of the IAV in question.

1. This is not an INSERT INTO, it is an UPDATE for a record that does not have a null for [SADue]

Hi Doc Man, I thought you no longer come on these forums

Can we see your strCriteria?
And which status date field are you referring to here?

Considering that you're updating records where data should exists between both tables, I would start by changing the LEFT JOIN to an INNER JOIN.

 

[The_Doc_Man]

Hi, vbaInet, I rarely come on right now due to an agreement with my employer that when I'm busy on my main project, I limit my time on the forum. As it happens, I also rarely come in from home because I'm doing some grandson duties and a bunch of honey-do's... but I digress.

Here is the code segment. The "bUseTch" piece is a type of filtration based on a table that remembers things I "touched" recently, but in this case the bUseTch flag is FALSE so does not apply. The lRecCnt variable is 1 when this code executes in the case that gives me the heartburn. The date of #31-Dec-2009# is our arbitrary "start of epoch" - the date before which nothing could possibly be valid. The audit code makes an entry in a separate table if the update worked. It does NOT make the entry for this case because the trap occurs on the .Execute method and never gets to that point. The rest is just counting various statitics for later event logging if we ever got that far.

    sCriteria = "( Nz([SAStsDate],#31-Dec-2009#) < [ActIssueDate] )"
    If bUseTch Then sCriteria = sCriteria & " AND " & sIAVInTouch & " AND " & sSRVInTouch
    
    lRecCnt = DCount("*", "qIAVjSRVIAV", sCriteria)
    
    If lRecCnt > 0 Then
        sContraQry = "UPDATE qIAVjSRVIAV SET [SAStsDate] = [ActIssueDate] WHERE " & sCriteria & " ; "
        dbCDB.Execute sContraQry, dbFailOnError
        lRecCnt = dbCDB.RecordsAffected
        lModSI = lModSI + lRecCnt
        sSumStep = "Updated " & CStr(lRecCnt) & " requests with impossible Effective Status Date - " & CStr(lRefCnt)
        AuditEvent_RS evtInternal, lCUSA, sEvtSum & sSumStep, rsEVT
        sMBSum = sMBSum & sSumStep & vbCrLf
    End If

I researched the issue of when a query is or is not updatable. I found links to something on MSDN as to when queries cannot be updated, but at least at first blush I am not sure I have one of the cases. The query doesn't use DISTINCT or an aggregate function, and the query was built with a Wizard that was aware of the existing one/many relationship between the two tables. (Which is why I'm using LEFT rather than INNER joins). I suppose I could use INNER but the query that was Wizard-built used LEFT and I tend to trust the wizards for the simple-minded stuff like that.

Over a year ago, I was still using DoCmd.RunSQL (and turning warnings on and off around each case). The use of .Execute is cleaner, avoids the SetWarnings calls, but it has other issues about what you can and cannot do. When you use the .RunSQL method, you get the full force of the Access environment, and this code ran correctly in that environment.

The thing I don't understand, and I'm not disputing CJ but I am a bit uncertain as to his contention, is that Access has a specific error that tells you when the query you are using is NOT updateable. Well, for this code I don't get that error and have never gotten this error with .Execute or .RunSQL - though when I was first building this application, I did get that message now and then for other queries. I would think that Access would kvetch at me if it though the query was not updateable rather than give me a very specific error about lacking a specific field from a specific table. Particularly when the field isn't even a candidate key, much less an actual key.

I am about to remove the "Required" field from the tSRVIAV table's reference to the SADue date as an experiment. I don't know what else would be screwed up by removing that setting, but if it makes the code work, I'm all for it. If it makes the code return a different (and more informative error), that would help too.

 

[The_Doc_Man]

CJ, your comment:

It may not be damaged data, just data 'out of range' e.g. a null where there shouldn't be one or a 'future date' perhaps. Maybe your sanitisation has missed a trick?

is definitely something I'm considering, but in my developer copy, I can't seem to find an out-of-range record in either table. I'm just a bit confused because this error has not cropped up before and I have not changed the definitions of the underlying tables in over a year. Nor has the code changed since the time I switched to .Execute vs. .RunSQL methods, and that was about a year ago. I find it really oddball that this would crop up after a year.

I'm going over the database tables with a fine-toothed comb now, but so far nothing at all has shown up when I do the individual queries. Oh, BTW, relational integrity is turned on, so I know that I don't have a dangling record in this case.

 

[The_Doc_Man]

It was a data error. My boss entered an IAV record but then decided he didn't need to "distribute" the new record because he know that it applied to nobody. I will leave this as proof of Murphy's Law - what CAN go wrong WILL go wrong. This case isn't supposed to happen, but it did.

The reason I couldn't find a bad record in either table is that there WAS no bad record in either table. The error only existed in the virual recordset built by the JOIN code. I'll have to revisit this code to verify that I can change the JOIN query without breaking something else, because that query gets used in more than one place. But that was the problem - bad data.

 

[vbaInet]

Good to hear you found the culprit Doc Man.

I would still consider switching that LEFT JOIN to an INNER JOIN though because you're pulling more records than you really need. Even though the wizard created it it doesn't know your full intent. If you wanted to manipulate records that exist in tIAV but do not exist in tSRIAV then leave it as a LEFT. I'm sure you know the difference anyway.

 

[The_Doc_Man]

Actually, if I needed to manipulate records in tIAV but not in tSRVIAV, I would pull tIAV directly, and I have a query that does a count of records in tSRVIAV matching tIAV, so I could identify mismatches - except that I didn't think I would ever have any. Normally we archive out the old stuff anyway, so the oddball cases sort of "fall off the system."

This was just a case of someone doing something I never considered. As soon as I have a chance to figure out whether other forms and queries would be clobbered by the LEFT/INNER change, I'll see what I can do. I'll also have to talk to my boss (gently) about leaving behind a little mess for me to identify and clean up.

 

[vbaInet]

Oh I see. I thought it was the other way round, I misread it then.

I'll also have to talk to my boss (gently) about leaving behind a little mess for me to identify and clean up.

Give your boss a smack on the wrist... see how s/he responds.

 

[CJ_London]

Glad you got it sorted - can see there have been a lot of posts

Just to clarify

CJ but I am a bit uncertain as to his contention, is that Access has a specific error that tells you when the query you are using is NOT updateable.

I got it into my head you were using a subquery - on reviewing the thread, I can't see why I thought that

But at least my contention that it could be a data issue was bang on