• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 114
  • Last Modified:

Getting error on CurrentDb.Execute ("UPDATE statement

I'm trying to use CurrentDB.Execute for an "UPDATE statement.  I'm tripping on the dbFailOnError.  How do I find out what error I'm getting?

Judith
0
JudithARyan
Asked:
JudithARyan
  • 7
  • 5
  • 2
  • +1
3 Solutions
 
Dale FyeCommented:
you need to include an error handler routine and tell Access to use that error handler if an error is encountered.  Generally a procedure might look something like:
Private Sub cmd_Update_Click()

    On Error goto ProcError

    strSQL = "UPDATE yourTable SET [SomeNumberField] = " & me.txtNewValue _
           & " WHERE [SomeOtherField] = " & me.OtherValue
    currentdb.execute strsql, dbfailonerror

ProcExit:
    Exit Sub

ProcError:
    msgbox err.number & vbcrlf & err.description, , "cmd_Update_Click"
    debug.print "cmd_Update_Click", err.number, err.description

End Sub

Open in new window

0
 
JudithARyanAuthor Commented:
If I'm using ProcError, why do I need dbFailOnError ?

Judith
0
 
Dale FyeCommented:
because without dbFailOnError, the Execute command will not raise an error if an error is encountered.  You must use the dbFailOnError if you want to be able to figure out what the error was, and present a meaningful message to the user.  The example I gave you is the simplest form of error handler.  I normally have a series of If statements in mine, something like:
    'Other code here
    '...

ProcError:
    'the following error numbers are made up
    If err.Number = 1234 Then
        msgbox "you failed to enter ... please try again"
        resume procExit
    elseif err.number = 321 then
        msgbox "Some other meaningful message"
        Resume Next  'you might want to simply continue processing after some errors
    elseif err.number = 987 Then
        'you might want to set some value and try the statement that originally raised the error
        me.txt_SomeValue = 0
        Resume
    Else
        msgbox err.number & vbcrlf & err.description, , "cmd_Update_Click"
        debug.print "cmd_Update_Click", err.number, err.description
        Resume ProcExit
    End If

End Sub

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Can you post the SQL of the Update statement or a screen shot from the query grid ?
0
 
JudithARyanAuthor Commented:
Sorry I haven't responded, I got distracted by other work.  

I did try your code, Dale, and got an error #0.  When I tried to figure out the problem, my changes led to a problem with my combo box that I'm have to dig out of.  Let me work on this and I'll send you a sample of the code and data.

Judith
0
 
JudithARyanAuthor Commented:
I've gotten myself in a real mess.  The form I'm working on is called by an autoexec module.  So when it gets an error it hangs and I can't get back into the program to do anything.

Is there a Function Key that will help me break into this problem?

Judith
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Hold down the shift key when you open the DB and the autoexec won't fire.

Jim.
0
 
JudithARyanAuthor Commented:
I'm attaching a file with images about my form.  Hopefully you can read it.  It's in PDF.

JudithLogonFormInfo.pdf
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
OK ... I'm confused now as to what the actual problem is at this point?
With CurrentDB.Execute ?
Or there is a problem with a Form ?
0
 
JudithARyanAuthor Commented:
After getting the response from Dale, I've come to realize that it is probable a form problem with the only combo box on it.  Sorry for the confusion!

Should I close this question and open a different one?

Judith
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Well ... what is the specific problem now?
Also ... in the last screen shot - in the Row Source property ... there is some kind of character (') just before the word SELECT .... ???
0
 
JudithARyanAuthor Commented:
The SELECT statement is started & ended with "s.

When I open the form, I get the following error message:

The record source '~sq_cfrmSysLogon2013~sq_cContactID' specified on this form or report does not exist.

As you can see by my record source query results, it does!  

?????

Judith
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I don't think you want "" surround the SQL stmt ...
And double check if that SQL will run alone in the query grid ?
0
 
JudithARyanAuthor Commented:
Here's the data table and the record source query results for the form.

Hope this helps.

JudithLogonFormData.pdf
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
This
'~sq_cfrmSysLogon2013~sq_cContactID'

is usually the type of Name your would find in MSysObjects for an SQL Statement used as a Form/Report Record Source or the Row Source for a combo box.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now