There's got to be a way

[Dave14867]

Hello all,

I have a subform on a form that contains parts used to build an item. I have a field for "Lot Number" in the subform where I input the Lot Number of an item used based on paper documentation. I also have just put in a field for "Qty On Hand" into the subform. The subform can contain just 1 or many records depending on the item being built.

What I want to be able to do is when I input the "Lot Number" (in the after update event I believe) that I run a query which will populate the unbound "Qty on Hand" field for that record.

Does that make sense? So far I haven't had any luck trying it.

Any Help is greatly appreciated



Thanks in Advance

[Bob Fitz]

You could probably use DLookup() or DCount() to get the required figure. Would need more details of your tables for more detailed advice.

[Dave14867]

OK, all the data comes from 1 table, the table contains fields for LotNumber, QtyRecieved, QtyUsed, QtyShrinkage, QtyCycleCount. When items are received, the qty gets populated into the QtyReceived field, and a Lot Number is assigned that gets populated into the LotNumber field. As parts are consumed for building product, I enter a Lot Number in the field on the subform and the QtyUsed is already populated based on the parent item and the qty made of the parent item.

What I want, is that when I input the Lot Number I need a query to run that calculates the Qty on Hand for that lot number. I have a similar query working on a report but I want to do the same thing in the subform. For the report I input the Lot Number and the report gives me how many pieces were received and how many have been consumed and what order # consumed each qty.

Thanks

[Bob Fitz]

Can you post a copy of the db with just a few fictitious records for illustration.

[Micron]

So you want to know how to run the query that would be the control source for an unbound textbox? I'm not reading that you want help to build that query.
Seems to me that you could update your unbound textbox in a number of ways:
- have your query calculate the value and bind the control to it
- an event runs an update query and populates the other query/table field, your control is bound to that and you refresh the form
- your unbound control source is a function that calculates the value or runs a query that calculates the value
- your single unbound control is on its own subform, allowing you to set the recordsource of that form to the calculating query and thus the control to the calculated field (not so attractive I think, but I'm striving for content here!).

Seeing your db might help if you remain stuck.

[Dave14867]

I can't post the data base but here is a pic of a record and what I want to do.




I want to have the query run after I put in the lot number in the subform (I populate that after the form has been populated. The units used is already there, I enter the Lot Number from paperwork I receive).

So I was thinking of using the AfterUpdate event on the Lot Number field.



Thanks

[Micron]

So I was thinking of using the AfterUpdate event on the Lot Number field.

That makes sense to me.

[Dave14867]

I have a query that I use for a report that Asks for a Lot Number and that works fine. I tried to create a new query to use to show QOH for this issue and I get the following error.





It's the QOH Expression that is causing the issue, that contains the following: QOH: Sum([UnitsReceived]-[UnitsUsed])

I am no good at SQL but learning slowly same with VBA but I know this should be possible.

Thanks

[mike60smart]

Hi Dave
Try [UnitsReceived]-[UnitsUsed] - remember you are calculating each record and not the Sum of anything

[Dave14867]

Mike,

but that gives me a list of all transactions for that lot number, what I want is 1 line that gives me the net sum. So for instance the query results in 29 rows, 1 for each transaction, what I need is 1 row that gives me the Net of how many went to stock and how many have been taken out total.

Maybe I could use 2 queries, 1 to give me the total of UnitsUsed and 1 to give me the total UnitsReceived and subtract 1 from the other?

[mike60smart]

Hi Dave

The example screen shot is confusing me anyway.

It shows QtyUsed Values but you have Zero Items in Stock?

[Micron]

Screenshots don't always help - especially in this situation as I suspect that is a Totals query based on the error message but the pic doesn't reveal that. Some here can do wondrous things if you post the sql, but usually that's not me. Having said that, I suspect your issue is that the problem field is not part of a Group By clause, or you don't even have a group by clause. Perhaps there is a Having clause instead.

[Dave14867]

Micron,

That's a great idea, post the SQL, I hadn't thought of that, it is below.

SELECT [Inventory Transactions].LotNum, [Inventory Transactions].UnitsOrdered, [Inventory Transactions].UnitsReceived, [Inventory Transactions].UnitsSold, [Inventory Transactions].UnitsShrinkage, [Inventory Transactions].UnitsUsed, [Inventory Transactions].CycleCountCorrection, [Inventory Transactions].UnitsOnNCR, [Inventory Transactions].Restricted, Sum(Nz([UnitsReceived])-Nz([UnitsShrinkage])-Nz([UnitsUsed])-Nz([UnitsSold])+Nz([CycleCountCorrection])-Nz([UnitsOnNCR])) AS QOH
FROM [Inventory Transactions]
WHERE ((([Inventory Transactions].LotNum)="802255"));


But, I don't want the LotNum = "802255", that was just for testing purposes, it should be the lot number just entered in the row of the subform from the above screen shot. Here is what I have from the query builder, [Forms]![DHREntryNewMethod]![frmDhrEntryInventoryTransactions].[Form]![LotNum]


Thanks

[mike60smart]

Hi Dave

The only way this will be sorted is if you upload a copy of the database with some sample data

[Micron]

All I can think of is that your use of Nz is just like a post I responded to a moment ago. If you do not specify the "value if Null" you get a zls (zero length string). Arithmetic with nulls is supposed to propagate ok AFAIK, but not if you try to subtract a zls from anything. Provide a value for the second half of the function - perhaps 0 and see what happens.