Solved Changing Prices to SQL Server database via a form fail (1 Viewer)

nector

Member
Local time
Today, 17:15
Joined
Jan 21, 2020
Messages
465
Hi Experts!

I'm trying to use an access form to change the existing prices with BE in SQL Server, I'm getting an error , what should I do?

VBA code I'm using below:

Code:
Private Sub CheckStatus_Click()
Dim strSql As String
'make sure any unsaved changes are saved to avoid conflicts:
    DoCmd.RunCommand (acCmdSaveRecord)
    'build the update query SQL string:
    strSql = "UPDATE [tblPricing]" _
    & " SET [FinalStatus]=Not NZ([FinalStatus],0)"
    'if the form has an active filter - include it:
    If Me.Filter <> "" And Me.FilterOn = True Then
        strSql = strSql & " WHERE " & Me.Filter
    End If
    'run the update query SQL string:
    CurrentDb.Execute strSql
    'refresh the form so that changes in the table "bubble-up" to the form:
    Me.Refresh
exit_sub:
End Sub

Error message attached on picture



Price Change saving.jpg
 
The error is telling you the problem, I suspect you are on the record you are updating in code.

If this is a sub-form showing the same record, remember you have two copies of the same data, the changes in both need to be reflected and saved otherwise you get a conflict. The other option is to make one of the forms recordsource a snapshot and not updateable.
 
Okay let me try
 
Actual there is no subform I have also tried to use a single form its still giving me problems
 
At which line of code does the error occur?
Is Me.Refresh sufficient or must Me.Requery be used?

Or try Me.Dirty = False instead of DoCmd.RunCommand acCmdSaveRecord

Otherwise (if you use float data fields or something similar):
Is there a timestamp field in the table?
 
Timestamp is not there I will try to include it tonight , but the same form if used against an access table its working okay.
 
At which line of code does the error occur?
Is Me.Refresh sufficient or must Me.Requery be used?

Or try Me.Dirty = False instead of DoCmd.RunCommand acCmdSaveRecord

Otherwise (if you use float data fields or something similar):
Is there a timestamp field in the table?
Otherwise (if you use float data fields or something similar):
Is there a timestamp field in the table?

Many thanks sir, the timestamp field has sorted out the problem you are a really gift to us.
 

Users who are viewing this thread

Back
Top Bottom