RE: SQL insert record return value?
- From: "'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@xxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 6 Oct 2005 14:36:06 -0700
Hi, Paul.
> I have a loop that includes inserting a record to a datasbase. What I'd
> like to to is be able to tell if the new entry successfully wrote to the
> database.
Instead of using RunSQL for this action query, use Execute and the
dbFailOnError parameter to prevent any messages from being displayed to the
user unless there actually is an error, in which case your error handler can
tell the user exactly which record failed to be appended to the table, since
there's only one record at a time being appended. The error handler can quit
processing -- or return to processing -- the records. For example:
' Within a loop:
CurrentDb().Execute "INSERT INTO tblCourses (CourseName) " & _
"VALUES ('" & sValue & "');", dbFailOnError
nCount = nCount + 1
' More code in the loop.
CleanUp:
MsgBox "Successfully saved " & nCount & " courses."
Exit Sub
ErrHandler:
MsgBox "Error in SaveSelectionBtn_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp
End Sub
.. . . where tblCourses is the name of the table, CourseName is the name of
the field, and sValue is the text string value being entered into the field,
and nCount is the counter for successful appends. If there's no error (and
no subsequent error message), then the record was successfully appended and
the counter will increment. When the processing stops (whether because there
are no more records to append or there is an error), the user will see how
many records were saved.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
"pvdalen" wrote:
> Howdy,
>
> I have a loop that includes inserting a record to a datasbase. What I'd
> like to to is be able to tell if the new entry successfully wrote to the
> database. I have SetWarnings set to false because I don't want the user to
> have to deal with those mesages every time, but I'd like to report how many
> records were inserted. Is there a value that is set or returned from the
> DoCmd RunSQL statement that I can capture to conditionally increment a
> variable?
>
> Thanks,
> Paul
.
- Prev by Date: RE: Access closes when code is invoked
- Next by Date: Re: Date for this month
- Previous by thread: RE: Access closes when code is invoked
- Next by thread: Re: Using calculated query field in Recordset
- Index(es):
Relevant Pages
|
