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.
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.
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.
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
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.
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
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.
Re: Error message if duplicate part number from barcode reader
Originally Posted by vbfbryce
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
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.
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!
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.
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