What is the best way of allowing the user to search the database and return all incidents of a particular item. It should group them by raw material, Lot# and location. It should also calculate the total in each lot# - material can be taken from a location by inputting a negative value (-10 etc).
The query must display all the available raw material by raw material, Lot# and location. It must also be possible to open another form to deduct from stock using the original query results as a guide. It should be visible in the background.
On clicking the save button in form 2 the qty required in that form should be checked against the qty available in that Lot# and location and an error message returned if there is not enough.
I have outlined below my approach but it is very cumbersome with the user haveing to use the filter keys (I would prefer if the users had as little interaction as possible).
I have created the query below to sum the qty of material in stock
SELECT tblstock.[MaterialPart#], tblstock.[Lot#], tblstock.Location, Sum(tblstock.Qty) AS SumOfQty
FROM tblstock
GROUP BY tblstock.[MaterialPart#], tblstock.[Lot#], tblstock.Location;
I then created a filter query based on the above query.
I then created a continuous form based on the filter query. I can then query by an raw material. I also include a button on the side to call another form which allows me to deduct the stock – I placed one form at the top of the page an done at the bottom. The second is in popup form so it stays on top. The only thing that does not work is running the check of qty require against qty in stock (in the click event on the save button?).
This works but is not very efficient. Any suggestions would be apreciated.
Regards
Niall
The query must display all the available raw material by raw material, Lot# and location. It must also be possible to open another form to deduct from stock using the original query results as a guide. It should be visible in the background.
On clicking the save button in form 2 the qty required in that form should be checked against the qty available in that Lot# and location and an error message returned if there is not enough.
I have outlined below my approach but it is very cumbersome with the user haveing to use the filter keys (I would prefer if the users had as little interaction as possible).
I have created the query below to sum the qty of material in stock
SELECT tblstock.[MaterialPart#], tblstock.[Lot#], tblstock.Location, Sum(tblstock.Qty) AS SumOfQty
FROM tblstock
GROUP BY tblstock.[MaterialPart#], tblstock.[Lot#], tblstock.Location;
I then created a filter query based on the above query.
I then created a continuous form based on the filter query. I can then query by an raw material. I also include a button on the side to call another form which allows me to deduct the stock – I placed one form at the top of the page an done at the bottom. The second is in popup form so it stays on top. The only thing that does not work is running the check of qty require against qty in stock (in the click event on the save button?).
This works but is not very efficient. Any suggestions would be apreciated.
Regards
Niall
