Using MS Access/adding stock

Advertisement


Question
Hello Julie,
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?
Many thanks,
Hilde

Answer
Hi Hilde,

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()

End Sub

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.edit
   
   rst!Quantity = i + Me.AddQuantity 'Change the name of the fields
   rst.update

'Closes everything
   rst.Close
   db.Close

Exit Sub
ErrorHandler:
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.  

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Julie Misson

Expertise

My area of expertise is Microsoft Access 2000-2007. I have been building microsoft databases for the past twelve years. I would be competent in answering questions from novice Access 2000 users to the more advanced including VBA. If I cannot help, I am more than likely be able to point you to websites that can.

Experience

I have a Microsoft Access business where I build database for small businesses. These are usually one off designs, where there is no off the shelf software avaliable to meet the business needs. I also teach Microsft Access to beginners. I am the owner of the www.simply-access.com website.

Education/Credentials
Self taught in Microsoft Access. Have done some units in normalisation and SQL, but most of what I know I have learnt from books originally and more recently the Internet.

©2016 About.com. All rights reserved.