ACCESS FEED: RE: [MS_AccessPros] UPDATE Statement

Kamis, 01 September 2011

RE: [MS_AccessPros] UPDATE Statement

Hi Mike

Are you using the Execute method to run your SQL statement?

Try adding dbFailOnError to the statement to see if it is failing. Also,
display the RecordsAffected for debugging:

Dim db As database
Set db = CurrentDb
strSQL = ...
db.Execute strSQL, dbFailOnError
MsgBox db.RecordsAffected & " records updated"

I'm with Duane - there is probable one record in the process of being edited
(and therefore locked) on a currently open form or subform. If you can
establish this as the cause, then use Form.Dirty = false to force the record
to me saved before you run your update query.

Best wishes,
Graham

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mithomas48
Sent: Friday, 2 September 2011 02:22
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] UPDATE Statement

Can anyone explain why the below Update statement misses one record in every
instance? For example, let's say there are 50 records to be updated and
there are 2 account numbers. So there are 25 records for each account
number. The update statement below updates each account number but leaves
only ONE record for that account number NOT updated. When I sort a query on
the accounts, I can see that it is probably either the first or last record
for that account that's not updated. Can anyone explain why this is
happening and how to correct it?

strSQL = "UPDATE PTbl_Activity SET " & _
"QC_Complete = True, " & _
"QC_Complete_DT = Now() " & _
"WHERE PTbl_Activity.[LOAN NUMBER] = '" & iCurrLoan & "' " & _
"AND PTbl_Activity.PORTFOLIO = '" & iPortf & "';"

Thanks!
Mike