asked on

Use the result of an Access query in another query

Table 1 (Orders)
One order equals one row in the 'Orders' table.

Table 2 (Products)
Contains product names, IDs and current inventory amounts.

Every order gets a free keychain. (Product ID = 629)
I have 1,000 keychains in inventory (according to my Products table)

So ...
Let's say I run this query ...

SELECT Count([Order ID]) AS CountOfOrders 
    FROM [Orders] 
    WHERE [Order Date] = [forms]![Main Form]![txtReportDate];

Open in new window

... and it returns '10'.
That tells me I processed 10 orders for today.

Now I need to deduct '10' keychains from the Products table.

I need help figuring out how to take the result (10) from this query and use it in another query. (Using VBA since I will be assigning this action to a button click).

UPDATE Products SET Products.QOH = QOH - **ResultFromAboveQuery**
    WHERE Products.[ProductID] = "628";

Open in new window

To run a query in VBA, do I have to use DAO, create a recordset, grab the value, assign that value to a variable, then close the recordset ... then ... create a NEW recordset, run the update query, and then close the recordset?

If this is best practice, I need help writing this code.
Or is there an easier way to do this?

AccessGuy1763

I guess the one thing that bothers me about this scenario is that it looks like you're trying to run this query to keep quantity up to date when, in my mind, a more proper solution would be to have the free keychains added to the invoice through your system at a $0.00 price. If the system is set up right, QOH could be reduced as a result of the order being processed. However, I digress as I don't know the details of your system well enough to know if this type of implementation is possible.

A big concern I have with your suggested solution is just that it seems so manual in nature. For example, let's say last week you physically reduce your QOH for the keychain by running a query like you've suggested. Now it's Friday this week. What day did you run that update on? If Wednesday (are you sure?), did you just run it for last week or did you run it for last week and Monday and Tuesday? Did you include Wednesday? You see what I'm getting at here? How do you keep track of the days you've run so far to keep QOH up to date?

Absent a solution like I suggested in the first paragraph, my suggestion would be to not actually reduce QOH for the keychain until you know they're all gone. Your query would be like follows to track how many keychains have been given out so far:

SELECT Count([tblOrders.OrderID]) as OrderCount
FROM tblOrders
WHERE OrderDate>=#FirstDateWhereKeychainsWereGiven#

Open in new window

If you want to use this within a function, this is how it would look:

Function fcnCountOrders(inStartDate As Date) As Long

    'This function will count the number of Orders in tblOrders from the
    'passed in Start Date to the present.  The function returns -1 if any
    'error occurs.
    
    'Turn on error handling
    On Error GoTo Error_Handler
    
    'Declare variables
    Dim objOrderCountRS As DAO.Recordset2
    Dim strSQL As String
    
    'Construct SQL string
    strSQL = "SELECT Count([tblOrders.OrderID]) AS OrderCount " & _
             "FROM tblOrders " & _
             "WHERE OrderDate>=#" & inStartDate & "#;"
             
    'Instantiate recordset
    Set objOrderCountRS = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
    
    'Return the OrderCount
    fcnCountOrders = objOrderCountRS.Fields("OrderCount")
    
    'Close RS
    objOrderCountRS.Close
    
    'Clean Up
    Set objOrderCountRS = Nothing
    
    'Exit before error handling
    Exit Function
    
Error_Handler:

    'Return -1
    fcnCountOrders = -1
    
End Function

Open in new window

At this point, you could do a bunch of things with the function. I would probably try to tie it in with the ordering form or put it in a textbox on a different form where it's visible on a regular basis. When that count reaches 1000, the keychains are gone and you can reduce QOH to 0. You can also call the function from within a query if that suits your style better.

AccessGuy1763

I should add that you could also use my suggestion to somewhat keep QOH up to date as well... the only real difference is that you would always subtract whatever the query or function returns from 1000 if the date you're using for inStartDate is the date the first keychains went out.

E C

ASKER

Hello AccessGuy,
Thanks for the detailed info. I certainly share all your concerns and wish there was a way to automate this a bit more, but the daily list of orders comes to us from a third party (I simply import it into Access each morning), and we process orders (usually once per day) in batches.

So let's say we receive a data file with 20 orders. All 20 orders are sucked into Access and the shipping labels are printed at that moment. There's a flag called 'LabelPrinted' that is set to True as our shipping system sends back the transaction ID. So at any given moment I know exactly which shipping labels have been generated, and when they were printed.

I figure this is the best time to go ahead and deduct 20 keychains, since the guys in shipping aren't going to remember to report this to me every single time they pack a box and put a label on it. As soon as we generate 20 shipping labels, I want to deduct 20 keychains from inventory. (I am probably doing this a little farther upstream than I should be, but once I hand off the labels to our shipping department I know I don't have to go back into Access and click any buttons until the next day. Data processing is done until the next data file of orders comes in)

There's quite a bit more functionality to the app than I explained in my original post, but I figured I would strip it down to the core problem. (I haven't used Access in over 10 years so I get to learn VBA all over again)

Regarding your comment about WHEN the app runs (If it doesn't run until Wednesday, does it include orders for Monday and Tuesday...) ... The database keeps track of where we left off. So although we always try to process the list every day (only once per day), if we did miss a day (or if we processed a list twice in one day), there are safeguards to ensure we are not double-processing orders or missing any orders.

OK, so on to the VBA code ...
The function you wrote in your post ...
It creates runs the query and stores that value (20, in my example) where? In a variable?

How do I now take that variable and use it to deduct 20 from inventory?

ASKER CERTIFIED SOLUTION

AccessGuy1763

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

E C

ASKER

Interesting ...

So, I can create a button that when clicked, will simply run this update query:

UPDATE Products SET Products.QOH = QOH - fcnCountOrders(#DateHere#)
WHERE Products.[ProductID] = "628";

Open in new window

And the query is calling the function?

Didn't know you could call a function from within a query!
let me try that now ...

AccessGuy1763

In that case you can just do:

Docmd.RunSQL "Your query text here or you can use a variable"

Open in new window

This will trigger the Access Update query warning.. you can do this to turn it off for a second:

docmd.setwarnings false

docmd.runsql "Query text or a variables"

docmd.setwarnings true

Open in new window

E C

ASKER

AccessGuy, thank you VERY much for taking time to help me. Your detailed responses gave me everything I needed to know to get my project completed. You also taught me a few things (like using docmd.setwarnings false / run sql / docmd.setwarnings true to run a SQL update statement without displaying the warning message from Access ;-)

I bumped this question up to 500 points.
Thanks again.