ms access - AddNew function - If linked to SQL back end i get Run time error "3219" invalid operation - Stack

admin管理员组

文章数量:1122826

I have very old access database that i have inherited. It was converted from access backend tables to sql back end tables many years ago, again, inherited work. The users report that when they want to add a new record to a list, the get a run time error "3219" invalid operation. When i test it, i see that this only happens when the backend table is linked to SQL. If i have the same database with local tables it adds the new record without issue. This is the code and where it falls over.

I am expecting it to add a name to a table called People

Private Sub Driver_ID_NotInList(NewData As String, Response As Integer)
Dim ctl As Control
Dim PeopleRecs, PeopStat
Dim FirstName, LastName As String
Dim Tmp_Space As Integer

    ' Return Control object that points to combo box.
    Set ctl = Me![Driver ID]
    ' Prompt user to verify they wish to add new value.
    If MsgBox("'" & NewData & "' is not in the list. Do you want to Add it?", vbOKCancel) = vbOK Then
        ' Set Response argument to indicate that data is being added.
        Response = acDataErrAdded
        ' Add string in NewData argument to row source.
        Tmp_Space = InStr(1, NewData, " ")
        If Tmp_Space > 0 Then
            FirstName = Left(NewData, Tmp_Space - 1)
            LastName = Mid(NewData, Tmp_Space + 1, Len(NewData) - Tmp_Space)
        Else
            FirstName = ""
            LastName = NewData
        End If
        sqlstr = "SELECT [Person ID],[First name],[Last Name] from [People]"
        Set PeopleRecs = Currentdb.OpenRecordset(sqlstr, dbOpenDynaset, dbSeeChanges)
        sqlstr = "SELECT [Person ID],[Status] FROM [People Status]"
        Set PeopStat = Currentdb.OpenRecordset(sqlstr, dbOpenDynaset, dbSeeChanges)
        With PeopleRecs
            .AddNew
            ![Person ID] = NextID("Person")
            ![First Name] = FirstName
            ![Last Name] = LastName
           ** PeopStat.AddNew**
            PeopStat.[Person ID] = PeopleRecs.[Person ID]
            PeopStat.[Status] = "Car Driver"
            .Update
            .Close
            PeopStat.Update
            PeopStat.Close
        End With
    Else
    ' If user chooses Cancel, suppress error message and undo changes.
        Response = acDataErrContinue
        ctl.Undo
    End If

End Sub

I have very old access database that i have inherited. It was converted from access backend tables to sql back end tables many years ago, again, inherited work. The users report that when they want to add a new record to a list, the get a run time error "3219" invalid operation. When i test it, i see that this only happens when the backend table is linked to SQL. If i have the same database with local tables it adds the new record without issue. This is the code and where it falls over.

I am expecting it to add a name to a table called People

Private Sub Driver_ID_NotInList(NewData As String, Response As Integer)
Dim ctl As Control
Dim PeopleRecs, PeopStat
Dim FirstName, LastName As String
Dim Tmp_Space As Integer

    ' Return Control object that points to combo box.
    Set ctl = Me![Driver ID]
    ' Prompt user to verify they wish to add new value.
    If MsgBox("'" & NewData & "' is not in the list. Do you want to Add it?", vbOKCancel) = vbOK Then
        ' Set Response argument to indicate that data is being added.
        Response = acDataErrAdded
        ' Add string in NewData argument to row source.
        Tmp_Space = InStr(1, NewData, " ")
        If Tmp_Space > 0 Then
            FirstName = Left(NewData, Tmp_Space - 1)
            LastName = Mid(NewData, Tmp_Space + 1, Len(NewData) - Tmp_Space)
        Else
            FirstName = ""
            LastName = NewData
        End If
        sqlstr = "SELECT [Person ID],[First name],[Last Name] from [People]"
        Set PeopleRecs = Currentdb.OpenRecordset(sqlstr, dbOpenDynaset, dbSeeChanges)
        sqlstr = "SELECT [Person ID],[Status] FROM [People Status]"
        Set PeopStat = Currentdb.OpenRecordset(sqlstr, dbOpenDynaset, dbSeeChanges)
        With PeopleRecs
            .AddNew
            ![Person ID] = NextID("Person")
            ![First Name] = FirstName
            ![Last Name] = LastName
           ** PeopStat.AddNew**
            PeopStat.[Person ID] = PeopleRecs.[Person ID]
            PeopStat.[Status] = "Car Driver"
            .Update
            .Close
            PeopStat.Update
            PeopStat.Close
        End With
    Else
    ' If user chooses Cancel, suppress error message and undo changes.
        Response = acDataErrContinue
        ctl.Undo
    End If

End Sub

• sql
• ms-access

Share Improve this question Follow asked Nov 22, 2024 at 15:50 KateMKateM 111 silver badge11 bronze badge 1

• "this only happens when the backend table is linked to SQL" check for an insert trigger on the SQL table. Also, try to manually add a record to the SQL table via a tool like SSMS. – Bart McEndree Commented Nov 22, 2024 at 15:58

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

It probably doesn't like the mixed inserts. Try:

Dim NewID As Long
NewID = NextID("Person")

With PeopleRecs
    .AddNew
    ![Person ID] = NewID
    ![First Name] = FirstName
    ![Last Name] = LastName
    .Update
    .Close
End With
With PeopStat
    .AddNew
    ![Person ID] = NewID
    ![Status] = "Car Driver"
    .Update
    .Close
End With

本文标签：