We help IT Professionals succeed at work.

Increment Qty from Scan

jspc
jspc asked
on
80 Views
Last Modified: 2018-11-05
Hello,

I have the attached Microsoft Access Database with two columns:
•      UPC (Barcode)
•      Quantity

I want to be able to scan a barcode to the database. If the same barcode is scanned I want the qty of the first scanned line item to increment to 2 rather than creating a new line.

So I want to combine the Qty column (or Sum) if the same barcode is scanned.

Are you able to help me with that? The attached database can be changed.

Thank you.
StockCount.accdb
Comment
Watch Question

CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
You can't do this in one step.  First you need to use something like a dLookup() to determine of the Barcode already exists.  If it exists, then you would run an update query to increment the quantity field.  If it does not exist, you would run an append query to add a new record.

Author

Commented:
Oh ok .. but I'm actually scanning the data in from each product. (This is how its added)

If I was to have the data in an Excel file (Barcodes) could it then be done?
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Scanning into Excel rather than an Access form just adds unnecessary complications and you have the same issue.  If the Barcode exists, you need to UPDATE but if the Barcode does not exist, you need to INSERT.  These are two different operations.

Author

Commented:
I'm scanning into Access, not Excel.

I am adding all the barcodes from each stock item into an access database.
Each scan is a qty so many scans of the same product I want to consolidate those same products.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Awesome! That's it. Thank you so much.

Author

Commented:
Just one other thing, are you able to tell me how to set the default Qty to equal 1 and enter automatically?
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Yes:

Private Sub UPC_BeforeUpdate(Cancel As Integer)
    
    Dim rs  As DAO.Recordset
    
    Set rs = Me.RecordsetClone
    rs.FindFirst "UPC = '" & Me!UPC.Value & "'"
    If Not rs.NoMatch Then
        rs.Edit
            rs!Quantity.Value = rs!Quantity.Value + 1
        rs.Update
        Me.Undo
        Cancel = True
    End If
    rs.Close
    
    If Not Cancel Then
        Me!Quantity.Value = 1
    End If
    
End Sub

Open in new window

CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
I would not scan into a bound control.  You don't want to dirty the current record with a duplicate barcode.  If you do, you will also have to deal with that issue because you don't want Access to try to save the duplicate record.

As I suggested earlier, don't even use the form's recordset.  Just run update or insert queries.

You can provide the value for the quantity in the insert as Gus suggested or you can modify the table design and set 1 as the default value for the quantity.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Try my clever demo. There will be no duplicate barcode.
StockCount.accdb

Author

Commented:
I am stocktaking barcodes so there will be barcodes which are the same.

Thanks, Gustav Brock that is awesome!

My last question is, when you enter a new barcode the default qty is 1, then you need to hit the "Enter" key to accept the data. Can the "Enter" key step be automated so you don't need to touch the keyboard at all? Just scan and accept.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Gee your good! Thank you, that's brilliant!
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.