Solved Barcode based inventory db duplicate handling

[mamradzelvy]

Hello, i'm rather new to access and as my first project i've started working on an inventory type db which is using barcodes as primary key, however as our office is a hot mess, it happens on the regular, that while adding items to it, we encounter duplicates and since i have the collumn for the barcode id set up to forbid duplicates, its throws out an error.

My question is: would it be possible in access to recognize the attempt to input a duplicate value and instead take only the value of "amount" and add it to the existant records current amount? Let's say i have an item with the barcode 12345, for instance a hdmi cable. now in my inventory db the item 12345 already has got an entry with 20 pieces in stock and i just filled out a input form with some 5 more pieces, i would like to scrap the current forms values such as name and type and other properties i have preset and only keep the value and add it to the existing record to make it say 15, but only in case that the selected inventory type on the current form (in this case "cable") matches the already existing records value "cable")

I have been trying to find a way to make this happen, however i've been unable to, as my knowledge of vba and access in general is quite limited at the moment. i would greatly appreciate any input on this, thank you!


edit: i apologize for any grammar mistakes, im Czech.

 

[The_Doc_Man]

The error occurs when you take steps to do the append operation that adds a new record. Until you do the append, that duplicate doesn't matter. So the question is, what do you do to attempt the append for the case when the ID is NOT a duplicate? A button click event? A simple navigation action? Some other action?

Whatever you do, there will be some kind of event that occurs that is associated with the action. But until we know the action you perform, we can't specify the method of preventing the duplication because it would be event-specific.

Having said that, the general answer to your question is that when that event "fires" then before you allow it to proceed, you test for this possible duplication and disallow it to complete the Append (new record) step if it IS a new record. You could then perhaps develop some code in that same event to do an Update instead of an Append.

So you see, it wouldn't be that hard to do, we just need a little bit more info to decide WHERE (and WHEN) to do it.

 

[mamradzelvy]

The error occurs when you take steps to do the append operation that adds a new record. Until you do the append, that duplicate doesn't matter. So the question is, what do you do to attempt the append for the case when the ID is NOT a duplicate? A button click event? A simple navigation action? Some other action?

Whatever you do, there will be some kind of event that occurs that is associated with the action. But until we know the action you perform, we can't specify the method of preventing the duplication because it would be event-specific.

Having said that, the general answer to your question is that when that event "fires" then before you allow it to proceed, you test for this possible duplication and disallow it to complete the Append (new record) step if it IS a new record. You could then perhaps develop some code in that same event to do an Update instead of an Append.

So you see, it wouldn't be that hard to do, we just need a little bit more info to decide WHERE (and WHEN) to do it.

Thank you for the feedback, i haven't installed any controls for this particular form, i only put in a dummy button with a floppy disk icon, but it only does the following:

Private Sub btnSave_Click()
DoCmd.GoToRecord , , acNewRec
End Sub

My goal is to catch the duplicate once the form is filled out and the button is clicked, i was thinking of a pop up window which would ask whether i would like to merge the "Instock/ammount" values of the duplicite data in the form with the existing records values or discard it all together.

 

[The_Doc_Man]

OK, when you do that .GoToRecord, you navigate - which means you leave the record you were on. If that record is "dirty" (has unsaved data on it) then Access saves it automatically before moving to the new record.

Before you execute the .GoToRecord, you need to test for a couple of cases.

If the form is dirty, you WILL have to do something with it BEFORE you navigate; otherwise, you will risk that error message popping up. Just for completeness, here is a link about "Dirty."

Form.Dirty property (Access)

Office VBA reference topic

docs.microsoft.com

If you have nothing on the form, your button to move to a new record is benign. If the form content would not cause a duplicate, the button is also benign. The only problem is if you have data that would cause a duplicate. To fix this, you could write code to do some simple-minded testing. In what I'm going to show you, I have no idea of your field names so you will have to substitute the correct names. Watch out for the quote-apostrophe-quote sequence, it can be confusing in print sometimes.

