Inventory Tracking - Don't allow negative inventory order

[Guest_imported]

Thanks for your help

I have a small item inventory system that I have constructed that allows me to - after a few steps - calculate true inventory by product, by subtracting the total number of units sold of product X from total units added to inventory of product X (field in a query called "TrueInventory". These calculations exist in their own tables ,queries and forms.

What I want to do is on the form that allows the operator to sell units, when she pressed the button to process, some type of function will go and check and see if:
1. There is enough inventory of that item in the "TrueINventory" field to process the sale(Subtracting the number of ordered widgets minus the number of "TrueINventory" widgets does not result in a negative number). If there isn't enough inventory, the record would not be processed and a dialogue box would appear

All the inventory items have a unique number, so matching shouldn't be a probem, but I don't know how to write VBA well enough to have the DB do this checking behind the scenes on the spot.

Thanks for any thoughts you may have
Tomas

 

[MilinPCH]

I also have an issue similar to this - can anyone be of assistance?

 

[braindead2]

not seeing how the form is designed and such I have to be real generic here.
Using VBA code would look something like!
dim rst as recordset,db as database
dim strsql as string
strsql = "select TrueINventory from yourquery " _
& "where Uniqueid = " & me!uniqufieldonyourform
set db= currentdb
set rst = db.openrecordset(strsql)
rst.movefirst 'query should return only one record
if rst!TrueINventory < me.fieldwhereorderisfrom then
msgbox &quot;yourmessagehere&quot;
else
end if
rst.close

you would need to include error trapping in case no records are returned.
Another option would be to use dlookup
create a field on the from name it &quot;onhand&quot; and set its source to
=dookup(&quot;TrueINventory&quot;,yourqueryname, &quot;Uniqueid = &quot; & me!uniqufieldonyourform &quot then set validation for the field where you are removing stock to >[onhand]

 

[MilinPCH]

I was trying to use the solution above in my db, but I get the error:
Syntax error (missing operator) in query expression 'SKU='
and then Access highlights the Set Rst = line of code

Here is my code..............

Private Sub Item1Qty_LostFocus()

Dim rst As Recordset
Dim db As Database
Dim strsql As String

strsql = &quot;select ACTUALinventory from [InvInv]&quot; & &quot;where SKU = &quot; & Forms!InventorySoldSubTest!SKUNo
Set db = CurrentDb
Set rst = db.OpenRecordset(strsql)
rst.MoveFirst
If rst!SKU < Forms!InventorySoldSubTest!SKUNo Then
MsgBox (&quot;can't create a negative value in inventory&quot
Else
End If
rst.Close

I've checked the name of the fields and they are correct - and the format types match also (number). The form I'm referring to is a subform, but I have it open in the background when the code runs and it seems to be ok.

Thanks,
Milin

 

[MichaelRed]

strsql = &quot;select ACTUALinventory from [InvInv] where SKU = &quot; & Forms!InventorySoldSubTest!SKUNo

AT least this much. Maybe more, but you did not leave a space between [InvInc] and the where clause. Also, it is not necessary to concatenate the clauses.



MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[DaveShmave]

If I understand this correctly (trying to take a basic approach), the &quot;True Inventory&quot; is simply the Quantity On Hand or Quantity in Stock if you prefer. Also, this is a calculated field on the form? In other words, do you keep a running &quot;True Inventory&quot; or is this something you update manually?

If it's a running value and assuming you have some sort of &quot;Quantity&quot; field for the units you're selling as well as the &quot;True Inventory&quot; field on the form in question then try this:

Private Sub txtQuantity_BeforeUpdate(Cancel As Integer)

If IsNull(txtQuantity) Or (txtQuantity) < 0 Then
MsgBox &quot;The quantity entered MUST be greater than or equal to 0.&quot;, vbInformation
End If

If txtQuantity > Val(txtTrueInventory) Then
MsgBox &quot;The quantity entered CAN'T be greater than the true inventory.&quot;, vbInformation
End If

End Sub

Substitute whatever fields and message so it matches your requirements.

If that's not quite it, post back and maybe I can help you set up the query in VBA so you can have an updated value for the unique product you're working on.

HTH

Dave X-)

 

[MichaelRed]

Actually, in many sales/order apps, the inventory should be allowed to be negative. For Retail, especially you wouldn't want to have to tell a customer they can't buy an item - because it isn't in stock - when they have it &quot;in hand&quot;.

I know your app MUST not be retail POS, however the same issues can/may apply. Inventory is often inaccurate. Why it is inaccurate is not really important. The fact/reality of the inaccuracies is important.

You should have a process to verify inventory/availability ammounts before committing orders to customers AND a process to:

[tab]alocate the items to the customer
[tab]mark allocated items with the customer info
[tab]return items to inventory (canceled orders)
[tab]remove customer info from items when returned to inventory.

Wholesale order processing (I assume that is what you app is for) cannot be done through 'computerization' alone. Someone needs to handle the physical inventory AND they need to interact w/ the order processing.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[MilinPCH]

I'm going to bump this back up, as I have just gotten a chance to try out your suggestions....MichaelRed's suggestions seem to be the closest to what I want. I tried it again but get the same syntax error with SKU=

Here's what I wrote:
Private Sub Item1Qty_LostFocus()
Dim rst As Recordset
Dim db As Database
Dim strsql As String

strsql = &quot;select ACTUALinventory from [InvInv]where SKU = &quot; & Forms!InventorySoldSubTest!ProdNo
Set db = CurrentDb
Set rst = db.OpenRecordset(strsql)
rst.MoveFirst
If rst!ACTUALinventory < Forms!InventorySoldSubTest!ProdNo Then
MsgBox (&quot;can't create a negative value in inventory&quot
Else
End If
rst.Close

End Sub


Item1Qty is a field on InventorySoldSubTest - it is the final field a user chooses, so all other info is already entered on that line item (item name, item number, date, invoice no). It is the quantity of item requested to be sold.

InvInv is a query with the field ACTUALinventory that contains the inventory (by item) in the system. In the same query, the SKU field has a number with is the id of the product, so it looks like: SKU ACTUALinventory


Form InventorySoldSubTest displays in datasheet view.
InventorySoldSubTest is actually a subform within a larger invoice form. I have InventorySoldSubTest open (user can't see it). On this form I have a field that populates with the number of the product selected (user selects the item in a dropdown before the Item1Qty field - then the field automatically fills in the item number) this number field is called ProdNo and sources back to the same info the SKU field does (in InvInv query), so ProdNo and SKU are using the same data - the number id of the products.

as far as one poster saying I should use negative inventory - I can't in this situation...this db is for a non-profit group that tracks it's supply of customized trinkets and such - there really isn't any money changing hands anyway. They just need to keep track of who they've give promo materials to and how much they have left on hand.

Ideally, I'd like to make this work as it is the last technical goal I had in making this db work. Otherwise, I have set it up so that they keep open a &quot;current inventory form&quot; that lists counts by product. As they process their ordering on another form, the inventory form updates. So they would have to keep an eye on what inventory is on their own.

That's my story. Whether I get this to work or not, I already have learned from all your comments - thanks!
Milin

 

[Guest_imported]

Thank you Milin for the questions. I too would be interested in responses to him.

Tomas

 

[braindead2]

is SKU from the original table a Number or Text Field
If SKU is a number field then the sql needs to be
&quot;select ACTUALinventory from [InvInv]where SKU = &quot; & me!ProdNo
if sku is a Text field the it need toi be

&quot;select ACTUALinventory from [InvInv]where SKU = '&quot; & me!ProdNo & &quot;'&quot;
the sql says select actualinventory records from INVINV query where then sku (number or text) matches the field on your form named prodNO.

Be sure the field is named ProdNo and not test1 or something

 

[braindead2]

Came back to check to see if the above helped and I noticed another some other problems with your code

If rst!ACTUALinventory < Forms!InventorySoldSubTest!ProdNo

rst!ACTUALinventory should return the remaining inventory amount, Corrrect?

If as you describe, ProdNo should be the SKU number. Some where else on the form should contain the amount they are trying to order I believe it is Item1qty

I believe what you want is
If rst!ACTUALinventory < me!Item1Qty

Also you say
&quot;InventorySoldSubTest is actually a subform within a larger invoice form&quot;
based on where the code is being fired from you may need to say me!InventorySoldSubTest!Item1Qty and the same holds true for the earlier post. You may have to say
me!InventorySoldSubTest!ProdNo
you see the actual way to refer to the subform would be
Forms!Mainformname!Subformname.form!FieldName
Good Luck