i have a order form with a sub form orderdtl, we got the option to change the status of the order from processing to shipping, only if the desired items in the order are all in stock or in other words if the quantity is less than or equal to the available stock, but for this to happen i hav to combine 2 queries one called stock which has all the stock on hand details which is calculated in the query and other query is the orderdtl query where the item total is calculated with unitprice and quantity.
i have tried two ways:
i made just one query with only orderdtl query, and added two text boxes to dlookup the stock and if funtion to see if the quantity is available and with tht data i count all items in the order and also count no of instock items in tht order, and staus can only change if total count of items equals to instock item. for ths to happen i need to send the dlookup and if function data to a table field to be stored inother to be proccessed for counting, but it does not work becuse the event only occurs in the current selected record of the continous form orderdtl.
second option i tried was combine 2 queriess the orderdtl and stock ones so that i get the needed data already stored in a table and check to order form but then when the queries combine i cannot add a new field to the continous form orderdtl which has the recordsocuce of the 2 queies
i have tried two ways:
i made just one query with only orderdtl query, and added two text boxes to dlookup the stock and if funtion to see if the quantity is available and with tht data i count all items in the order and also count no of instock items in tht order, and staus can only change if total count of items equals to instock item. for ths to happen i need to send the dlookup and if function data to a table field to be stored inother to be proccessed for counting, but it does not work becuse the event only occurs in the current selected record of the continous form orderdtl.
second option i tried was combine 2 queriess the orderdtl and stock ones so that i get the needed data already stored in a table and check to order form but then when the queries combine i cannot add a new field to the continous form orderdtl which has the recordsocuce of the 2 queies