UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Recordsaffected, Any Version    
 
   
access2009eg
post Jul 20 2019, 11:19 PM
Post#1



Posts: 1,098
Joined: 19-February 08



friends

Why RecordsAffected always give me 0 .while the record is updated or update is applied on the record

please see attached DB
Attached File(s)
Attached File  abc.zip ( 67.29K )Number of downloads: 8
 

--------------------
I would like to Thank you for your help
Go to the top of the page
 
DanielPineault
post Jul 21 2019, 08:07 AM
Post#2


UtterAccess VIP
Posts: 6,771
Joined: 30-June 11



I have a bigger question here since you are using a bound form, why are you performing UPDATEs, DELETEs ... in this manner? Just let the form manage the events normally.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
DanielPineault
post Jul 21 2019, 08:21 AM
Post#3


UtterAccess VIP
Posts: 6,771
Joined: 30-June 11



As for your problem, it is caused by 2 things

1- Using On Error Resume Next - blocked/masked the actual error from being reported to you, you shouldn't use this command. It is only for very specific functions, not to be use in a normal procs.
2 - Your SQL statement had data type issues, once resolved, it works fine.

Try
CODE
Private Sub cmdUpdate_Click()
    Dim CountAffectedRecodUpdated As Integer
    Dim sSQL                  As String

    On Error GoTo Error_Handler

    getCompanyID_ = Me.CompanyId.Value

    If (Me.CompanyId.Value = "") Then
        MsgBox "Maybe Record is empty pls check", vbCritical + vbOKOnly, "error"
    Else
        Set db = CurrentDb
        sSQL = "UPDATE tblCompany " & vbCrLf & _
               "SET Nuip= '" & Me.Nuip & "', CompanyName= '" & Me.CompanyName & "', CompanyShortName='" & Me.CompanyShortName & "', CompanyType_ID=" & Me.CompanyType_ID & ", IsActive=" & Me.IsActive & " " & vbCrLf & _
               "WHERE tblCompany.CompanyId =" & getCompanyID() & ";"
        With db
            .Execute sSQL, dbFailOnError
            CountAffectedRecodUpdated = .RecordsAffected
        End With
        If CountAffectedRecodUpdated >= 1 Then
            MsgBox CountAffectedRecodUpdated & " Record(s) Updated Succefuly", vbInformation + vbOKOnly, "Successful"
        End If
    End If

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: cmdUpdate_Click" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub



But as stated above, you don't need any of this.

Also, why did you set db and rs a global vars?

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
access2009eg
post Jul 22 2019, 06:21 PM
Post#4



Posts: 1,098
Joined: 19-February 08



Thank you so much

It's a not bound form
It's unbound form


why did you set DB and rs a global vars?

to use with update delete insert (multi-operation)

or should I use one variable for each

--------------------
I would like to Thank you for your help
Go to the top of the page
 
DanielPineault
post Jul 22 2019, 06:49 PM
Post#5


UtterAccess VIP
Posts: 6,771
Joined: 30-June 11



If you Set the variable in each proc, then you should Dim the variable in the Proc.

If you want to reuse the variable, which makes sense, then you should declare it globally, set it once and then reuse it.

Even better, use a self-healing variable, which sets itself, you need only use it.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th August 2019 - 11:16 AM