Barcode Scanner help (1 Viewer)

[Croenbaugh]

Hi. My small manufacturing company is coming out of the dark ages. Not much, but a little. We use an access database for bill of lading, and product trace back. The database keeps track of truck numbers, and what product goes on the truck. One of the tables is two columns, Load number, and Pallet number. A load can have many pallets, but pallet numbers are unique. This looks like an ideal use of bar codes and a bar code reader. No problem right. Right... So we add bar codes to the pallets. Easy. We add bar codes to the truck load sheet. Easy.

Now the hard part. I want to give a 18 year old with a short attention span the scanner. 1. Scan the truck number... 2. Scan the pallet number. Works great. Access thinks the scanner is a key board. Have the truck number generate a tab, and have the pallet number generate a return. Perfect. Until the 18 year old stops paying attention and scans a pallet twice, or scans the truck twice, or scans the truck and not the pallet.

I need help setting up logic inside the table that deals with errors. I'm sure someone has done this. If that is you or you have ideas how it is best done. Please weigh in. Regards

Chris

 

[theDBguy]

Hi Chris. Welcome to AWF!

Did you say the scan is being done directly to the table? If so, you should use a form instead.

 

[Cronk]

Is the load number unique? If so, create an index on both the load number and pallet number in your table and make that index unique. That will stop duplicate records with the same pallet/load number from being entered.

 

[The_Doc_Man]

Strongly concur with theDBguy. If you have a form wrapped around the input process, you have the ideal place to validate the input before you accept it. Direct input to a table is almost never a good idea from that kind of interface.

We don't know how much you know about Access, but the idea if you use a form is that you can trigger an update to your table through a form. BUT a form has events where you can intercept something before allowing the update. The event in question is called "Before_Update" and is something you can look up online.

https://docs.microsoft.com/en-us/office/vba/api/access.form.beforeupdate-event - the basic info

https://www.access-programmers.co.u...fields-in-a-form-beforeupdate-i-think.173800/ - a forum link to a longer discussion of the problem

https://www.pcreview.co.uk/threads/validation-and-dirty-in-beforeupdate-of-form.2625062/ - a link to a similar thread in a different forum

 

[Croenbaugh]

Hi Chris. Welcome to AWF!

Did you say the scan is being done directly to the table? If so, you should use a form instead.

Yep. i said table, but your totally right. a form is better

 

[Croenbaugh]

Is the load number unique? If so, create an index on both the load number and pallet number in your table and make that index unique. That will stop duplicate records with the same pallet/load number from being entered.

An individual truck can have many pallets. Well 18-20. Each truck number is unique, but this table is linked to the truck database which has info about buyer, destination... The pallet number is unique, and links to the inventory database that stores data about what is on the pallet, when it was made and by who.

 

[Croenbaugh]

Strongly concur with theDBguy. If you have a form wrapped around the input process, you have the ideal place to validate the input before you accept it. Direct input to a table is almost never a good idea from that kind of interface.

We don't know how much you know about Access, but the idea if you use a form is that you can trigger an update to your table through a form. BUT a form has events where you can intercept something before allowing the update. The event in question is called "Before_Update" and is something you can look up online.

https://docs.microsoft.com/en-us/office/vba/api/access.form.beforeupdate-event - the basic info

https://www.access-programmers.co.u...fields-in-a-form-beforeupdate-i-think.173800/ - a forum link to a longer discussion of the problem

https://www.pcreview.co.uk/threads/validation-and-dirty-in-beforeupdate-of-form.2625062/ - a link to a similar thread in a different forum

will check those out.

thanks much.

 

[theDBguy]

Yep. i said table, but your totally right. a form is better

Hi. That's good. As Doc said, you'll have better control of what users can do when you use forms.

 

[strive4peace]

hi Chris,

Welcome to Access World forums!

it is good when the scan codes also identify the item -- so if a truck was scanned when Access is expecting a pallet, or vice-verse, the user can be informed.

As others stressed, using forms are important. In this way, the form can be set up such that the truck scan is on top and pallet scans are in a list below. If a scan is done twice, code can check to see if it is a duplicate (for that load only, or whatever your rules are)

As scans are made, the system can also count and display how many pallets are on the load so at the end, the number can be compared.

I think it would be important for the form to look nice with big print, since it will be looked at all day ... maybe also pictures of trucks and pallets ~