Update field on separate table if result from form equals certain criteria

[1eye1vision]

Hi All,


I have a form, which i use to book in products received. What i want to do is if the quantity received is equal to what was ordered then i'd like to update a yes/no field (Order Complete) on a separate table to true.

I cannot seem to get this to work no matter what i try...
Any help would be greatly appreciated.

Thanks in advance....

[June7]

Would need to know your data structure to advise on specifics. Do you want to provide the project for analysis?

[AcsDb]

Have you tried running an query in SQL via VBA?

[1eye1vision]

Hi,
I have already tried, but the table is not linked in a relationship so will not work. I may have to redesign the table to allow for orders and transactions..
The structure i have at the moment is:

1. tblorders
2. tblorderdetails
3. tblgoodsin

i have attached an image of the relationship table to show the connections between tables. You will see that the goodsin tbl is only linked to the muster list via the ProductID.



Any thoughts???

What i meant to add is:
The field i'd like to update is the Order Complete field on the OrderDetails Table.
I have the following in the recordsource for the form:
SELECT [Goods In].[GI No], [Goods In].[Product ID], [Goods In].[Quantity Received], [Goods In].[Quantity Rejected], [Muster List].[Stock Location], [Goods In].[Date Received], [Muster List].UnitsInStock, [Muster List].[Units On order], [Muster List].[Quantity Received]
FROM [Goods In] INNER JOIN [Muster List] ON [Goods In].[Product ID] = [Muster List].[Product ID];

Now if i add the Ordercomplete field to this i get in the status bar, recordset not updateable.

[AcsDb]

--------------

[1eye1vision]

The problem is if i don't store this value, when i print out a list of outstanding orders it will display all the orders. This way i could filter out what has been completed and what's not..

This was the only way i could think of doing this

[1eye1vision]

ok thanks will take another look and see if i can figure this out.

[June7]

An OrderComplete field should be in the Orders table, not OrderDetails repeating for each line item of the order. A status field in OrderDetails should indicate status of the specific item (sent, backordered, canceled). Then determining if Order is incomplete can be shown by a query that has criteria to return Order records based on OrderDetails Status field.

Requisitions to replenish inventory do not need to relate back to specific customer orders.

Maintaining aggregate data such as UnitsInStock is problematic. Ideally, in/out transactions (purchases/sales) records would be summed by product and the balance calculated whenever needed to determine stock levels.

[orange]

I don't understand GoodsIn and Muster List

Sounds like Goodsin is an Inventory type table. And you would have 2 basic transactions
Add Product to Inventory, Remove Product from Inventory.

You might get some design ideas from the model here http://www.databaseanswers.org/data_...ucts/index.htm

It deals with more things than you, but shows Customers, Products, Orders ,OrderItems(Details).

As has been said, lack of Normaliztion (fields in more than 1 place), will cause you grief.

Good luck with your project.