Update Query Crashes Access -- why?

[Guest]

I am using MS Access 2003. My application has a form with a command button
on it that will "lock" a record from further updating--turns a bit variable
on. Below are the command button basics:

Private Sub cmdLockCustomer_Click()
On Error GoTo Err_Handler

Dim strCustCode As String

strCustCode = Me.txtCustomerCode

Call LockCustomer(strCustCode)

Exit_Here:
Exit Sub
Err_Handler:
LogErrorToTable Err.Number, Err.Description, "Form_CustomerInfo",
"LockCustomer", Erl
Resume Exit_Here

End Sub

Public Function LockCustomer(CustCode As String)

On Error GoTo Err_Handler

Dim strSQL As String
Dim dbs As DAO.Database

Set dbs = CurrentDb


strSQL = "UPDATE CustomerInfo SET [Locked] = True WHERE
([CustomerCode]='" & CustCode & "');"
dbs.Execute strSQL, dbSeeChanges + dbFailOnError

Exit_Here:
Set dbs = Nothing
Exit Function
Err_Handler:
LogErrorToTable Err.Number, Err.Description, "CustomerServices",
"LockCustomer", Erl
Resume Exit_Here

End Function

When the dbs.execute statement runs it crashes access and totally blows me
out. Anyone know why this might happen?

Here is the Access error msg:

AppName: msaccess.exe AppVer: 11.0.6566.0 AppStamp:42cdb33e
ModName: ntdll.dll ModVer: 5.1.2600.2180 ModStamp:411096b4
fDebug: 0 Offset: 00001010

Thanks for your help!!

 

[AccessVandal via AccessMonster.com]

I suggest you rename the column “Locked” and the control in the form as well.

“Locked” is a reserved name for Access.

‘strSQL = "UPDATE CustomerInfo SET [Locked] = True WHERE
‘([CustomerCode]='" & CustCode & "');"
‘ dbs.Execute strSQL, dbSeeChanges + dbFailOnError

Try something like ,

UPDATE CustomerInfo SET YourColumnName = -1

If the Data Type is “Yes/No” in your table.

-1 = True 0 = False
if you are using a checkbox on a form.

Al wrote:

 

[Guest]

Thanks AccessVandal.

I tested this SQL in a query before I put it in code and it ran well with
the "Locked" variable name. I believe by putting brackets around locked it
turns it into a field name. I used your variable substitution idea of -1,0
for true, false. I do like that better.

Lastly I figured out that the SQL option dbFailOnError was causing the
error. I eliminated that out of the sql statement and it seems to work fine
now.

Thanks again!