....
Dim strSQL AS String
...  
    IF Me.Dirty = TRUE THEN                                                 'can there be a possible conflict?
        IF DCount( "*", "tblInventory", "[BarID]='" & CStr( Me.[BarID] ) * "'" ) <> 0 THEN       'yes.  check for conflict.
            strSQL = "UPDATE tblInventory SET [AmtInStock] = [AmtInStock] - " & CStr( Me.[Amount] ) & " ;"
            CurrentDB.Execute strSQL, dbFailOnError              'do an UPDATE rather than saving
            Me.Undo                                                                'prevent the automatic save by leaving nothing to save
        END IF
    END IF
    DoCmd.GoToRecord, , adNewRec                                'now go on to the new record

This says "If the form is dirty then if the bar-code ID would be a duplicate then update the record and ERASE THE FORM. If it would not be a duplicate or if the form has nothing on it, do thing. When the tests are done, fall through to move to the new record."

Now, having shown you how to fix your immediate problem, I have to tell you that this is not the preferred way to do inventories. The way that most people use is a transaction-oriented method. If you were using the sum-of-transactions method, you would not have had the problem in the first place. I'm showing you the fix for what you have, but long-term you should look up "Inventory" in this forum to see articles based on creating a transaction history that lets you just sum up transactions to get current inventory.

 

[mamradzelvy]

OK, when you do that .GoToRecord, you navigate - which means you leave the record you were on. If that record is "dirty" (has unsaved data on it) then Access saves it automatically before moving to the new record.

Before you execute the .GoToRecord, you need to test for a couple of cases.

If the form is dirty, you WILL have to do something with it BEFORE you navigate; otherwise, you will risk that error message popping up. Just for completeness, here is a link about "Dirty."

Form.Dirty property (Access)

Office VBA reference topic

docs.microsoft.com

If you have nothing on the form, your button to move to a new record is benign. If the form content would not cause a duplicate, the button is also benign. The only problem is if you have data that would cause a duplicate. To fix this, you could write code to do some simple-minded testing. In what I'm going to show you, I have no idea of your field names so you will have to substitute the correct names. Watch out for the quote-apostrophe-quote sequence, it can be confusing in print sometimes.

....
Dim strSQL AS String
... 
    IF Me.Dirty = TRUE THEN                                                 'can there be a possible conflict?
        IF DCount( "*", "tblInventory", "[BarID]='" & CStr( Me.[BarID] ) * "'" ) <> 0 THEN       'yes.  check for conflict.
            strSQL = "UPDATE tblInventory SET [AmtInStock] = [AmtInStock] - " & CStr( Me.[Amount] ) & " ;"
            CurrentDB.Execute strSQL, dbFailOnError              'do an UPDATE rather than saving
            Me.Undo                                                                'prevent the automatic save by leaving nothing to save
        END IF
    END IF
    DoCmd.GoToRecord, , adNewRec                                'now go on to the new record

This says "If the form is dirty then if the bar-code ID would be a duplicate then update the record and ERASE THE FORM. If it would not be a duplicate or if the form has nothing on it, do thing. When the tests are done, fall through to move to the new record."

Now, having shown you how to fix your immediate problem, I have to tell you that this is not the preferred way to do inventories. The way that most people use is a transaction-oriented method. If you were using the sum-of-transactions method, you would not have had the problem in the first place. I'm showing you the fix for what you have, but long-term you should look up "Inventory" in this forum to see articles based on creating a transaction history that lets you just sum up transactions to get current inventory.

I forgot to thank you for your kindness, the input you have provided to me has been very helpful! Many thanks!

 

[Gasman]

Doc,

Shouldn't that be

 strSQL = "UPDATE tblInventory SET [AmtInStock] = [AmtInStock] + " & CStr( Me.[Amount] ) & " ;"

 

[The_Doc_Man]

@Gasman - I should put disclaimers on my advice. For some reason lost in the fog of late-night posts, I though the OP was removing from inventory.

 

[Gasman]

@Gasman - I should put disclaimers on my advice. For some reason lost in the fog of late-night posts, I though the OP was removing from inventory.

Well you know how some posters literally copy and paste offered code, sometime without even modifying the field/control names. , so I thought it might be worth mentioning in case this was one of those times.