Phucon-
Ah, you must include dbFailOnError to be able to trap any error generated by the
Execute:
dbs.Execute strSQL, dbFailOnError
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
---------------------------------------
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of cc
Sent: Saturday, April 14, 2012 12:23 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: RecordsAffected
Thank you very much Bill.
I used the Execute method. Below is partial code of the procedure:
Sub InsertRecord()
On Error GoTo ErrorHandler
DoCmd.Hourglass True
Set dbs = CurrentDb()
strSQL = "Insert INTO tblReportersTESTONLY (ID, NM, fyrend, DT_OPEN, CITY,
STATE, DateRecordInserted) " & _
"SELECT ID, NM, IIf(Len([fyrend])=3, Left([fyrend],1), Left([fyrend],2))" & _
" & " & Chr$(34) & "/" & Chr$(34) & " & " & "Right([fyrend], 2)" & " & " &
Chr$(34) & "/" & Chr$(34) & " & " & stryy & " AS fDATE, " & _
"Mid(DTOPEN,5,2)" & " & " & Chr$(34) & "/" & Chr$(34) & " & " &
"Right(DTOPEN,2)" & " & " & Chr$(34) & "/" & Chr$(34) & " & " & "Left(DTOPEN,
4)AS DateOpen, " & _
"CITY , STATE, Now() " & _
"FROM dbo_CUV_ATTR AS AA " & _
"WHERE ID In (SELECT IDTOP FROM dbo_CUV_TOP AS TOPH WHERE ID_TOP in(" &
frm!lstBH.Column(0) & ") " & _
"AND " & strDate & " BETWEEN TOPH.DTSTART AND TOPH.DTEND) " & _
"AND " & strDate & " BETWEEN AA.DTSTART AND AA.DTEND " & _
"AND fyrend = " & strmmdd & " AND BHIND = 1 AND DIST = 1 AND ESTTYPE = 1 AND
FBIND = 0"
dbs.Execute (strSQL)
DoCmd.Hourglass False
strRecordsAdded = dbs.RecordsAffected
End If
ExitProcedure:
Set dbs = Nothing
Exit Sub
ErrorHandler:
MsgBox Err.Number & vbCrLf & Err.Description
Resume ExitProcedure
Resume
End Sub
--- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@...>
wrote:
>
> Phucon
>
> It depends what method you are using and if you warnings are on or off. First
put this in the Immediate window and press Enter:
> DoCmd.SetWarnings true
>
> That will make sure your warnings are turned on.
>
> But you still have to tell us how you are executing your INSERT statement
before we can fully answer your question.
>
> Regards,
> Bill Mosca, Founder - MS_Access_Professionals
> http://www.thatlldoit.com
> Microsoft Office Access MVP
> https://mvp.support.microsoft.com/profile/Bill.Mosca
>
>
>
> --- In MS_Access_Professionals@yahoogroups.com, "cc" <saigonf7q5@> wrote:
> >
> > Hi good morning
> >
> > I use "Insert INTO..." to insert records to a table. When the record is
already exist in the table, the RecordsAffected is = 0, and when that happens, I
expect it will give out some sort of error message , but it does not. Why is
that?
> >
> >
> > Phucon
> >
>
Tidak ada komentar:
Posting Komentar