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
> Code Corrupting Records. Curious, Not, Access 2016    
 
   
ITdarkside
post Sep 21 2017, 04:09 PM
Post#1



Posts: 61
Joined: 19-May 16



Hi all,

I'm having a problem with a piece of code and I'm more interested in understanding why the problem causes such a big headache. I do know what code is causing the fault and how to fix it.

CODE
Private Sub cmdApvAndImp_Click()
Dim x As Integer
Dim y As Integer
Dim Nbr As Integer
Dim yy As Integer
Dim NextCONbr As String
Dim strSQL As String
Dim strChgs As String
Dim db As DAO.Database


yy = Right(Date, 2) 'gets the last two numbers of the year
0 Nbr = DMax("Nbr", "qprocNewCONbr", "Yr=" & yy) 'Finds the biggest CO number for the current year
Nbr = Nbr + 1 'adds 1 to the current CO number
NextCONbr = Nbr & "-" & yy 'concatenates the new CO number
strSQL = "Insert Into tblMainCO (CONbr,DateIni,Type,Plant,Background,Division,ItemDescription,Duration,Initia
tor,Status,Authorization) Values ('" & NextCONbr & "', '" & Me!DateSubmitted & "', '" & Me!Type & "' ,'" & Me!Plant & "', '" & Me!Reason & "', '" & Me!Division & "', '" & Me!Description & "', '" & Me!Duration & "', '" & Me!Initiator & "','In Process','" & Me!ApprovedBy & "')"
strChgs = "Insert Into tblChangeComponent (Plant, ComponentNbr, ChangeFrom, ChangeTo, EffectiveDate) SELECT tblChanges.PlantID, tblChanges.ItemNumber, tblChanges.ChangeFrom, tblChanges.ChangeTo, tblChanges.EffectiveDate FROM tblChanges WHERE tblChanges.COInputID = " & Me!txtCOInputID
Set db = DBEngine(0)(0)

x = MsgBox("This will create a new record to Change Order Database. Are you sure you want to do this?", vbYesNo, "Attention")
If x = 6 Then 'user clicks Yes
    y = MsgBox("The next available CO number is " & NextCONbr & ". Do you want to use this number?" & vbCrLf & "Press 'No' to assign a new number. Press 'Cancel' to stop import process", vbYesNoCancel, "Attention")
    If y = 6 Then ' user clicks yes


Then comes this line, which changes the status of my record in a table that lives on a separate back end and is linked to my front end.

CODE
DoCmd.RunSQL "Update InputTable Set CONbr = '" & NextCONbr & "', Status = 'Accepted' Where InputTable.ID = " & Me!txtID & ""


CODE
strSQL = "Insert Into tblMainCO (" & vbNewLine & _
         "   CONbr,DateIni,Type,Plant,Background,Division," & vbNewLine & _
         "   ItemDescription,Duration,Initiator,Status,Authorization" & vbNewLine & _
         ") Values (" & vbNewLine & _
         "   '" & NextCONbr & "', " & vbNewLine & _
         "   '" & Me!DateSubmitted & "', " & vbNewLine & _
         "   '" & Me!Type & "', " & vbNewLine & _
         "   '" & Me!Plant & "', " & vbNewLine & _
         "   '" & Me!Reason & "', " & vbNewLine & _
         "   '" & Me!Division & "', " & vbNewLine & _
         "   '" & Me!Description & "', " & vbNewLine & _
         "   '" & Me!Duration & "', " & vbNewLine & _
         "   '" & Me!Initiator & "', " & vbNewLine & _
         "   'In Process', " & vbNewLine & _
         "   '" & Me!ApprovedBy & "'" & vbNewLine & _
         ")"
Debug.Print strSQL
With db
  .Execute strSQL, dbFailOnError
  MsgBox .RecordsAffected & " Records were appended." 'Tells you if the operation was a success
  '.Execute "Update InputTable Set Status = 'Accepted' Where InputTable.ID = " & Me!txtID, dbFailOnError
End With
'DoCmd.RunSQL "Update InputTable Set Status = 'Accepted' Where InputTable.ID = " & Me!txtID & ""


Now comes the problem.

The acSaveYes property of the next code line causes a write conflict box to pop up with the following buttons:
1) Save Record
2) Copy to Clipboard
3) Drop Changes

CODE
DoCmd.Close acForm, "frmReviewing", acSaveYes
DoCmd.OpenForm "frmMainCO", acNormal, "qryFRM_MainCO", "CONbr='" & NextCONbr & "'"
        
        Set db = Nothing


If I click Save Record at this point, after the status for the record was changed by the DoCmd.RunSQL earlier, the record will become corrupted: All text in the record turns to chinese characters, the search index is lost and the corrupt record can't be deleted until that backend has undergone a Compact and Repair. I also believe that this code caused the front end to corrupt last week. It was so bad I had to have my IT dept do a file restore to get the database back into working order.

Any idea as to why the corruption happens? Is this normal behavior for Access?
Go to the top of the page
 
ITdarkside
post Sep 21 2017, 04:36 PM
Post#2



Posts: 61
Joined: 19-May 16



On further review I've identified the real problem here.

I have an OnLoad event that changes the status of the record from 'New' to 'Seen' when it's first looked at by a user on our end. If the form isn't closed between this status change, and running the import code shown above, then there is a write conflict because the 'Seen' Status that was changed is again being overwritten.

Here is the code for the OnLoad event.

CODE
Private Sub Form_Load()
If Me!Status = "New" Then
    Me!Status.Value = "Seen"
    DoCmd.Save
    Else
    Exit Sub
End If
End Sub


Any ideas on how to circumvent the problem without closing the form between updates to the status?
Go to the top of the page
 
doctor9
post Sep 21 2017, 04:38 PM
Post#3


UtterAccess Editor
Posts: 17,916
Joined: 29-March 05
From: Wisconsin


ITdarkside,

This is a wild stab in the dark, but maybe try replacing DoCmd.Save with Me.Dirty = False.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
cheekybuddha
post Sep 21 2017, 04:45 PM
Post#4


UtterAccess VIP
Posts: 9,275
Joined: 6-December 03
From: Telegraph Hill


Do you have Option Explicit declared at the top of every code module? If so, does your code compile?

This line looks odd:
CODE
0 Nbr = DMax("Nbr", "qprocNewCONbr", "Yr=" & yy) 'Finds the biggest CO number for the current year


This line:
CODE
DoCmd.Close acForm, "frmReviewing", acSaveYes

does not save the data in the form - it saves design changes (which should not be applicable here)

Why are you bothering with DoCmd.RunSQL? You have already set a database object. Use
CODE
db.Execute "Update InputTable Set CONbr = '" & NextCONbr & "', Status = 'Accepted' Where InputTable.ID = " & Me!txtID, dbFailOnError


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
gemmathehusky
post Oct 13 2017, 07:47 AM
Post#5


UtterAccess VIP
Posts: 4,432
Joined: 5-June 07
From: UK


CODE
Private Sub Form_Load()
If Me!Status = "New" Then
    Me!Status.Value = "Seen"
    DoCmd.Save
    Else
    Exit Sub
End If
End Sub


I suspect docmd.save does not save the record. It probably saves form design changes.
{confirmed with a bit of research}

you want
RunCommand acCmdSaveRecord OR
me.dirty = false as already posted.

from another forum- see the last post.
https://access-programmers.co.UK/forums/sho...ad.php?t=115693

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 02:59 PM