Using MS Access/adding stock
I am a very beginner with Access. I am trying to design a database to control stock.
I have a simple problem, but I can't find the answer when I google it.
I have a table with stock on hand. Each product has a unique batch number. I want to create a form in wich I can find the product by batch number with an empty field for quantity. In that field I want to be able to add a number. That number then should then be added to the quantity in my stock table. The next time I use this form, the field for quantity should be empty again for adding a new number etc.
I hope I explain it clearly.
Is there an easy solution for this?
Thank you for the question. It seems you have your drop box and text box sorted, so I will not delve into these.
To achieve what you are asking you will need to write some code. I will step you trough as best I can. I have given the fields/table the following names, wherever they appear, replace with your own.
Table where Batch Number and Quantity are stored: tblBatches.
Batch Number - BatchNumber
Quantity - Quantity
ComboBox - cboBatchNo
Blank Text Box - AddQuantity
Both the above can be changed in the 'Other' tab in properties, if you wish to change the name to reflect mine.
I am not sure what version of Access you have, so the following step may be slightly different, but hopefully you can work it out. If not let me know.
Select the Quantity text box, open the properties window, rhen click on the Event tab. Next to 'After Update', click on the drop down arrow and choose [Event Procedure] then click on the button with the three dots. A window with the following two lines of code will be displayed.
Private Sub AddQuantity_AfterUpdate()
In between these two lines of code add the following code:
On Error GoTo ErrorHandler
Dim db As Object
Dim rst As Object
Dim i As Integer
Set db = Application.CurrentDb ' Refers to the current Database
'Open the table containing the batches
Set rst = db.openrecordset("tblBatches") 'Change to the name of your table
'Move through the records until it finds the batchnumber that matches the combobox. Note the 0 is presuming it is in the first or only column
rst.FindFirst "[BatchNumber] = " & Nz(Me!cboBatchNo, 0) ' Change BatchNumber to the name of your batches field and cboBatchNo to the name or your combo box
'Stores the value of the Quantity field in a variable to use later
i = rst!Quantity
'Lets the table know it will be editing the table and adding the new number to the quantity field
rst!Quantity = i + Me.AddQuantity 'Change the name of the fields
MsgBox "Error Number: " & err.Number & " " & err.Description, , "Error Message"
Close the coding window by clicking on the outer most X.
Now test it out, by selecting a batch number from the drop down list, adding a quantity then tabbing.
An alternative if you did not want to do it on the After Update event is to add a button, and add the code there instead on the on click.
Hope this of some help.