I cant help with the SQL error idea, but since you are running in Access, ALL errors will be in the Access front end.
No need to record them since they happen live. The message box error will stop at the point you need and debug will lead you to the culprit. This is how I've always tracked errors.
NOW, if you want to get fancy, in your error code, right before the Msgbox err.description, put a Routine to write the error to a log table. I use this.
usage: POST2LOG (err.description,"MyRoutine()",err)
Code:
Public Sub Post2Log(pvEvent, pvSrc, pvError)
Dim sSql As String
Dim vElaps, vUser
vUser = Environ("Username")
sSql = "INSERT INTO tLogs ([Event], [Tbl], [EventDate],[USER]) values ('" & pvEvent & "','" & pvSrc & "','" & pvError & "','" & vUser & "',#" & Now() & "#)"
HrGlass
DoCmd.RunSQL sSql
End Sub