compare data entry to current count in inventory

[bmazur]

hi
I'm developing Inventory Audit. I have a form where user will enter box count for specific barcode. I need to verify if that count is correct. I do have a query that gives me current count for the same barcode, and I have a check box yes/no. Before update I'd like to compare value in my data entry field to a query and update check box to yes if they match.
here is my query
SELECT tblBrochureShipments.BrochureShipmentID, tblBrochureMovement.WarehouseID, Sum(tblBrochureMovement.NumberOfBoxes) AS SumOfNumberOfBoxes
FROM tblWarehouse INNER JOIN (tblBrochureShipments INNER JOIN tblBrochureMovement ON tblBrochureShipments.BrochureShipmentID = tblBrochureMovement.BrochureShipmentID) ON tblWarehouse.WarehouseID = tblBrochureMovement.WarehouseID
GROUP BY tblBrochureShipments.BrochureShipmentID, tblBrochureMovement.WarehouseID
HAVING (((tblBrochureShipments.BrochureShipmentID)=[Forms]![frm_Audit]![frm_AuditDetail1].[Form]![BrochureSchipmentID]) AND ((tblBrochureMovement.WarehouseID)=[Forms]![frm_Audit]![WarehouseID]))
ORDER BY tblBrochureShipments.BrochureShipmentID, tblBrochureMovement.WarehouseID;


Users populate [frm_AuditDetail1].[Form]![AuditCount]


And I have AuditMatch (yes/no) that I need to update to yes if [AuditCount] and Sum(tblBrochureMovement.NumberOfBoxes) AS SumOfNumberOfBoxes match.

thank you in advance
Barb

[June7]

So you want to show result of that query on form and then user look at both values and click the checkbox if appropriate? or do you want the checkbox automatically checked?

Don't you need a date/time parameter for the selection of records? On what date was the box count taken? Only transactions up to that point should be included in comparison. Afterall, business doesn't come to a halt just because of an audit.

The checkbox field may be extraneous. With a timestamp the query count can be calculated and compared to box count whenever needed and the result displayed even if the 'check' is never saved to table. This is appropriate treatment for calculated data (not saving calculated values). And the 'check or no check' is really the result of a calculation.

[bmazur]

no, I don't want to show the results to the user. But as soon as user enters data in the record, I want to compare it and update yes to my check mark, so I can then review it. Doing it live allows me not to worry about DateTime stamp.

[June7]

Lack of time stamp and saving the calculated value runs risk of 'disconnect' from the raw data. Will not be able to replicate and substantiate the results. Auditors don't like that.

However, if you must, then need code in some event. The real trick is figuring out what event. Perhaps textbox AfterUpdate?

Me.checkbox = Nz(Me.Textbox,0) = Nz(DLookup("SumOfNumberOfBoxes", "queryname"),0)

[bmazur]

thank you. worked like a charm. i know this would not get past auditors, but it won't need to. It's just a small internal thing.