Results 1 to 16 of 16

Thread: Error message if duplicate part number from barcode reader

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jun 2014
    Posts
    19

    Error message if duplicate part number from barcode reader

    Good morning, everyone. First post. I'm sure you guys can help!

    I'm an engineering co-op and have been tasked with modifying one of our programs written in Visual Basic. Unfortunately, I have no experience with visual basic (but I do have programming experience in other languages like SAS, R, and MATLAB). We recently had a quality issue in which the same barcode was scanned twice, and our program allowed it to happen (hence the necessary modifications).

    I'm going to make more sweeping changes as I become familiar with the language and the program, but I must prevent this quality problem from happening again. My mentors would like this change by Wednesday, they say, but I'd like to finish it up today.

    I've attached the code which accepts the barcode scanner information. At the end, I'd like to add another ElseIf statement which checks to see if a certain barcode has already scanned, but I have no idea how to do this. As a side note - these values are stored in a separate sheet within the workbook when they're scanned by the barcode reader. Can anyone help?

    Sample barcode output is at the bottom of this program. I'd like to check to make sure no codes are scanned more than once.
    Attached Files Attached Files

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Error message if duplicate part number from barcode reader

    Since this appears to be in Excel it should probably be moved to the Office section. VBA != VB6

    As to the issue at hand are you trying to prevent a double scan or trying to prevent something from being scanned if it has been scanned at anytime?

    A double scan prevention is simple, just create a variable to hold the data from the last scan and then when a new scan comes in check against that variable if = then it is a double scan and should be ignored else update the variable to reflect the one just scanned and process normally

    In the other case you would have to do a look up on the data you have already processed to see if the barcode was processed already.
    I do not work in Excel VBA and try to avoid using Excel for anything like this so I'm not sure what you would have to do to do the lookups if need be. It is very simple using a DB, should be simple in Excel as well but I have never looked into it.

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Jun 2014
    Posts
    19

    Re: Error message if duplicate part number from barcode reader

    Can a moderator please move this thread to the appropriate section?

    I am attempting to prevent the latter case, which would still work to prevent double scans. Not sure how I'd script a lookup, but hopefully someone in the Office section has some ideas. I'll keep searching until then.

    Thanks!

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Error message if duplicate part number from barcode reader

    Welcome to VBForums

    Thread moved to the 'Office Development/VBA' forum... note that while it certainly isn't made clear, the "VB Editor" in Office programs is actually VBA rather than VB, so the 'VB6' forum is not really apt

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Jun 2014
    Posts
    19

    Re: Error message if duplicate part number from barcode reader

    I'm attaching to this reply the overarching program (the one which calls the sub-program from the original post). This might help those who are currently having a look at this.

    Thanks!

    BatchTravelerFull_VBForums.txt

  6. #6
    Addicted Member 3com's Avatar
    Join Date
    Jul 2013
    Location
    Spain
    Posts
    253

    Re: Error message if duplicate part number from barcode reader

    I have not examined your code in depth. but according to what you say here:

    As a side note - these values are stored in a separate sheet within the workbook when they're scanned by the barcode reader
    Since you store all the values in a spreadsheet, Why does not look that value, before performing the scan?

    Assume you've, lets say "serialNumber" in a column (A), and you named this range as "SerialRange".
    Now you can search for value before do nothing, ie:

    Code:
    Dim c
    Dim SerialNum ' called to store current barcode value 
    
    Set c = range("SerialRange").Find(SerialNum, LookUp:=xlvalues)
    
    If c is nothing then 'barcode value has not been scaned before
      'Perform scan and store his value in the spreadsheet
    Else
      'Do nothing
    end if
    HTH
    Last edited by 3com; Jun 16th, 2014 at 04:14 PM. Reason: Adding quotes

  7. #7
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Error message if duplicate part number from barcode reader

    @3com That does not make any sense. You can not know if you have scanned that barcode before if you have not scanned it yet.

    You must first scan the barcode and then check to see if that data exists in the data already recorded. It is not possible to do before you scan the barcode as you would not know what you are looking for.

    The order must be
    Scan
    Lookup
    Record If not found

  8. #8
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Error message if duplicate part number from barcode reader

    I think you and 3 are saying the same thing, maybe a bit was lost in the translation.

  9. #9
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Error message if duplicate part number from barcode reader

    Quote Originally Posted by vbfbryce View Post
    I think you and 3 are saying the same thing, maybe a bit was lost in the translation.
    I don't think so, the little code snippet seems to be looking for SerialNum which has not been initialized yet and then only if not match is returned is the scan preformed.

    What has to happen is the scan must be performed and then the lookup must look for the value that was actually scanned and then determine if it should record the value or not.

    Or maybe it is just a misleading sample and the comment out from the Dim statement means that you would need to populate that with the scanned value before the next line is executed but if so it is far from clear

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Jun 2014
    Posts
    19

    Re: Error message if duplicate part number from barcode reader

    I appreciate the feedback guys. Still looking to solve this. I will be out most of the day today in training, so any responses to your pearls of wisdom will likely be delayed. I'm still trying some things out, including macro recording, but we'll see how it goes.

  11. #11
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Error message if duplicate part number from barcode reader

    Or maybe it is just a misleading sample and the comment out from the Dim statement means that you would need to populate that with the scanned value before the next line is executed but if so it is far from clear

    DM,

    This is what I believe he was trying to do, yes, but you're right: far from clear!

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Jun 2014
    Posts
    19

    Re: Error message if duplicate part number from barcode reader

    I managed to fix this using Data Validation (no VBA needed!). Thanks guys!

    My solution:

    Go to Data Validation
    Under Allow, select Custom
    In the formula box, I inserted: =countif($C$10:$E$21,C10)=1
    Checked the box for "apply these changes to all other cells" (only need to do this if you haven't already selected the entire range)
    Then went to the "Error Alert" tab, selected stop, and inserted some verbiage to help anyone receiving the error.

    Thanks guys! I'm sure I'll be back with some questions as this summer term progresses.

  13. #13
    Addicted Member 3com's Avatar
    Join Date
    Jul 2013
    Location
    Spain
    Posts
    253

    Re: Error message if duplicate part number from barcode reader

    far from clear!
    Yeah guys, you both are right, I apologize for that.

    @ traveler
    Glad you get del with.

    I'm sure I'll be back with some questions as this summer term progresses.
    No problem, promise I will try to be more clear the next time.

  14. #14
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Error message if duplicate part number from barcode reader

    No need to apologize, 3. English is not everyone's native tongue, am I right?!

  15. #15
    Addicted Member 3com's Avatar
    Join Date
    Jul 2013
    Location
    Spain
    Posts
    253

    Re: Error message if duplicate part number from barcode reader

    Quote Originally Posted by vbfbryce View Post
    No need to apologize, 3. English is not everyone's native tongue, am I right?!
    Of course!

  16. #16

    Thread Starter
    Junior Member
    Join Date
    Jun 2014
    Posts
    19

    Re: Error message if duplicate part number from barcode reader

    Hey guys! Turns out the data validation trick did not work, and my guess is because the macro was overriding everything else in the workbook. It just passed right over data validation when attempting to run. I then did some macro recording with the data validation and attempted to copy this into the program, but this did not run either (even though a separate macro which I recorded for conditional formatting DID run, and I had them in the same place).

    What did work was to actually redimension the variables in the two arrays in question from As Long to As String (these arrays were PgNumSerialArray and NumSerialArray, or something like that). There was actually already code within the program to check for duplicates, but it was not working. This change fixed that problem, but for reasons I do not understand (can you guys figure it out? it's not a huge issue, obviously, but my goal is to learn VBA, not just get lucky).

    Doing this also tends to make the loops run a little longer. The error form which pops up can be seen looping (it might loop five or six times) before returning to the input screen. This is something else I don't quite understand, but am looking into.

    As of now, however, the program works - albeit a little inefficiently. We shouldn't have anymore quality issues at least

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width