db.RecordsAffected doesn't always work 3

[Lhuffst]

I have sql for insert and updating records. At the end, I use the db.RecordsAffected which sometimes seems to work and sometimes not. For example, I just updated my existing data on a form and although the changes were inserted to the databae, the db.recordsaffected was still 0. Does anyone have any ideas why it wouldn't work and/or if there is another way to make sure that my inserts & updates are working?

The code I use is:

  If db.RecordsAffected = 0 Then
  MsgBox "Your new Contract Was Not Updated."
  'nill = AddErrorLog(0,"New Contract Was Not Updated!", "CitationUpdate", ssql)
  CitationUpdate = False
  Exit Function
End If

Thanks
Lhuffst

 

[Darrylles]

Lhuffst,

How are you executing your SQL?
(You haven't shown that part of the code).

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[Lhuffst]

Darrylle, here it is. When I check the table, the changes were updated. If I test it via a query, it works but when it runs via the program, it works but the db.recordsaffected = 0. Thanks lhuffst

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb()
ssql = "Update tblCitationInfo Set " & _
"fldDateSafetyReceived = '" & txtDateSafetyReceived & "', " & _
"fldPoliceDeptReportingCitation= '" & checkTicks(txtPoliceDeptReportingCitation) & "', " & _
"fldPoliceDeptPhone= '" & checkTicks(txtPolicePhone) & "', " & _
"fldCitationMailDate= '" & txtCitationMailDate & "', " & _
"fldCitationNumber= '" & checkTicks(TxtCitationNum) & "', " & _
"FldCitationDueDate= '" & txtCitationDueDate & "', " & _
"fldCitationAmountDue= '" & checkTicks(txtAmountDue) & "', " & _
"fldCitationLocation= '" & checkTicks(txtCitationLocation) & "', " & _
"fldViolationtype= '" & checkTicks(cboViolationType) & "', " & _
"fldCitationDetail= '" & checkTicks(txtCitationDetail) & "',"
ssql = ssql & _
"fldDateObserved= '" & txtViolationDate & "', " & _
"fldtimeObserved= '" & txtViolationTime & "', " & _
"fldPostedSpeed= '" & checkTicks(txtPostedSpeed) & "', " & _
"fldViolationSpeed= '" & checkTicks(txtViolationSpeed) & "', " & _
"fldDescOfVehicle= '" & checkTicks(txtVehicleDescription) & "', " & _
"fldLicensePlateNumber= '" & checkTicks(txtLicensePlate) & "', " & _
"fldVehicleNumber= '" & checkTicks(txtVehicleNumber) & "', " & _
"fldNumOfOccupants= '" & checkTicks(txtNumOccupants) & "', " & _
"fldGroupName= '" & checkTicks(txtGroupName) & "', " & _
"fldLocation= '" & checkTicks(txtEmpWorkLocation) & "',"
ssql = ssql & _
"fldSupervisorName = '" & checkTicks(txtEmpSupervisor) & "', " & _
"fldDateDueToSafety= '" & txtDateDueToSafety & "', " & _
"fldDateSentToSupervisor= '" & txtDateSentToSupervisor & "', " & _
"fldDateReviewedBySafety= '" & txtDateReviewedBySafety & "', " & _
"fldDefensiveDrivingCandidate = " & ChkDefensiveDriving & ", " & _
"fldDriverImprovementClassDate= '" & txtDateDriverImprovementClassTaken & "', " & _
"fldCounty= '" & checkTicks(CboViolationCounty) & "', " & _
"fldNumberofCitations= '" & checkTicks(txtViolationsReceived) & "', " & _
"fldDriverName = '" & checkTicks(txtDriverName) & "', " & _
"fldDatePaid = '" & txtDateFinePaid & "', " & _
"fldPaymentMethod = '" & checkTicks(txtPaymentMethod) & "', " & _
"fldCheckNumber = '" & checkTicks(txtCheckNumber) & "', " & _
"fldReceiptNumber = '" & checkTicks(txtReceiptNumber) & "' " & _
" Where fldCitationNumber = '" & g_CitationNum & "'"
  db.Execute (ssql)
  

  If db.RecordsAffected = 0 Then
  MsgBox "Your new Contract Was Not Updated."
  'nill = AddErrorLog(0,"New Contract Was Not Updated!", "CitationUpdate", ssql)
  CitationUpdate = False
  Exit Function
End If

 

[kzutter]

Just for the heck of it, try unwrapping the parentheses from the ssql argument

try
db.execute ssql

I know it sounds silly, but I have had problems with unneeded parens in the past.

 

[Lhuffst]

Kzutter,
Thanks for the suggestion but I still have the problem.
Lhuffst

 

[idbr]

Hi Lhuffst,

Are your records actually being updated? From the help file:

In a Microsoft Jet workspace, if you provide a syntactically correct SQL statement and have the appropriate permissions, the Execute method won't fail — even if not a single row can be modified or deleted. Therefore, always use the dbFailOnError option when using the Execute method to run an update or delete query. This option generates a run-time error and rolls back all successful changes if any of the records affected are locked and can't be updated or deleted.

Cheers, Iain

 

[Darrylles]

Hi again,

I was aware of the dbFailOnError switch, and my initial question was leading up to that.

I was waiting for your [tt]db.Execute (ssql)[/tt]

This can be trapped with dbFailOnError.

It is failing somewhere, maybe this will tell you why.

ATB

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[Lhuffst]

Hi Darrylle

I did try the dbFailOnError at one point but the results were exactly the same.

As far as the records updating (or adding), they seem to work fine.

Should I close the recordset before I do the db.recordsaffected?
Thanks for the suggestions.

 

[idbr]

Hi Lhuffst,

If it's unreliable, why not just work around:

Add a column to your table, say, Update_Key (Double)

Add a couple of lines of code:

dim dblUpdateKey as double

' convert the current time and date to double
set dblUpdateKey = cdbl(now())

ssql = .... & _
"Update_Key = " & dblUpdateKey

if dcount("Update_Key","tblCitationInfo", "Update_Key = " & dblUpdateKey) = 0 then ...

Regards, Iain

 

[Lhuffst]

Great Idea. I'll try that
Lhuffst

 

[TheAceMan1]

How are ya Lhuffst . . .

Just happen to run across Currentdb.RecordsAffected Property in Microsoft Access

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Lhuffst]

Great Thanks for letting me know. I put this to good use.
Lhuffst