field cannot be updated

**mshmyob** · Mar 28 '12, 07:11 PM

Worked fine before what?

Where is the code?

Show the table design also.

Is that the full message or did you truncate the message because you did not feel like typing it all in?

A little more info would be helpful.

cheers,

**rovral** · Mar 28 '12, 07:52 PM

It just stopped working and started giving me the error, didn't change anything. Here is the details.

Table design:

Building_ID (Autonumber, primary key)
Location_Addres s (Memo)
Location (Text)
Province (Text)
Location_Distri ct_ID (Number, foreign key)
Trans_Date (Date/Time)
Selling_Price (Currency)
Selling_Terms (Text)
Land_Use (Text)
Legal_Desc (Memo)
Linc_No (Number)
Site_Size_SF (Number)
Site_Size_Acres (Number)
Site_Size_Condo (Text)
Land_Value_Est (Currency)
Vendor (Memo)
Purchaser (Memo)
Net_Income (Currency)
Gross_Income (Currency)
Operating_Expen ses (Currency)
Comments (Memo)
Building_Type_I D (Number, Foreign Key)
Dev_Desc (Memo)
Dev_Name (Memo)
Tenant_Owner (Text)
Other_SF (Number)
Retail_SF (Number)
Office_SF (Number)
Warehouse_SF (Number)
Storage_SF (Number)
Mezzanine_SF (Number)
Basement_SF (Number)
Residential_SF (Number)
Stories (Number)
Net_Rentable_Ar ea (Number)
Const_Year (Text)
No_Units (Number)
Selected (Yes/No)
Lease_Only (Yes/No)

Form code:

Code:

Option Compare Database
Option Explicit

Private Sub cboDistrict_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

    strMsg = "'" & NewData & "' is not an available Sub-District " & vbCrLf & vbCrLf
    strMsg = strMsg & "Do you want to associate the new Name to the current Sub-District?"
    strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type it."
    
    If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
        Response = acDataErrContinue
    Else
        Set db = CurrentDb
        Set rs = db.OpenRecordset("tblLocation_District", dbOpenDynaset)
        On Error Resume Next
        rs.AddNew
            rs!Location_District = NewData
        rs.Update
        
        If Err Then
            MsgBox "An error occurred. Please try again."
            Response = acDataErrContinue
        Else
            Response = acDataErrAdded
        End If
    End If

Set rs = Nothing
Set db = Nothing
End Sub

Private Sub cboLand_Use_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

    strMsg = "'" & NewData & "' is not an available Land Use " & vbCrLf & vbCrLf
    strMsg = strMsg & "Do you want to associate the new Name to the current Land Use?"
    strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type it."
    
    If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
        Response = acDataErrContinue
    Else
        Set db = CurrentDb
        Set rs = db.OpenRecordset("tblLand_Use_List", dbOpenDynaset)
        On Error Resume Next
        rs.AddNew
            rs!Land_Use = NewData
        rs.Update
        
        If Err Then
            MsgBox "An error occurred. Please try again."
            Response = acDataErrContinue
        Else
            Response = acDataErrAdded
        End If
    End If

Set rs = Nothing
Set db = Nothing
End Sub

Private Sub cboLocation_BeforeUpdate(Cancel As Integer)
If IsNull(cboLocation) Then
        MsgBox "You must choose a Location", 48, "No Data Entered"
        Cancel = True
    End If
End Sub

Private Sub cboLocation_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

    strMsg = "'" & NewData & "' is not an available Location " & vbCrLf & vbCrLf
    strMsg = strMsg & "Do you want to associate the new Name to the current Location?"
    strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type it."
    
    If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
        Response = acDataErrContinue
    Else
        Set db = CurrentDb
        Set rs = db.OpenRecordset("tblLocation_List", dbOpenDynaset)
        On Error Resume Next
        rs.AddNew
            rs!Location = NewData
        rs.Update
        
        If Err Then
            MsgBox "An error occurred. Please try again."
            Response = acDataErrContinue
        Else
            Response = acDataErrAdded
        End If
    End If

Set rs = Nothing
Set db = Nothing
End Sub

Private Sub cboProvince_BeforeUpdate(Cancel As Integer)
If IsNull(cboProvince) Then
        MsgBox "You must choose a province", 48, "No Data Entered"
        Cancel = True
    End If
End Sub

Private Sub cboTenant_AfterUpdate()
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmLease_Data_Entry"
stLinkCriteria = "[Building_ID]=" & Forms!frmData_Entry.txtID
    
    If cboTenant.Value = "Tenant Occupied" Or cboTenant.Value = "SubLease" Then
        cmdLease.Enabled = True
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
        DoCmd.OpenForm stDocName, , , stLinkCriteria
        Forms!frmLease_Data_Entry.txtID.DefaultValue = Forms!frmData_Entry.txtID
    Else: cmdLease.Enabled = False
    End If
