Error and inventory checking dilema

[Ascentient]

My predecessor created a well-to-do ticketing program. His hindsight and lack of real world knowledge has left me in a bit of a jam.

The department that uses this program receives updated inventory counts once a week. (The night that their tickets are billed as invoices.) It has been a long standing issue between that department and certain "employee's" that it is a HUGE hindrance in their day-to-day operations when selling products. (I would certainly say so.)

So I took it upon myself with some help from that department to give them some kind of updated inventory count while they created tickets through out the week.

--Now time for the good stuff--
I made changes to the main form to subtract the quantities from Inventory. - That was the easy part.
The hard part comes when they go to correct a ticket. This typically happens when the customer wants more of the same product or has to much, as well as forgetting he needs such-and-such a part.

Ten "rows" were created by using the bounds fields of the table.
Example:
Material-Code-1 Qty-Ship-1 u/m-1 ...etc
Material-Code-2 Qty-Ship-2 u/m-2 ...etc
Material-Code-3 Qty-Ship-3 u/m-3 ...etc

So they are fields from a table but not what I call a true record. A screenshot of the form can be found here

http://smithadamr.googlepages.com/buildingmaterials

I have created the following code to be run when the corrections have been made and the ticket is saved.

Dim dblChange As Double
Dim strSQLUpdInvQtys As String
Dim l1, l2 As Variant
For lineno = 1 To 10
   If Me("[MATERIAL-CODE-" & lineno & "]") <> "" Then
      l1 = Me("[QTY-SHIP-" & lineno & "]").OldValue 'No Purpose-used to check values
      l2 = Me("[QTY-SHIP-" & lineno & "]") 'No Purpose-used to check values
      If Me("[QTY-SHIP-" & lineno & "]").OldValue <> Me("[QTY-SHIP-" & lineno & "]") Then
         dblChange = Me("[QTY-SHIP-" & lineno & "]").OldValue - Me("[QTY-SHIP-" & lineno & "]")
         strSQLUpdInvQtys = "UPDATE tblPrdct SET tblPrdct.INVENTORY = [tblprdct]![inventory] + " & (dblChange * 1) & " WHERE (((tblPrdct.MATERIAL)=""" & Me("[MATERIAL-CODE-" & lineno & "]") & """));"
         DoCmd.RunSQL strSQLUpdInvQtys
      End If
   End If
Next

The problem I managed to find is that if the Material Code gets changed, I am screwed. (Unless I check for it first.)
This leads me to believe each "record" should be processed individually. But I am not sure where to toss the code and for what else to check.

Thoughts, comments, suggestions, etc are very much appreciated.

Ascent

My thought is that I am going to have to error check per record

 

[TomCologne]

Hi Ascent,

Do I understand correctly that the quantity on hand is stored in "tblPrdct.INVENTORY" ?

That would not be good.


The tables below are very basic examples to illustrate the idea:

tblPrdct.MATERIAL'' = MATERIAL-CODE
tblPrdct.Desc '' = description
tblPrdct.Discontinued '' = Yes/No
tblPrdct.Price
tblPrdct.Note


tblInventory.MATERIALcode '' = tblPrdct.MATERIAL
tblInventory.Qty '' = quantity
tblInventory.Transaction '' = in (eg. product purchases by your company) / out (eg. Customer orders, missing products) or detailed
tblInventory.TransDate '' = Transaction date
tblInventory.Note

tblOrders
tblCustomers
etc

If the "MATERIAL-CODE" for a product changes = new product!

On hand qty for MATERIALcode = tblInventory.Qty(in/+) - tblInventory.Qty(out/-)

calculated on the fly, query or form or...


If a physical count differs from calculated On hand qty the difference amount is again a simple tblInventory.Transaction

I worked in a bank for some years and accounting was part of the drill, that is why the above makes sense to me.

Let me know if it's not as clear as I hope.


TomCologne

 

[Ascentient]

Thanks for the reply Tom. I spent the remainder of yesterday afternoon figuring out how to account for my issue, which worked. It then hit me this morning, to make the user delete the line item instead of allowing them to change it. Doing this method will be much easier and there should be few mistakes made by the end-user.

Ascent