Edit error message when updating table through code.

[Sorwen]

I have a form that is used to set user security. The form is not directly link to, but gets its data from a table. The form allows me to click a check box and it then updates the table with the new information. It works, but I get an error after every other change. I've tried several things, but I set this back to the original code.


Error:

The data has been changed.

Another user edited this record and saved the changes before you attempted to save your changes.

Re-edit the record.

Dim Con As ADODB.Connection
Dim rsTable As ADODB.Recordset
Dim strSQL As String

Option Compare Database
Option Explicit

Private Sub Form_Current()
    SetupGroups
End Sub

Private Sub Form_Open(Cancel As Integer)
    Set Con = CurrentProject.Connection
    Set rsTable = New ADODB.Recordset
    
    strSQL = ""
    strSQL = strSQL & "Select *"
    strSQL = strSQL & " From user_tbl;"
    
    rsTable.Open strSQL, Con, adOpenDynamic, adLockOptimistic
    
    sites_fsbar.Max = rsTable.Fields.Count - 1
End Sub

Private Function SetupGroups()
Dim Count As Long
Dim Count2 As Long
Dim Current As Long

Current = sites_fsbar.Value
    
    With rsTable
        .MoveFirst
        
        Do While .EOF <> True
            For Count = 1 To 6
                If (Count + (Current - 1)) < .Fields.Count Then
                    Me.Controls("site" & Count & "_lbl").Caption = .Fields.Item(Count + (Current - 1)).Name
                    Me.Controls(!UserName & "_" & Count).Value = .Fields.Item(Count + (Current - 1)).Value
                    Me.Controls("site" & Count & "_lbl").Visible = True
                    Me.Controls(!UserName & "_" & Count).Enabled = True
                Else
                    Me.Controls("site" & Count & "_lbl").Visible = False
                    Me.Controls(!UserName & "_" & Count).Enabled = False
                End If
            Next Count
            .MoveNext
        Loop
    End With

End Function

Private Sub sites_fsbar_Change()
    SetupGroups
End Sub

Private Sub UpdateTable()
Dim Count As Long
Dim Count2 As Long
Dim Current As Long

    With rsTable
        .MoveFirst
        
        Do While .EOF <> True
            For Count = 1 To 6
                .Fields.Item(Me.Controls("site" & Count & "_lbl").Caption).Value = Me.Controls(!UserName & "_" & Count).Value
            Next Count
            .Update
            .MoveNext
        Loop
    End With
End Sub

 

[Crowley16]

first, use a docmd.Setwarnings false

then put some error handling in your functions...

private function blah()
on error goto blah_Error


blah_Exit:
'function cleanup code
exit function

blah_Error:
'error handling code
resume blah_Exit

end function

--------------------
Procrastinate Now!

 

[Remou]

This occurs, as far as I know, when the record is being updated in two different places. Say, after you build the form, you alter a value, then run the UpdateTable procedure, you will be updating the table both manually and in code and will get this error. You need to save at a suitable point.

 

[SimonSellick]

I vaguely remember that this can also happen under at least two other circumstances:

1) The connected table has a bit (yes/no) field specified as null-allowed (Required = No) and is displayed as a checkbox on the form. Fix is to make the field NOT NULL.

2) The table has a text (memo) field and also has at least one column whose name contains space characters. Fix is to rename the columns.

HTH

 

[Sorwen]

Thanks everyone. I'll check all of this.

1) The connected table has a bit (yes/no) field specified as null-allowed (Required = No) and is displayed as a checkbox on the form. Fix is to make the field NOT NULL.

This is close to the situation, but it shouldn't be setting it to null. I'm going to check it all again though because that is so close to what could be happening.

 

[Sorwen]

I found what it was. Took me a while. I didn't make any sense as no matter what I did I couldn't break before the error. Forced things to yes/no rather than relying on the check box. Added error handling to trap for the error. Nothing worked. It was almost as if the code resided in the form itself. Then I remembered that this form was created originally for other code I had made which didn't work out. The forms source was set to the table so every other time the form was reading the table as (or right after I don't know which) my vba code was writing. I had totally forgotten that I had set the source. DOH!! Thanks everyone.

 

[Remou]

In other words,

Say, after you build the form, you alter a value, then run the UpdateTable procedure, you will be updating the table both manually and in code

 

[Sorwen]

Yes, sorry you were exactly right. I read what you wrote, but it didn't stick because I didn't think it though. *sheepish*