Avatar of jspc
jspcFlag for Australia asked on

Increment Qty from Scan

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
DatabasesMicrosoft Access

Avatar of undefined
Last Comment
Gustav Brock

8/22/2022 - Mon
PatHartman

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.
ASKER
jspc

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?
PatHartman

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
jspc

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.
ASKER CERTIFIED SOLUTION
Gustav Brock

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
jspc

Awesome! That's it. Thank you so much.
ASKER
jspc

Just one other thing, are you able to tell me how to set the default Qty to equal 1 and enter automatically?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gustav Brock

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

PatHartman

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.
Gustav Brock

Try my clever demo. There will be no duplicate barcode.
StockCount.accdb
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
jspc

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.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
jspc

Gee your good! Thank you, that's brilliant!
Gustav Brock

You are welcome!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.