Automatic Quantity, and Input After Every Barcode Scan

[conton]

Hello!

I have a database that keeps track of customer orders of one product. I have an order form that has many fields, the two pertinent to this question being the Quantity and the Barcode field. I use a scanner to input the barcodes. At the moment, I manually input the amount of product I am scanning for the order in the Quantity field, and put a comma and a space after each barcode I scan into the Barcode field.

Here is what I would like to accomplish:

1. For Access to automatically input the comma and space after each scanned barcode in the Barcode field. I don't know what input my scanner produces, but I assume it is simply a keyboard emulator. So, I imagine the only way to accomplish this would be to have the Barcode field insert a comma and a space after every 9 characters entered (ie. barcode format XX-XXXXXX).
2. For Access to automatically count the amount of barcodes I have scanned into the Barcode field and store that amount in the Quantity field. The only way I can think to do this is to have the Quantity field count the amount of characters in the Barcode field, and produce the amount based on that. So, for the Quantity field to count 1 for every 11 characters in the Barcode field (ie. 9 barcode characters, a comma character, and a space character).

So, in short, I would like to simply scan the barcodes, and not have to manually input the comma and space after each barcode, or the quantity of barcodes I have scanned.

Thanks in advance!

 

[Phildaburn]

I don't understand. If there is only one product (one barcode), why do what you're doing?

 

[conton]

I don't understand. If there is only one product (one barcode), why do what you're doing?

Each unit of product has a unique barcode. An order can consist of several units of product, and therefore several unique barcodes.

 

[boblarson]

You should be adding a record for EACH barcode. It should be a junction table which lets you add as many as you need. They should not be added on in the same field. That violates the rules of normalization and makes getting the data back out hard as hell.

 

[conton]

You should be adding a record for EACH barcode. It should be a junction table which lets you add as many as you need. They should not be added on in the same field. That violates the rules of normalization and makes getting the data back out hard as hell.

Actually, in my old system, I had five separate fields for five unique barcodes. The obvious problem there was that, if there were more than five units ordered, I couldn't store all of the barcodes. That is why i am trying to edit that old system to allow me to scan unlimited barcodes for each order.I am not familiar with junction tables, but it sounds like what I need. I will play around with a junction table conversion.

In that case, I would need a different solution for my quantity problem as well.

Thanks for the advice!

 

[boblarson]

If you have the junction table you can have a field for the barcode, the ProductID and the quantity. If it is a subform on the products form, then the productID can be filled in automatically by Access. Then you should be able to enter quantity, hit the enter key and have the after update event of the text box move you to the barcode text box and then you can scan and in it's after update event you could move to a new record.

 

[conton]

Hi there! How do I go about making the after update event of the barcode move to a new record?

 

[boblarson]

Hi there! How do I go about making the after update event of the barcode move to a new record?

In the After Update event you would put:

DoCmd.RunCommand acCmdRecordsGoToNew

 

[conton]

In the After Update event you would put:

DoCmd.RunCommand acCmdRecordsGoToNew

I put that code in the barcode text box's AfterUpdate routine, and it doesn't appear to be moving to the next record. It just moves to the next text box.

When is the afterupdate routine triggered? For instance, if I were to input the barcode manually and hit enter, would that trigger the AfterUpdate code?

 

[boblarson]

If you put it in manually and hit enter, yes it would trigger. But the after update event won't fire if the value is updated via code. In that case you need to call it explicitly.