Form only works right in debug mode (VBA Excel)

[oldfossil35967]

This freaking me out!

I have a simple user form:
1) scan value with CCD barcode scanner
2) retrieve description with SQL
3) position to quantity field, key quantity
4) update quantity value to row found using lookup code
5) clear old input values and return to barcode input textbox

The form works perfectly when barcode values are entered via keyboard, then quantity is keyed, form updates and cursor returns to textbox1 as needed.

If you use the scanner, everthing works, except when you press enter after keying the quantity the update to the spreadsheet row does not occur nor do the data fields clear and cursor return to the textbox1 barcode input field.

If I debug any statement in the macros, any statement at all, barcode scanning works same as keyboard entry, but ONLY with debug on!

In other words, the code is perfect, but it only works when using the barcode scanner in debug mode. (The values scanned by the scanner are correct, the SQL lookup/description retrieval works, you can key the quantity ok, but when you press enter on the quantity, nothing further happens, no event fired code executes.)

This is a USB port scanner. Is it possible it is turning off some interrupt handler until I set debug on?

Any clues or similar experiences out there?

 

[ettienne]

Open Notepad and scan a barcode, check for any additional characters that may be added to the string, including non printable characters. Some scanners can be set to send a LF+CR and this could be fouling up your code.
Running in debug mode can affect this.

 

[oldfossil35967]

Thanks, Ettiene.

That was a good idea. It finally resolved itself as a coding issue.

I was proceeding under a false assumption, namely that the data entry sheet with the barcodes contained only part numbers that existed in the master spreadsheet I was filling in quantity data for.

Turns out the data entry sheet did not have a one-for-one relationship to the spreadsheeet and contained entries not on the spreadsheet. Out lookup code did not have an error trap or logic branch indicating what to do if no corresponding spreadsheet row existed.

My testing process was also faulty. When hand-keying entries to the barcode input field I was deliberately only keying entries that had a match on the visible spreadsheet, so it only appeared as if the code was working 100%.

After twenty years, you'd think I'd know better to assume some exotic cause of an inexplicable test result. The answer is always in the code, or lack of code in this case.

Chalk up another stupid programmer trick to me!