2 queries in one form (1 Viewer)

TMK

New member
Local time
Tomorrow, 03:04
Joined
Apr 28, 2025
Messages
27
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
 
hi guys maybe if there is any way i could count the number of items. In the order using a text field with control source "=count(*)" i ge the full amount of items, but to get the other one is the problem im having, tht is the count of how many items are in stock form the other. i cant count it as the field used to calculate field temporay as is not stored in a table so it send #Error msg instead, if i could get tht count, i can make a another equation to allow change of status to shipped if total count = stock present count only.
 
Words aren't going to work to explain this. We either need a sample database and precise instructions as to what you need where. Or we can just convert this to a data problem and you can provide sample data to demonstrate your issue.
 
Your question is difficult to answer without knowing something of the structure of the relevant tables. There is also the issue of how you are keeping track of the inventory, for which there are two popular methods - updating a master record, or storing all transactions and doing a summation of the transaction amounts.

To get best results, we would also need to know a little bit about relationships among the tables - if any are defined.
 
Before giving any theory advice, how quickly does your stock change? Do you normally have plenty to sell during the day and still keep stock for the next day? Do you sell out by the end of the day? Do you sell out during the day and have to stop selling that item?

Depending on how quickly your stock amount changes, you can either show "Quantity on hand" when the order is started and expect it will be ready to go when you complete your order OR you will have to check and allocate stock as you enter detail lines.

This is a design question that you would want to answer before you start building this.
 
The following query references a query qryStockInHand which computes the current inventory per product and returns columns ProductID and StockInHand, and an OrderDetails table with columns OrderNumber, ProductID, Quantity, and UnitPrice. The query returns any orders for which there is not current stock in hand to supply all products ordered:

SQL:
SELECT DISTINCT
    OrderNumber
FROM
    OrderDetails
WHERE
    EXISTS
       (SELECT
            *
        FROM
            qryStockInHand
        WHERE
            qryStockInHand.ProductID = OrderDetails.ProductID
            AND qryStockInHand.StockInHand < OrderDetails.Quantity);
 
hi guys maybe if there is any way i could count the number of items. In the order using a text field with control source "=count(*)" i ge the full amount of items, but to get the other one is the problem im having, tht is the count of how many items are in stock form the other. i cant count it as the field used to calculate field temporay as is not stored in a table so it send #Error msg instead, if i could get tht count, i can make a another equation to allow change of status to shipped if total count = stock present count only.
Can you upload a copy of the database?
 

Users who are viewing this thread

Back
Top Bottom