What is the syntax for getting a function's result into a query's criteria?

[stonward]

Hello Again All,

Forgive me as I try to be brief:

I am using a modified version of Allen Browne's excellent 'onhand' function for calculating stock levels 'on-the-fly'.

The function works perfectly, however I need to show stocklevels for a number of items on my 'home' form and found that the onhand function is way too slow to allow that. My solution is to 'reflect' the onhand results via a query into my products table, and this works well, since I'm simply viewing the data.

But when I perform a transaction, I can only get the onhand function to work if I recalculate all of my products, rather than just the ones in the transaction - and that is slow - at least 30 seconds.

I have tried my preferred method of SQL in the module code, and running a query of the same from the code, but zero records are updated. Here is the SQL I have been using - can you see an error?

Dim strSQL as String
strSQL = "UPDATE Products SET Products.Stocklevel = (onhand([productID])) " & vbCrLf & _
"WHERE (((Products.Stocklevel) = [forms]![frm_PurchaseDetails]![productID]));"
DoCmd.SetWarnings False
DoCmd.RunSQL(strSQL)
DoCmd.SetWarnings True

Products = Products table, frm_PurchaseDetails = subform to frm_Purchases, linked of course.

If you don't know the 'onhand' function, you simply provide the ProductID and an optional date to get the stock calculation for that date.

The SQL for the query that works (but calculates all products) is;

UPDATE Products SET Products.Stocklevel = (onhand([productID]));

And a clue may be that the above will not work as SQL in the module, only as running the query.

Sorry it appears so drawn out...

Best regards,
Stonward

[NeoPa]

The WHERE clause from the SQL in the first code block treats Products.Stocklevel as a ProductID, whereas the working, but slow, SQL from the second block treats it in a way that makes more sense - as a StockLevel.

[stonward]

Ha! Spotted NeoPa. And so obvious...now.

Altered to where clause from stocklevel to productid and the update works, but is still very slow. Am I missing something more fundamental I should be looking at?

Thanks for finding that; I couldn't find it.

Regards

Stonward

[stonward]

Hi again.

Something is wrong with the update SQL/query (above). I altered the SQL to take account of what looked like a glaring error that Neopa found. The stocklevels get updated, but i also get a parameter query input box asking me for input. Clicking OK (no input) updates the stocklevel.

But when I look at the (update) query that the SQL is based upon, I can see no errors. The

Products.Stocklevel

that appears to be an error is the field and table that are being updated:

Field: Stocklevel
Table: Products
Update To: (onhand([productID]))
Criteria: [forms]![frm_Purchases]![frmPurchaseDetails].[form].[productID]

Can anyone see an error in the above? It should update one stocklevel record of a product with the product ID shown on the PurchaseDetails subform.

It's starting to look easier to simply add/subtract the transaction quantity from the current stocklevel!

Thanks guys,

stonward

[NeoPa]

Stonward:
Am I missing something more fundamental I should be looking at?

The only obvious thing I see is that you are calling a VBA function. I'm guessing the function needs to navigate to the correct record every time it's called. This is on top of opening and closing a recordset and the interpreter interpreting every line of VBA code in it. I wouldn't expect this to be at all trivial.

PS. As for the new question I suggest you post it in its own thread and include the actual SQL code sent to the Jet SQL engine. Feel free to post a link in here to the new thread if you like, but I expect I'll see it somewhen shortly anyway.