The data has changed "Bit Feild" / Cant assign a value to this object (1 Viewer)

[tucker61]

Morning all, on my recent form, I am getting 2 errors when running the code.

vba below -
Access front end - SQL Server back end.
Me.chkApproved = true is a tick box in Access, Bit field in SQL Server, Default value is set as "0" but shows in SQL Server as ((0)). I get the error saying that the data has changed Error -2147352567 but when i progress through the VBA line by line - it appears to work.

Me.Approved_By = Get long name gives me the cannot assign a value to this field. Again - seems to work when progressing line by line, but when running automatically after press of a button it fails.
I have a timestamp on the table, and the table has been relinked.

        Me.chkApproved = True
        me.Approved_By = GetLongName

 

[Minty]

If you have any existing data where that field is set to null you will encounter a problem.
Have a read here:

Microsoft Access tips: Code accompanying article - Has the record been printed?

A workaround for a bug where Microsoft Access crashes or fails to execute queries with Yes/No fields.

allenbrowne.com

I avoid using Bit fields altogether in SQL server and just use an Integer field instead.

 

[tucker61]

If you have any existing data where that field is set to null you will encounter a problem.
Have a read here:

Microsoft Access tips: Code accompanying article - Has the record been printed?

A workaround for a bug where Microsoft Access crashes or fails to execute queries with Yes/No fields.

allenbrowne.com

I avoid using Bit fields altogether in SQL server and just use an Integer field instead.

NO Nulls, just True and False. Thanks for the advise on Integer Field - will look at this next.

 

[tucker61]

Changed to Int Data type, relinked, Changed code to

Me.chkApproved = 1

, still getting data has been changed, and this line is highlighted.

Noted that if i come out of the form, and then go back into it - the code runs with no issues.

 

[Minty]

What event are you running this in? Can you show the whole code for the event?

 

[tucker61]

Private Sub btnProgress_Click()
'On Error GoTo Handler
Dim TargetFile As String
Dim MessageBody As String
Dim tolist As String
Dim cclist As String
Dim AttArray()


tbapproverlbl.Visible = True
Me!SubfrmCSRChargeDetail.Enabled = False

    If MsgBox("You cannot reverse this process. Are you sure?", vbExclamation + vbYesNo + vbDefaultButton2, "Confirm!") <> vbYes Then
        Exit Sub
    Else
         AttemptSave
         Me.chkApproved = 1
         'On Error Resume Next
        btnQuit.SetFocus
        Approved_By = GetLongName
        tbApproveTime = Now()
        tbApproverText.Visible = True
        tbapproverlbl.Visible = False
        btnUnapprove.Visible = True
        btnProgress.Visible = False
        AttemptSave

        Select Case optWhoPays
            Case 0:
                tolist = ""
                MsgBox "Thanks, The Charge has been logged as a Very Group Charge.", vbCritical + vbOKOnly, "No Charge!"
            Case 1:
                tolist = "xxx@Gmail.com"
    cclist = ""
        '    Case 2:
        '        Recipient = "xx@Gmail.com"
        End Select
        If tolist <> "" Then
            If Nz(DCount("*", "tblQCCsrChargesDetail", "Job_ID=" & Job_ID & " AND [Quote/Charge]=1"), 0) > 0 Then
             Sup_Name = Nz(DLookup("Sup_Desc", "tblSuppliers", "Sup_Code='" & Nz(CboxSup_Code, "") & "'"), "")
                TargetFile = Environ("Temp") & "\Job " & Format(Forms!frmCSRCharges.tbjobid, "00000000") & " Charges.pdf"
                ReDim Preserve AttArray(1)
                AttArray(0) = TargetFile
                DoCmd.OutputTo acOutputReport, "rptCsrCharges", "PDFFormat(*.pdf)", TargetFile, False, "", 0, acExportQualityPrint
                MessageBody = "Please find enclosed charges for CSR Job Number. " & Format(Forms!frmCSRCharges.tbjobid, "00000000") & vbNewLine & vbNewLine & "Regards" & vbNewLine & tbApprover
                OutlookEmail tolist, cclist, "CSR Penalty for Supplier - " & Trim(Sup_Code) & " - " & Sup_Name & ":- Job Number " & Format(Forms!frmCSRCharges.tbjobid, "00000000"), MessageBody, AttArray, True

            Else
                MsgBox "There are no Charge Details to email!", vbCritical + vbOKOnly, "No Details"
            End If

        End If
    End If
    Exit Sub
Handler:
        Call LogError(Err.Number, Err.Description, "frmNCChargesProgresstoFinance", Forms!frmCSRCharges.tbjobid)
        Forms!frmCSRCharges.Visible = True
        Exit Sub
End Sub

 

[Minty]

Okay what does
AttemptSave
do I suspect that is the culprit

 

[tucker61]

Okay what does
AttemptSave
do I suspect that is the culprit

DoCmd.RunCommand acCmdSaveRecord

 

[Minty]

You have changed the data then saved it, then are changing the data again.
Try requerying the form before making the changes and/or removing the attempted save.

You only need to save the changes once.

 

[tucker61]

Appreciate if someone can have a look at the enclosed and advise.

Add new record, Select Supplier Code, add a Comment, Select charge reason on sub form, add a Qty, then approve and send to finance. Fails at ChkApproved = 1

 

[tucker61]

You have changed the data then saved it, then are changing the data again.
Try requerying the form before making the changes and/or removing the attempted save.

You only need to save the changes once.

Rem out this attempt save code did not resolve the issue.

 

[Minty]

Your problem is caused by the underlying change of data when you update the sub-form.
You are changing the data in the main record from the update query on the change of the subform data.

It's unnecessary as well - you are storing the data twice and having to maintain the updates in two places.
You can use a simple aggregate query to get the claim value at any time and it will be correct.

In your button code add

Me.Recordset.Requery

Before doing anything else and it will work.

 

[tucker61]

Thanks. What code should I remove from sub form that is unnecessary

 

[Minty]

The UpdateClaim Value code - you can calculate that at any time from the subdata records,

If you are using SQL server you can build a view that does that and simply link that to any queries you need to display it in, and it will always be up to date. No need for the claim cost field in the main table at all.

To be honest, the approved Check box is completely redundant as well, you have an approved by and approved DateTime stamp, so why bother with the approved check box as well. The ApprovedTime tells you it's approved and when - it does a more complete job.