End Sub

Private Sub cboType_AfterUpdate()

    txtUnits.Enabled = Me.cboType.Column(2)
    txtPrice_Unit.Enabled = Me.cboType.Column(2)

End Sub

Private Sub cboType_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg, strMsg2 As String
Dim i As Integer

    strMsg = "'" & NewData & "' is not an available building type " & vbCrLf & vbCrLf
    strMsg = strMsg & "Do you want to associate the new name to the current type?"
    strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type it."

    If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new building type?") = vbNo Then
        Response = acDataErrContinue
    Else
        Set db = CurrentDb
        Set rs = db.OpenRecordset("tblBuilding_Type_List", dbOpenDynaset)
        On Error Resume Next
        rs.AddNew
            rs.Fields(1) = NewData
        For i = 2 To rs.Fields.Count - 1
            If rs(i).Name = "Enable_Units" Then
               strMsg2 = "Enable No. of Rooms / Units & Price per Room / Unit?"
               If MsgBox(strMsg2, vbQuestion + vbYesNo, "Enable Fields?") = vbYes Then
                  rs(i).Value = True
               Else
                  rs(i).Value = False
               End If
            End If
        Next i
        rs.Update

        If Err Then
            MsgBox "An error occurred. Please try again."
            Response = acDataErrContinue
        Else
            Response = acDataErrAdded
        End If
    End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

Private Sub chkLease_AfterUpdate()
If chkLease = True Then
    txtPrice.Enabled = False
    txtDate.Enabled = False
    txtGross.Enabled = False
    txtNet.Enabled = False
    txtGIM.Enabled = False
    txtOAR.Enabled = False
    txtLand.Enabled = False
    txtPurchaser.Enabled = False
    txtExpense.Enabled = False
Else
    txtPrice.Enabled = True
    txtDate.Enabled = True
    txtGross.Enabled = True
    txtNet.Enabled = True
    txtGIM.Enabled = True
    txtOAR.Enabled = True
    txtLand.Enabled = True
    txtPurchaser.Enabled = True
    txtExpense.Enabled = True
End If

End Sub

Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_cmdDelete_Click:
    Exit Sub

Err_cmdDelete_Click:

    Select Case Err
    Case 2501
        'action cancelled
        Resume Exit_cmdDelete_Click
    Case Else
        'handle unexpected errors here
        MsgBox Err.Description
        Resume Exit_cmdDelete_Click
    End Select
End Sub

Private Sub cmdLease_Click()
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmLease_Data_Entry"
stLinkCriteria = "[Building_ID]=" & Forms!frmData_Entry.txtID
    
    If cboTenant.Value = "tenant Occupied" Or cboTenant.Value = "SubLease" Then
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
        DoCmd.OpenForm stDocName, , , stLinkCriteria
        Forms!frmLease_Data_Entry.txtID.DefaultValue = Forms!frmData_Entry.txtID
    End If
   
End Sub

Private Sub cmdMain_Click()
On Error GoTo Err_cmdMain_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmMain_Menu"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
    DoCmd.Close acForm, "frmData_Entry"

Exit_cmdMain_Click:
    Exit Sub

Err_cmdMain_Click:
    MsgBox Err.Description
    Resume Exit_cmdMain_Click
End Sub

Private Sub cmdNew_Click()
On Error GoTo Err_cmdNew_Click


   
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    DoCmd.GoToRecord , , acNewRec
    txtAddress.SetFocus

   
Exit_cmdNew_Click:
    Exit Sub

Err_cmdNew_Click:
    MsgBox Err.Description
    Resume Exit_cmdNew_Click
End Sub

Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "rptData_Entry"
    
    stLinkCriteria = "tblCom_Buildings.[Building_ID]=" & Me![txtID]
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Exit_cmdPrint_Click:
    Exit Sub

Err_cmdPrint_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrint_Click
End Sub


'Private Sub Form_AfterUpdate()
'If IsNull(cboLocation) Or IsNull(cboProvince) Then
'        MsgBox "You must choose a location or province", 48, "No Data Entered"
'        Cancel = True
'    End If
'If IsNull(cboDistrict) Then
'    MsgBox "Please choose n/a for District rather than leaving blank", 48, "No Data Entered"
'    Cancel = True
'    End If
'End Sub

Private Sub Form_Close()
 If Me.Dirty Then
    Me.Dirty = False
 End If
 
End Sub

Private Sub Form_Current()
If cboTenant.Value = "Tenant Occupied" Or cboTenant.Value = "SubLease" Then
    cmdLease.Enabled = True
