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

nector

Member
Local time
Today, 06:40
Joined
Jan 21, 2020
Messages
404
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
 

Minty

AWF VIP
Local time
Today, 04:40
Joined
Jul 26, 2013
Messages
10,382
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.
 

nector

Member
Local time
Today, 06:40
Joined
Jan 21, 2020
Messages
404
Okay let me try
 

nector

Member
Local time
Today, 06:40
Joined
Jan 21, 2020
Messages
404
Actual there is no subform I have also tried to use a single form its still giving me problems
 

Josef P.

Well-known member
Local time
Today, 05:40
Joined
Feb 2, 2023
Messages
886
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?
 

nector

Member
Local time
Today, 06:40
Joined
Jan 21, 2020
Messages
404
Timestamp is not there I will try to include it tonight , but the same form if used against an access table its working okay.
 

nector

Member
Local time
Today, 06:40
Joined
Jan 21, 2020
Messages
404
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

Top Bottom