Files cannot update

[ashmariane]

Help me please I am new in Access, and I had a task to create an inventory for the company, then i use the template from access which is Inventory Management, I modify this template based on the requirements giving by the old inventory, every things ok but i encounter an error and its very difficult to solved



This error code said

The field cannot be updated because another user or process has locked the corresponding record or table. (Error 3164)

If you are familiar in Inventory Management of access online template your definitely understand what i mean.

Ok, the story starts with a 3 tables namely, Inventory table, Inventory Transactions table and Transaction Types table.

Then I created Inventory Details ( a form for a new item) then the Inventory List ( a split form type which you can see the summary of all the products) and the Inventory Transactions ( a form where you can modify the products, such incoming - the new delivered product and outgoing were you release the product from the inventory)

I encounter in the Inventory Transaction, see below


and this the illustration of relationship of each table i created


there are no modules create here, i copied all the event tab based from the Inventory management

Please help me the whole month of august ive search for this error to solved and nothings change, the inventory is needed urgently before the end of this year, and i cannot create another commands such as delete all record and reports for this error.

[boblarson]

You have too many tables in your query. Transaction Types should NOT be in that recordset.

[boblarson]

Oh and I forgot to mention, you don't need it at all in there. You still use a combo box to select the value to store inside the Transaction Type field in the Inventory Transactions table, but you don't need the Transaction Types table to be able to do so.

[ashmariane]

But how so not able to include the transaction types table, i think it doesn't affect the entire relationships, I want to solve error, i cant update the file, and yes i use the combo box to store data from inventory transaction, i always repeat from the start to understand the flow of inventory management why i always get this error,


hopefully you can help me

[boblarson]

What is the record source for the form?

[ashmariane]

record source?,

Ah, The form of Inventory Transaction connected to Inventory Transaction query,
the properties of this form had no modules only macro builder from the event tab which if you double click any row of the item the Inventory details will pop-up in this form you will see the history of the item , and also you can add new product.

In Inventory Transaction query here are the sql view

SELECT [Inventory Transactions].ID AS TransactionID, [Inventory Transactions].*, [Transaction Types].*, Inventory.*, IIf([Transaction Types].[Add/Remove]="Addition",[Inventory Transactions]![Quantity],-([Inventory Transactions]![Quantity])) AS [Actual Quantity], ([Inventory Transactions].[Quantity]*[Inventory Transactions].[Cost/Unit]) AS [Total Cost]
FROM Inventory INNER JOIN ([Inventory Transactions] LEFT JOIN [Transaction Types] ON [Inventory Transactions].[Transaction Type] = [Transaction Types].ID) ON Inventory.ID = [Inventory Transactions].[transaction Item];

additional
the Inventory Transaction also connected to Inventory List in this form you will see all the summary of all product stored,


Thank you very much

[boblarson]

Remove the Transaction Types table from the query. You also really should have the Inventory table as the ONLY table in that form but then have a SUBFORM with only the Inventory Transaction table as its record source located on the main form and then linked by

Master Link: ID
Child Link: Transaction Item


I don't use ID as my field names because it can get confusing. I would have InventoryID as the ID in the Inventory table and TransactionID in the Inventory Transactions table. I would then not have Transaction Item as the field name but InventoryID so that you can see how the two tables relate, even without looking at the relationships window.

If you don't separate into a main/sub form then you will need to go into the record source query and click in the gray are next to where the table shows in the diagram (but not on the table itself) and then change the property RECORDSET TYPE from DYNASET to DYNASET (INCONSISTENT UPDATES).

[ashmariane]

If will remove the transaction types table, and i will use look up to have add/remove combox?. Then I will rename the ID from Inventory Table into InventoryID and Item from Inventory Transactions type into InventoryID also? . After that, i the link from relationship tab which is the MasterLink is ID from inventory table and Child LInk is Transaction Item from the Inventory Transaction Table.

Great i will try right now,
Thanks you give me hope

[ashmariane]

I cant remove the Transaction types table, I had a Inventory Transaction query see this as what i posted before

SELECT [Inventory Transactions].ID AS TransactionID, [Inventory Transactions].*, [Transaction Types].*, Inventory.*, IIf([Transaction Types].[Add/Remove]="Addition",[Inventory Transactions]![Quantity],-([Inventory Transactions]![Quantity])) AS [Actual Quantity], ([Inventory Transactions].[Quantity]*[Inventory Transactions].[Cost/Unit]) AS [Total Cost]

FROM Inventory INNER JOIN ([Inventory Transactions] LEFT JOIN [Transaction Types] ON [Inventory Transactions].[Transaction Type] = [Transaction Types].ID) ON Inventory.ID = [Inventory Transactions].[transaction Item];

Thats how the transaction types connected .

[boblarson]

Sorry but you NEED to remove that table from this form's row source. You cannot have that calculated field in there anyway as it will also cause the record source to be non-updatable.

[ashmariane]

ok i will try ..