PDA

View Full Version : Field cannot be updated



gnod
11-20-2007, 01:13 AM
hi,

in the ListSheet, when i click the "update general services", it prompt an error - "Field cannot be updated"..

:help

thanks..

Charlize
11-20-2007, 05:26 AM
It's because you're using a formula to calculate the number in column AK of DBInfo on Annual_DBInfo sheet.
="Rate + " & Annual_InflationRate_GeneralServices*100 & "% Inflation Rate"
If you remove the formula from the destination sheet, it works.

gnod
11-20-2007, 04:08 PM
yes, it works if the destination sheet doesn't contain a formula but is there any other solution because the destination sheet is already deployed to the user and my Modifier file is only workaround for their Mid-Year budget..

thanks..

Charlize
11-20-2007, 04:33 PM
Modified version of this routine. Skip the column with the formula in it.Sub Write2ClosedBookMultipleCellRange(WorkbookFullName As String, _
SQL As String, NewValue As Variant)
Dim conn As New Connection, rs As New Recordset
Dim intRow As Integer, intCol As Integer
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & WorkbookFullName & _
";Extended Properties=""Excel 8.0;HDR=NO;"""
rs.Open SQL, conn, 1, 3
For intRow = 1 To UBound(NewValue, 1)
For intCol = 1 To UBound(NewValue, 2)
'*** additional check on which column
'*** but I don't know if you use this routine for something else
If intCol <> 6 Then
rs.Fields(intCol - 1).Value = NewValue(intRow, intCol)
End If
Next intCol
rs.MoveNext
Next intRow
rs.MoveFirst
rs.Update
rs.Close
conn.Close
End Sub

gnod
11-21-2007, 07:55 AM
Thanks for your reply.. but this Sub is general procedure if you notice i use it also in the "Update Janitorial Services"

you give me an idea.. i will create a condition if it is Janitorial or General Services.. thanks..

gnod
11-22-2007, 12:28 AM
hi,
when i click the "Update Janitorial Services" in the ListSheet, the file in the destination sheet gets bigger.. how can i save the closed workbook?

thanks

Charlize
11-22-2007, 03:16 AM
Try this Sub Write2ClosedBookMultipleCellRange(WorkbookFullName As String, SQL As String, NewValue As Variant)
Dim conn As New Connection, rs As New Recordset
Dim intRow As Integer, intCol As Integer
'declare a variable to be used as workbookhandle
Dim datawb As Workbook

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & WorkbookFullName & _
";Extended Properties=""Excel 8.0;HDR=NO;"""
rs.Open SQL, conn, 1, 3

For intRow = 1 To UBound(NewValue, 1)
For intCol = 1 To UBound(NewValue, 2)
rs.Fields(intCol - 1).Value = NewValue(intRow, intCol)
Next intCol
rs.MoveNext
Next intRow
rs.MoveFirst
rs.Update
rs.Close
conn.Close
'open the closed workbook and save it again
'without updating the screen
Application.ScreenUpdating = False
'define the handle
Set datawb = Workbooks.Open(WorkbookFullName)
datawb.Save
datawb.Close
Application.ScreenUpdating = True
Set datawb = Nothing
End Sub

gnod
11-22-2007, 07:36 AM
is there any other way without opening the closed workbook?
i'm trying the

rs.Save WorkbookFullName


after the rs.Update but it prompt me that the file is already exist
how can i select "Yes" in the prompt?


Thanks..