New user needs help

[Gener]

Hello, I am trying to implement an inventory. I want one simple task- create a program/macro to prompt user input for an ID, then it will add or subtract one from the quantity field of that object.

I am trying to use a barcode scanner after this as well, but in the meantime I would like to have this one simple task. Thank you!

 

[G37Sam]

Here's one way of doing it, enter the below code in a Module. This code assumes your table name is tblInventory, your Quantity field name is QTY and your Item ID field name is ItemID.

Sub AddToInventory()
dim tb as dao.recordset
set tb = currentdb.openrecordset("Select QTY from tblInventory where itemID = " & inputbox("Enter Item ID"))
if tb.eof = false then
  tb.edit
  tb!QTY = tb!QTY + 1
  tb.update
end if
tb.close
End Sub

Hit F5 to run that Sub procedure and report back

 

[Gener]

Holy crap, thank you so much! This worked perfectly. Don't want to be more annoying, but say you are in a warehouse and you want this to constantly be running (instead of clicking on the module each time you want to scan something), how would you do this? Could you have some loop that is constantly running this module with a macro? Like

while(true)
run(AddToInventory())

break if(Press cancel)

Sorry for poor pseudo coding, but hopefully you understand what I'm saying.

Thank you so much again! I appreciate your help.

 

[G37Sam]

What you can do is, create a form, put a text box in it.

And then on click of a command button (or before update of the text box itself?) you can execute that Sub procedure without the inputbox part.

Sub AddToInventory()
dim tb as dao.recordset
set tb = currentdb.openrecordset("Select QTY from tblInventory where itemID = " & me.YourTextBoxName
if tb.eof = false then
  tb.edit
  tb!QTY = tb!QTY + 1
  tb.update
end if
tb.close
End Sub