By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,052 Members | 1,456 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,052 IT Pros & Developers. It's quick & easy.

field cannot be updated

P: 28
All of a sudden, I get "the field cannot be updated" error when trying to add a new record in my data entry form. The form worked fine before. I doesn't matter what field I enter data into, I get the error. I click OK and then it lets me continue entering information. Puzzled as to why this is happening. Any ideas?
Mar 28 '12 #1
Share this Question
Share on Google+
6 Replies


mshmyob
Expert 100+
P: 903
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,
Mar 28 '12 #2

P: 28
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_Address (Memo)
Location (Text)
Province (Text)
Location_District_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_Expenses (Currency)
Comments (Memo)
Building_Type_ID (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_Area (Number)
Const_Year (Text)
No_Units (Number)
Selected (Yes/No)
Lease_Only (Yes/No)

Form code:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cboDistrict_NotInList(NewData As String, Response As Integer)
  5. Dim db As DAO.Database
  6. Dim rs As DAO.Recordset
  7. Dim strMsg As String
  8.  
  9.     strMsg = "'" & NewData & "' is not an available Sub-District " & vbCrLf & vbCrLf
  10.     strMsg = strMsg & "Do you want to associate the new Name to the current Sub-District?"
  11.     strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type it."
  12.  
  13.     If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
  14.         Response = acDataErrContinue
  15.     Else
  16.         Set db = CurrentDb
  17.         Set rs = db.OpenRecordset("tblLocation_District", dbOpenDynaset)
  18.         On Error Resume Next
  19.         rs.AddNew
  20.             rs!Location_District = NewData
  21.         rs.Update
  22.  
  23.         If Err Then
  24.             MsgBox "An error occurred. Please try again."
  25.             Response = acDataErrContinue
  26.         Else
  27.             Response = acDataErrAdded
  28.         End If
  29.     End If
  30.  
  31. Set rs = Nothing
  32. Set db = Nothing
  33. End Sub
  34.  
  35. Private Sub cboLand_Use_NotInList(NewData As String, Response As Integer)
  36. Dim db As DAO.Database
  37. Dim rs As DAO.Recordset
  38. Dim strMsg As String
  39.  
  40.     strMsg = "'" & NewData & "' is not an available Land Use " & vbCrLf & vbCrLf
  41.     strMsg = strMsg & "Do you want to associate the new Name to the current Land Use?"
  42.     strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type it."
  43.  
  44.     If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
  45.         Response = acDataErrContinue
  46.     Else
  47.         Set db = CurrentDb
  48.         Set rs = db.OpenRecordset("tblLand_Use_List", dbOpenDynaset)
  49.         On Error Resume Next
  50.         rs.AddNew
  51.             rs!Land_Use = NewData
  52.         rs.Update
  53.  
  54.         If Err Then
  55.             MsgBox "An error occurred. Please try again."
  56.             Response = acDataErrContinue
  57.         Else
  58.             Response = acDataErrAdded
  59.         End If
  60.     End If
  61.  
  62. Set rs = Nothing
  63. Set db = Nothing
  64. End Sub
  65.  
  66. Private Sub cboLocation_BeforeUpdate(Cancel As Integer)
  67. If IsNull(cboLocation) Then
  68.         MsgBox "You must choose a Location", 48, "No Data Entered"
  69.         Cancel = True
  70.     End If
  71. End Sub
  72.  
  73. Private Sub cboLocation_NotInList(NewData As String, Response As Integer)
  74. Dim db As DAO.Database
  75. Dim rs As DAO.Recordset
  76. Dim strMsg As String
  77.  
  78.     strMsg = "'" & NewData & "' is not an available Location " & vbCrLf & vbCrLf
  79.     strMsg = strMsg & "Do you want to associate the new Name to the current Location?"
  80.     strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type it."
  81.  
  82.     If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
  83.         Response = acDataErrContinue
  84.     Else
  85.         Set db = CurrentDb
  86.         Set rs = db.OpenRecordset("tblLocation_List", dbOpenDynaset)
  87.         On Error Resume Next
  88.         rs.AddNew
  89.             rs!Location = NewData
  90.         rs.Update
  91.  
  92.         If Err Then
  93.             MsgBox "An error occurred. Please try again."
  94.             Response = acDataErrContinue
  95.         Else
  96.             Response = acDataErrAdded
  97.         End If
  98.     End If
  99.  
  100. Set rs = Nothing
  101. Set db = Nothing
  102. End Sub
  103.  
  104. Private Sub cboProvince_BeforeUpdate(Cancel As Integer)
  105. If IsNull(cboProvince) Then
  106.         MsgBox "You must choose a province", 48, "No Data Entered"
  107.         Cancel = True
  108.     End If
  109. End Sub
  110.  
  111. Private Sub cboTenant_AfterUpdate()
  112. Dim stDocName As String
  113. Dim stLinkCriteria As String
  114.  
  115. stDocName = "frmLease_Data_Entry"
  116. stLinkCriteria = "[Building_ID]=" & Forms!frmData_Entry.txtID
  117.  
  118.     If cboTenant.Value = "Tenant Occupied" Or cboTenant.Value = "SubLease" Then
  119.         cmdLease.Enabled = True
  120.         DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  121.         DoCmd.OpenForm stDocName, , , stLinkCriteria
  122.         Forms!frmLease_Data_Entry.txtID.DefaultValue = Forms!frmData_Entry.txtID
  123.     Else: cmdLease.Enabled = False
  124.     End If
  125. End Sub
  126.  
  127. Private Sub cboType_AfterUpdate()
  128.  
  129.     txtUnits.Enabled = Me.cboType.Column(2)
  130.     txtPrice_Unit.Enabled = Me.cboType.Column(2)
  131.  
  132. End Sub
  133.  
  134. Private Sub cboType_NotInList(NewData As String, Response As Integer)
  135. Dim db As DAO.Database
  136. Dim rs As DAO.Recordset
  137. Dim strMsg, strMsg2 As String
  138. Dim i As Integer
  139.  
  140.     strMsg = "'" & NewData & "' is not an available building type " & vbCrLf & vbCrLf
  141.     strMsg = strMsg & "Do you want to associate the new name to the current type?"
  142.     strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type it."
  143.  
  144.     If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new building type?") = vbNo Then
  145.         Response = acDataErrContinue
  146.     Else
  147.         Set db = CurrentDb
  148.         Set rs = db.OpenRecordset("tblBuilding_Type_List", dbOpenDynaset)
  149.         On Error Resume Next
  150.         rs.AddNew
  151.             rs.Fields(1) = NewData
  152.         For i = 2 To rs.Fields.Count - 1
  153.             If rs(i).Name = "Enable_Units" Then
  154.                strMsg2 = "Enable No. of Rooms / Units & Price per Room / Unit?"
  155.                If MsgBox(strMsg2, vbQuestion + vbYesNo, "Enable Fields?") = vbYes Then
  156.                   rs(i).Value = True
  157.                Else
  158.                   rs(i).Value = False
  159.                End If
  160.             End If
  161.         Next i
  162.         rs.Update
  163.  
  164.         If Err Then
  165.             MsgBox "An error occurred. Please try again."
  166.             Response = acDataErrContinue
  167.         Else
  168.             Response = acDataErrAdded
  169.         End If
  170.     End If
  171.  
  172. rs.Close
  173. Set rs = Nothing
  174. Set db = Nothing
  175. End Sub
  176.  
  177. Private Sub chkLease_AfterUpdate()
  178. If chkLease = True Then
  179.     txtPrice.Enabled = False
  180.     txtDate.Enabled = False
  181.     txtGross.Enabled = False
  182.     txtNet.Enabled = False
  183.     txtGIM.Enabled = False
  184.     txtOAR.Enabled = False
  185.     txtLand.Enabled = False
  186.     txtPurchaser.Enabled = False
  187.     txtExpense.Enabled = False
  188. Else
  189.     txtPrice.Enabled = True
  190.     txtDate.Enabled = True
  191.     txtGross.Enabled = True
  192.     txtNet.Enabled = True
  193.     txtGIM.Enabled = True
  194.     txtOAR.Enabled = True
  195.     txtLand.Enabled = True
  196.     txtPurchaser.Enabled = True
  197.     txtExpense.Enabled = True
  198. End If
  199.  
  200. End Sub
  201.  
  202. Private Sub cmdDelete_Click()
  203. On Error GoTo Err_cmdDelete_Click
  204.  
  205.  
  206.     DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
  207.     DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
  208.  
  209. Exit_cmdDelete_Click:
  210.     Exit Sub
  211.  
  212. Err_cmdDelete_Click:
  213.  
  214.     Select Case Err
  215.     Case 2501
  216.         'action cancelled
  217.         Resume Exit_cmdDelete_Click
  218.     Case Else
  219.         'handle unexpected errors here
  220.         MsgBox Err.Description
  221.         Resume Exit_cmdDelete_Click
  222.     End Select
  223. End Sub
  224.  
  225. Private Sub cmdLease_Click()
  226. Dim stDocName As String
  227. Dim stLinkCriteria As String
  228.  
  229. stDocName = "frmLease_Data_Entry"
  230. stLinkCriteria = "[Building_ID]=" & Forms!frmData_Entry.txtID
  231.  
  232.     If cboTenant.Value = "tenant Occupied" Or cboTenant.Value = "SubLease" Then
  233.         DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  234.         DoCmd.OpenForm stDocName, , , stLinkCriteria
  235.         Forms!frmLease_Data_Entry.txtID.DefaultValue = Forms!frmData_Entry.txtID
  236.     End If
  237.  
  238. End Sub
  239.  
  240. Private Sub cmdMain_Click()
  241. On Error GoTo Err_cmdMain_Click
  242.  
  243.     Dim stDocName As String
  244.     Dim stLinkCriteria As String
  245.  
  246.     stDocName = "frmMain_Menu"
  247.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  248.  
  249.     DoCmd.Close acForm, "frmData_Entry"
  250.  
  251. Exit_cmdMain_Click:
  252.     Exit Sub
  253.  
  254. Err_cmdMain_Click:
  255.     MsgBox Err.Description
  256.     Resume Exit_cmdMain_Click
  257. End Sub
  258.  
  259. Private Sub cmdNew_Click()
  260. On Error GoTo Err_cmdNew_Click
  261.  
  262.  
  263.  
  264.     DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  265.     DoCmd.GoToRecord , , acNewRec
  266.     txtAddress.SetFocus
  267.  
  268.  
  269. Exit_cmdNew_Click:
  270.     Exit Sub
  271.  
  272. Err_cmdNew_Click:
  273.     MsgBox Err.Description
  274.     Resume Exit_cmdNew_Click
  275. End Sub
  276.  
  277. Private Sub cmdPrint_Click()
  278. On Error GoTo Err_cmdPrint_Click
  279.  
  280.     Dim stDocName As String
  281.     Dim stLinkCriteria As String
  282.  
  283.     stDocName = "rptData_Entry"
  284.  
  285.     stLinkCriteria = "tblCom_Buildings.[Building_ID]=" & Me![txtID]
  286.     DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  287.     DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
  288.  
  289. Exit_cmdPrint_Click:
  290.     Exit Sub
  291.  
  292. Err_cmdPrint_Click:
  293.     MsgBox Err.Description
  294.     Resume Exit_cmdPrint_Click
  295. End Sub
  296.  
  297.  
  298. 'Private Sub Form_AfterUpdate()
  299. 'If IsNull(cboLocation) Or IsNull(cboProvince) Then
  300. '        MsgBox "You must choose a location or province", 48, "No Data Entered"
  301. '        Cancel = True
  302. '    End If
  303. 'If IsNull(cboDistrict) Then
  304. '    MsgBox "Please choose n/a for District rather than leaving blank", 48, "No Data Entered"
  305. '    Cancel = True
  306. '    End If
  307. 'End Sub
  308.  
  309. Private Sub Form_Close()
  310.  If Me.Dirty Then
  311.     Me.Dirty = False
  312.  End If
  313.  
  314. End Sub
  315.  
  316. Private Sub Form_Current()
  317. If cboTenant.Value = "Tenant Occupied" Or cboTenant.Value = "SubLease" Then
  318.     cmdLease.Enabled = True
  319. Else: cmdLease.Enabled = False
  320. End If
  321.  
  322.     With Me
  323.        If IsNull(.cboType.Column(2)) Then
  324.            'do nothing
  325.         Else
  326.            txtUnits.Enabled = Me.cboType.Column(2)
  327.            txtPrice_Unit.Enabled = Me.cboType.Column(2)
  328.         End If
  329.     End With
  330.  
  331. If chkLease = True Then
  332.     txtPrice.Enabled = False
  333.     txtDate.Enabled = False
  334.     txtGross.Enabled = False
  335.     txtNet.Enabled = False
  336.     txtGIM.Enabled = False
  337.     txtOAR.Enabled = False
  338.     txtLand.Enabled = False
  339.     txtPurchaser.Enabled = False
  340.     txtExpense.Enabled = False
  341. Else
  342.     txtPrice.Enabled = True
  343.     txtDate.Enabled = True
  344.     txtGross.Enabled = True
  345.     txtNet.Enabled = True
  346.     txtGIM.Enabled = True
  347.     txtOAR.Enabled = True
  348.     txtLand.Enabled = True
  349.     txtPurchaser.Enabled = True
  350.     txtExpense.Enabled = True
  351. End If
  352. End Sub
  353.  
  354. Private Sub Form_Open(Cancel As Integer)
  355. On Error GoTo Err_Form_Open
  356.  
  357.     DoCmd.Maximize
  358.  
  359.     If Me.FilterOn = True Then
  360.         cmdClose.Visible = True
  361.     Else
  362.         cmdClose.Visible = False
  363.     End If
  364.  
  365. If cboTenant.Value = "Tenant Occupied" Or cboTenant.Value = "SubLease" Then
  366.    cmdLease.Enabled = True
  367. Else
  368.    cmdLease.Enabled = False
  369. End If
  370.  
  371. If chkLease = True Then
  372.     txtPrice.Enabled = False
  373.     txtDate.Enabled = False
  374.     txtGross.Enabled = False
  375.     txtNet.Enabled = False
  376.     txtGIM.Enabled = False
  377.     txtOAR.Enabled = False
  378.     txtLand.Enabled = False
  379.     txtPurchaser.Enabled = False
  380.     txtExpense.Enabled = False
  381. Else
  382.     txtPrice.Enabled = True
  383.     txtDate.Enabled = True
  384.     txtGross.Enabled = True
  385.     txtNet.Enabled = True
  386.     txtGIM.Enabled = True
  387.     txtOAR.Enabled = True
  388.     txtLand.Enabled = True
  389.     txtPurchaser.Enabled = True
  390.     txtExpense.Enabled = True
  391. End If
  392.  
  393. Exit_Form_Open:
  394.     Exit Sub
  395.  
  396. Err_Form_Open:
  397.    Select Case Err
  398.     Case 3024
  399.         'action cancelled
  400.         Cancel = True
  401.     Case Else
  402.         'handle unexpected errors here
  403.         MsgBox Err.Description
  404.         Resume Exit_Form_Open
  405.     End Select
  406. End Sub
  407. Private Sub cmdSave_Click()
  408. On Error GoTo Err_cmdSave_Click
  409.  
  410.  
  411.     DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  412.  
  413. Exit_cmdSave_Click:
  414.     Exit Sub
  415.  
  416. Err_cmdSave_Click:
  417.     MsgBox Err.Description
  418.     Resume Exit_cmdSave_Click
  419.  
  420. End Sub
  421.  
  422. Private Sub txtAcres_AfterUpdate()
  423. If txtAcres <> "" Then
  424.     txtSF.Value = txtAcres * 43560
  425. End If
  426. End Sub
  427.  
  428. Private Sub txtAddress_BeforeUpdate(Cancel As Integer)
  429. Dim strMsg1, strMsg2, strMsg3, strMsg4, strMsg5 As String
  430. Dim strFind As Variant
  431. Dim rs As DAO.Recordset
  432. Dim strCriteria, strAddress As String
  433.  
  434. strMsg1 = "This address already exists."
  435. strMsg2 = "Click OK to be taken to the record to verify." & Chr$(13) & Chr$(10)
  436. strMsg3 = "If the record you are trying to enter already exists, do nothing."
  437. strMsg4 = "Otherwise add a new record and select no to continue."
  438. strMsg5 = strMsg1 & Chr$(13) & Chr$(10) & strMsg2 & Chr$(13) & Chr$(10) & strMsg3 & Chr$(13) & Chr$(10) & strMsg4
  439.  
  440. Set rs = Me.RecordsetClone
  441. strAddress = Me.[txtAddress].Value
  442. strCriteria = "[Location_Address]=" & "'" & strAddress & "'"
  443.  
  444. If DCount("[Location_Address]", "tblCom_Buildings", strCriteria) > 0 Then
  445.         If MsgBox(strMsg5, vbInformation + vbYesNo, "Duplicate Address") = vbYes Then
  446.             Me.Undo
  447.             rs.FindFirst strCriteria
  448.             Me.Bookmark = rs.Bookmark
  449.  
  450.             rs.Close
  451.             Set rs = Nothing
  452.         Else
  453.             Exit Sub
  454.         End If
  455. End If
  456. End Sub
  457.  
  458. Private Sub txtSF_AfterUpdate()
  459. If txtSF <> "" Then
  460.     txtAcres.Value = txtSF / 43560
  461. End If
  462. End Sub
  463. Private Sub cmdClose_Click()
  464. On Error GoTo Err_cmdClose_Click
  465.  
  466.  
  467.     DoCmd.Close acForm, "frmData_Entry"
  468.  
  469. Exit_cmdClose_Click:
  470.     Exit Sub
  471.  
  472. Err_cmdClose_Click:
  473.     MsgBox Err.Description
  474.     Resume Exit_cmdClose_Click
  475.  
  476. End Sub
  477.  
Mar 28 '12 #3

P: 28
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
Mar 28 '12 #4

mshmyob
Expert 100+
P: 903
ok................. (don't see why a default can't be 0 but what the hey....)

cheers
Mar 28 '12 #5

P: 28
Here is Microsoft's explanation http://support.microsoft.com/kb/289659
Mar 28 '12 #6

mshmyob
Expert 100+
P: 903
Makes sense with this added piece of information of how your form and controls are bound.

cheers,
Mar 28 '12 #7

Post your reply

Sign in to post your reply or Sign up for a free account.