Results 1 to 7 of 7
  1. #1
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81

    runtime error 2448, can't update this field

    I get "runtime error 2448, You can't assign a value to this object" when I try to update a field in an existing record. This same sub successfully assigns the value when the record is new, and I'm ripping out my hair to figure out why.



    The purpose of this code is to record the previous value into an audit trail table to record changes to the record. The click event records the value of the field in a variable "oldInitAct", and then here in the AfterUpdate the new value is compared to the old to see if it changed. If it has changed it writes the value to the audit table in the addComment () function, and then tries to make the change. If oldInitAct is Null, then the audit route is skipped and the value is successfully updated. If the value has changed then it fails to update the field with the new value using similar code, [INITIAL_ACTIVITY] = InitialActivity.Value

    I have tried:


    1. Using me.INITIAL_ACTIVITY = InitialActivity.Value
    2. Using me!INITIAL_ACTIVITY = InitialActivity.Value
    3. Moving the three variations of the statement before the IF THEN comparison, just to see if it would work
    4. Moving it to a different function.

    I get 2448 every time. Why does this happen?

    Code:
    Private Sub initialActivity_AfterUpdate()
       Dim localInitAct As Variant
    
    
       localInitAct = InitialActivity.Value
    
    
       If IsNull(oldInitAct) Then
          GoTo skipCheck
       Else
          If localInitAct <> oldInitAct Then
             If MsgBox("Initial Activity already has a value of " & oldInitAct & " Would you like to update it? ", vbYesNo + vbQuestion) = vbYes Then
                [INITIAL_ACTIVITY] = InitialActivity.Value
                commentString = ("Initial Activity was changed from " & oldInitAct)
                addComment (commentString)
                GoTo endMe
             Else
                GoTo skipCheck
             End If
          End If
       End If
    
    
    skipCheck:
       MsgBox (InitialActivity.Value)
       [INITIAL_ACTIVITY] = InitialActivity.Value
       MsgBox ("DONE")
    endMe:
    End Sub
    Edit: the field is defined as "Numeric", and running MsgBox (IsNumeric(InitialActivity.Value)) returns "True"

  2. #2
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81
    This is probably dumb, but I'd love to see a better way. This is what I did to fix it:

    Code:
    Private Sub initialActivity_AfterUpdate()
       Dim localInitAct As Variant
       Dim StrSql As String
       
       localInitAct = InitialActivity.Value
    
    
       If IsNull(oldInitAct) Then
          GoTo skipCheck
       Else
          If localInitAct <> oldInitAct Then
             If MsgBox("Initial Activity already has a value of " & oldInitAct & " Would you like to update it? ", vbYesNo + vbQuestion) = vbYes Then
                StrSql = " INSERT INTO INDUCTION_DATA(INITIAL_ACTIVITY) VALUES " & "('" & localInitAct & "' );"
                DoCmd.RunSQL StrSql
                GoTo endMe
             Else
                GoTo skipCheck
             End If
          End If
       End If
    
    
    skipCheck:
       [INITIAL_ACTIVITY] = localInitAct
    
    
    endMe:
    End Sub

    EDIT: Maybe that should be an UPDATE. Does anyone know?
    EDIT2: Actually that didn't work, it created a new record. I'm looking up the correct SQL now, I'll correct this post when I get it

  3. #3
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Have you looked at the .OldValue Property?
    https://docs.microsoft.com/en-us/off...xtbox.oldvalue

  4. #4
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81
    This works better. There are a lot of 2448 questions on Google, but not a lot of answers. Hopefully this works for someone in the future.

    batchNo is unique and could be used as a secondary key in my case.


    Code:
    Private Sub initialActivity_AfterUpdate()
       Dim localInitAct As Variant
       Dim StrSql As String
       
       localInitAct = InitialActivity.Value
    
    
       If IsNull(oldInitAct) Then
          GoTo skipCheck
       Else
          If localInitAct <> oldInitAct Then
             If MsgBox("Initial Activity already has a value of " & oldInitAct & " Would you like to update it? ", vbYesNo + vbQuestion) = vbYes Then
                StrSql = " UPDATE INDUCTION_DATA SET INITIAL_ACTIVITY = '" & localInitAct & "' WHERE BATCH='" & batchNo & "';"
                DoCmd.RunSQL StrSql
                GoTo endMe
             Else
                'vbNo
                GoTo endMe
             End If
          End If
       End If
    
    
    skipCheck:
       [INITIAL_ACTIVITY] = localInitAct
    
    
    endMe:
    End Sub

  5. #5
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81
    @ moke123 :

    omg, I spent all day on this. I didn't know there was a tool for it! Well, those docs say its for a bound control, and this is an unbound control that I set the value of. Actually, I'm setting the field of a table. Thank you I'll play with it, but the SQL works fine for me.

  6. #6
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    you might be able to simplify that to

    Code:
    Private Sub initialActivity_AfterUpdate()
    
        Dim localInitAct As Variant
        Dim StrSql As String
    
    
        localInitAct = InitialActivity
    
    
        If IsNull(oldInitAct) Then
            [INITIAL_ACTIVITY] = localInitAct
        ElseIf localInitAct <> oldInitAct Then
            If MsgBox("Initial Activity already has a value of " & oldInitAct & " Would you like to update it? ", vbYesNo + vbQuestion) = vbYes Then
                StrSql = "UPDATE INDUCTION_DATA SET INITIAL_ACTIVITY = """ & localInitAct & """ WHERE BATCH= """ & batchNo & """"
                CurrentDb.Execute StrSql, dbFailOnError
            End If
        End If
    
    
    End Sub
    Where does the value of oldInitAct come from? Is that a field on your form? If so I would add the me keyword to make it clear.
    Also the
    CurrentDb.Execute method will skip any prompts the runsql method throws without using Set Warnings.

  7. #7
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81
    Nice, thanks!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Run-time error: 2448
    By Shadows1989 in forum Forms
    Replies: 10
    Last Post: 11-01-2019, 09:00 AM
  2. Replies: 0
    Last Post: 05-16-2012, 01:48 PM
  3. Error 2448
    By gtrudel in forum Programming
    Replies: 9
    Last Post: 07-11-2011, 07:30 PM
  4. Replies: 3
    Last Post: 04-15-2010, 09:43 AM
  5. runtime error 2448
    By ds_8805 in forum Forms
    Replies: 3
    Last Post: 04-14-2010, 07:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums