If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Unable to update field in a linked table
I have a database that is entirely dependant on an Excel
workbook and 14 worksheets through linked tables. The spreadsheet is used to forecast needed 'employees' on a given date. It uses the actual data that is entered in the access form to make this calculation. Example: On 4/19 there were 36 employees on 4/20 it is forecasted that there is a need of 35 employees. So, cell B2 contains the value 36, while cell B3 contains a formula. As a person enters data the formulas should be overwritten for each day's actual data. My form shows the values and the worksheet is doing its job. Problem: Access will not allow the information in "cell B3" to be overwritten - error message "unable to update field" the help says that it is 'in use' or 'locked' neither is the case. Please Help!! |
#2
|
|||
|
|||
Unable to update field in a linked table
"Ted" wrote in message ...
I have a database that is entirely dependant on an Excel workbook and 14 worksheets through linked tables. Heads up: in this ng you risk being told your database is incorrectly designed and be advised to read up on you read up on Relational Database Design Theory. cell B2 contains the value 36, while cell B3 contains a formula. As a person enters data the formulas should be overwritten for each day's actual data. Are you sure this is what's happening? It sounds likely B2 is a dependent of the formula in B3, so the formula remains the same and merely shows a different value when e.g. the worksheet is recalculated. Problem: Access will not allow the information in "cell B3" to be overwritten - error message "unable to update field" the help says that it is 'in use' or 'locked' neither is the case. You can't change a formula cell with a query. In order for a workbook's formulas to calculate properly and efficiently, Excel maintains trees and chains of dependencies. If you were allowed to change a formula it would upset the dependencies. You can only change a cell formula by opening the workbook in the native Excel app e.g. using automation from MS Access. -- |
#3
|
|||
|
|||
Unable to update field in a linked table
The whole thing was done only in Excel in the first place
and human error was a very big factor. There are about 75 cells of data that have to be entered by hand daily into 11 of the 14 spreadsheets, by using Access I keep the person entering data out of Excel. In this way, the only time Excel is ever opened is to view or print data. **The formula is cell B3 grabs data from another cell and calculates a 'forecasted' value. The same formula was in B2 until it was updated with the actual value. As would be the same with B3 and so on as the days go on.** The data entered in Access actually goes into the other 3 spreadsheets and then the 11 link to the other spreadsheets containing whatever data it needs to acquire. Every other aspect works seamlessly. The only problem was the one listed below. Getting the result of the formula - no problem - just needed to overwrite the formula. However, I did provide a 'work around' for the time being - but it does require someone going into the spreadsheet. A solution I believe could be the following and I will try it: Create another worksheet that contains the formulas that manipulate the data for forecasting, then remove all formulas from the worksheet that Access deals with - that allows Access to update the cells. Then the 11 worksheets that actually grab the data will need to have their formulas changed to 'If' statements checking to see if there is a value in the sheet Access uses, if so use it, if it doesn't, grab the data from the new sheet that 'forecasts' the information with formulas - seperating the two. Although very convoluted, it should work. -----Original Message----- "Ted" wrote in message ... I have a database that is entirely dependant on an Excel workbook and 14 worksheets through linked tables. Heads up: in this ng you risk being told your database is incorrectly designed and be advised to read up on you read up on Relational Database Design Theory. cell B2 contains the value 36, while cell B3 contains a formula. As a person enters data the formulas should be overwritten for each day's actual data. Are you sure this is what's happening? It sounds likely B2 is a dependent of the formula in B3, so the formula remains the same and merely shows a different value when e.g. the worksheet is recalculated. Problem: Access will not allow the information in "cell B3" to be overwritten - error message "unable to update field" the help says that it is 'in use' or 'locked' neither is the case. You can't change a formula cell with a query. In order for a workbook's formulas to calculate properly and efficiently, Excel maintains trees and chains of dependencies. If you were allowed to change a formula it would upset the dependencies. You can only change a cell formula by opening the workbook in the native Excel app e.g. using automation from MS Access. -- . |
Thread Tools | |
Display Modes | |
|
|