Else: cmdLease.Enabled = False
End If

    With Me
       If IsNull(.cboType.Column(2)) Then
           'do nothing
        Else
           txtUnits.Enabled = Me.cboType.Column(2)
           txtPrice_Unit.Enabled = Me.cboType.Column(2)
        End If
    End With

If chkLease = True Then
    txtPrice.Enabled = False
    txtDate.Enabled = False
    txtGross.Enabled = False
    txtNet.Enabled = False
    txtGIM.Enabled = False
    txtOAR.Enabled = False
    txtLand.Enabled = False
    txtPurchaser.Enabled = False
    txtExpense.Enabled = False
Else
    txtPrice.Enabled = True
    txtDate.Enabled = True
    txtGross.Enabled = True
    txtNet.Enabled = True
    txtGIM.Enabled = True
    txtOAR.Enabled = True
    txtLand.Enabled = True
    txtPurchaser.Enabled = True
    txtExpense.Enabled = True
End If
End Sub

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
    
    DoCmd.Maximize
    
    If Me.FilterOn = True Then
        cmdClose.Visible = True
    Else
        cmdClose.Visible = False
    End If
    
If cboTenant.Value = "Tenant Occupied" Or cboTenant.Value = "SubLease" Then
   cmdLease.Enabled = True
Else
   cmdLease.Enabled = False
End If

If chkLease = True Then
    txtPrice.Enabled = False
    txtDate.Enabled = False
    txtGross.Enabled = False
    txtNet.Enabled = False
    txtGIM.Enabled = False
    txtOAR.Enabled = False
    txtLand.Enabled = False
    txtPurchaser.Enabled = False
    txtExpense.Enabled = False
Else
    txtPrice.Enabled = True
    txtDate.Enabled = True
    txtGross.Enabled = True
    txtNet.Enabled = True
    txtGIM.Enabled = True
    txtOAR.Enabled = True
    txtLand.Enabled = True
    txtPurchaser.Enabled = True
    txtExpense.Enabled = True
End If

Exit_Form_Open:
    Exit Sub

Err_Form_Open:
   Select Case Err
    Case 3024
        'action cancelled
        Cancel = True
    Case Else
        'handle unexpected errors here
        MsgBox Err.Description
        Resume Exit_Form_Open
    End Select
End Sub
Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_cmdSave_Click:
    Exit Sub

Err_cmdSave_Click:
    MsgBox Err.Description
    Resume Exit_cmdSave_Click
    
End Sub

Private Sub txtAcres_AfterUpdate()
If txtAcres <> "" Then
    txtSF.Value = txtAcres * 43560
End If
End Sub

Private Sub txtAddress_BeforeUpdate(Cancel As Integer)
Dim strMsg1, strMsg2, strMsg3, strMsg4, strMsg5 As String
Dim strFind As Variant
Dim rs As DAO.Recordset
Dim strCriteria, strAddress As String

strMsg1 = "This address already exists."
strMsg2 = "Click OK to be taken to the record to verify." & Chr$(13) & Chr$(10)
strMsg3 = "If the record you are trying to enter already exists, do nothing."
strMsg4 = "Otherwise add a new record and select no to continue."
strMsg5 = strMsg1 & Chr$(13) & Chr$(10) & strMsg2 & Chr$(13) & Chr$(10) & strMsg3 & Chr$(13) & Chr$(10) & strMsg4

Set rs = Me.RecordsetClone
strAddress = Me.[txtAddress].Value
strCriteria = "[Location_Address]=" & "'" & strAddress & "'"

If DCount("[Location_Address]", "tblCom_Buildings", strCriteria) > 0 Then
        If MsgBox(strMsg5, vbInformation + vbYesNo, "Duplicate Address") = vbYes Then
            Me.Undo
            rs.FindFirst strCriteria
            Me.Bookmark = rs.Bookmark
            
            rs.Close
            Set rs = Nothing
        Else
            Exit Sub
        End If
End If
End Sub

Private Sub txtSF_AfterUpdate()
If txtSF <> "" Then
    txtAcres.Value = txtSF / 43560
End If
End Sub
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click


    DoCmd.Close acForm, "frmData_Entry"

Exit_cmdClose_Click:
    Exit Sub

Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click
    
End Sub

**rovral** · Mar 28 '12, 08:12 PM

I fixed the problem, it turns out that I had a calculated field that I set the default value to 0 on. Apparently you can't do this. Now the form works.

Thanks for your reply's

**mshmyob** · Mar 28 '12, 10:00 PM

ok............. .... (don't see why a default can't be 0 but what the hey....)

cheers

**rovral** · Mar 28 '12, 10:10 PM

Here is Microsoft's explanation http://support.microsoft.com/kb/289659

**mshmyob** · Mar 28 '12, 10:54 PM

Makes sense with this added piece of information of how your form and controls are bound.

cheers,