ACCESS FEED: [MS_AccessPros] Re: RecordsAffected

Jumat, 13 April 2012

[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
> >
>