[AccessD] Operation must use an updatable query...and it does.

Hi Christopher

First, specify the control as a parameter:

PARAMETERS [Forms]![frmFcod]![JobID] Long;
UPDATE Job
SET Job.JobNotes =
DCount("Note","tblNotes","JobID =" & [Forms]![frmFcod]![JobID])
WHERE (((Job.JobID)=[Forms]![frmFcod]![JobID]));

If that doesn't work, why not rewrite it:

  PARAMETERS JobNo Long, NoteCount Long;
  UPDATE Job
  SET Job.JobNotes = NoteCount
  WHERE (Job.JobID = JobNo);

and then:

  lngJobID = Nz([Forms]![frmFcod]![JobID], 0)
  lngNoteCount = DCount("Note", "tblNotes", "JobID = " & lngJobID & "")
  Set dbs = CurrentDb()
  Set qdy = dbs.QueryDefs("qryUpdateNotesCount")
  Set qdy.Parameters("JobNo").Value = lngJobID
  Set qdy.Parameters("NoteCount").Value = lngNoteCount
  qdy.Execute
  qdy.Close

This may turn out faster than debugging the current code.

/gustav


> This is weird.

> I'm refitting an Access 2000 app that a client of mine wrote to run
> his business.  One of the issues that has recently started happening
> (after the client exported all the objects from a corrupted MDB into
> a fresh MDB container) is that a query being run from a form triggers
> the following error:

> "Operation must use an updatable query"

> Here is the code that is firing the query:

> [code]  

> DoCmd.SetWarnings False
> DoCmd.OpenQuery "qryUpdateNotesCount"
> DoCmd.SetWarnings True

> [/code]

> Primitive, but OK.  I figure the query is written incorrectly or has
> recently been changed, and prepare myself to go clean it up/fix it. 
> But here's the kicker:

> 1) This exact code - and this exact query - has been running fine for
> years.  I can dig up previous versions of the app with this code and
> query in them, and this line of code executes just fine.

> 2) If I actually go to the queries tab and double-click
> qryUpdateNoteCount, it works just fine, even immediately after trying
> to execute that code and throwing an error.

> So now I'm scratching my head.  This is very perplexing.  This is the
> only build of the app in which this line of code errors out.  Yet, I
> can manually execute the query just fine in this build.  Even worse,
> it only fails on some machines.  Whiskey Tango Foxtrot, over?  This
> used to work on every machine in the house.  

> Here is the syntax of the query:

> [code]

> UPDATE Job SET Job.JobNotes = DCount("Note","tblNotes","JobID =" &
> [forms]![frmfcod]![jobid])
> WHERE (((Job.JobID)=[Forms]![frmFcod]![JobID]));
> [/code]

> I decompiled, recompiled, compacted & repaired.  No change.

> I am considering going through the app and replacing this line of
> code with something else to update this field.  But I'm loathe to do
> so until I understand why the failure is occurring to start with.

> Any ideas?

> -Christopher Hawkins, the Great and Terrible-
> Respectfully,

> Christopher Hawkins
> Software Developer
> (559) 687-7591
> http://www.christopherhawkins.com