Scanning a barcode into Access 2003

[MANGFN]

My client is a baseball league who started a Kids Club which has membership cards that have a barcode and their own specific number to monitor the number of times that the KidsClub members come to the games. They don’t have any software to go with the cards though. They asked me if I could help. I created a simple table in Access to capture the name, address, telephone, email, number of games attended. The cards have a barcode on them, and they would like to be able to scan the card with a USB handheld scanner and have the number of games attended field to be updated by the scan. Each card has its own number that is programmed into the barcode, which could be the primary key for Access. Can you offer any assistance in how to get the scan of the card to update the "number of games attended" field I created based upon the barcode number?

 

[Len Boorman]

If you are getting the Barcode into the Table as a number then you can just count the number of times a number occurs.

L

 

[MANGFN]

Len, Thanks for the prompt response. There are two things happening that I'm not certain how to fix. Here's what's happening. I open Access to the form which lists the fields. First, I have a field named "BarCodeID" which the barcode when scanned places the card number in the fhat field. I need it to bring up the data that corresponds to that card number. For example: Card number 00850 belongs to John Doe, 2525 Data Way, Greenville, SC 29601. So, when the card is scanned in, I would like the data that corresponds to the card number to come up. Right now it's over writing the card number that is currently in the field when Access is opened up.

Second issue. When the card is scanned and the appropriate data that corresponds to the card comes up, I would like the field called, "Number Of Games Attended" to increase by 1 each time the card is scanned.

Can you help?

 

[battenberg]

I recently developed a bar code printing database,
the hand held scanner i used could be configured to apply a carriage Return at the end of each read.

You could build an event onto that.

 

[Len Boorman]

Couple of points

1 When you are adding records i.e swiping a bar code you can bring up data but in this circumstance I would ask why do you need it at that time. The number corresponds to a certain individual who data you already have

2 Once you have added a "swipe" then you need to move on to next new record. Your add record form should be in continuous format

3 You do not need to keep a count record. This is a calculated value and it is not best practice to store calculated data. Calculate it when you want to know the score so to speak

So I would suggest that you think the process through carefully to decide what you want and when.

I am not being a snoot but it is a very good exercise and builds your display plans.

I am suggesting that when you are swiping cards you do not actually need to know who it is and how many visits

Len

 

[MANGFN]

Couple of points

1 When you are adding records i.e swiping a bar code you can bring up data but in this circumstance I would ask why do you need it at that time. The number corresponds to a certain individual who data you already have

2 Once you have added a "swipe" then you need to move on to next new record. Your add record form should be in continuous format

3 You do not need to keep a count record. This is a calculated value and it is not best practice to store calculated data. Calculate it when you want to know the score so to speak

So I would suggest that you think the process through carefully to decide what you want and when.

I am not being a snoot but it is a very good exercise and builds your display plans.

I am suggesting that when you are swiping cards you do not actually need to know who it is and how many visits

Len

Len,

You are correct in all that you have stated. The data with the individuals information is currently in an Excel spreadsheet which can be imported into Access. Therefore, it does not need to re-captured or viewed when the card is scanned. We do however, need to know how many times the card is scanned, and ensure that there are no "double scans" on the same date. I am by no means an avid Access programmer, but do have some experience. I am trying to help a client who made a mistake in ordering 9000 of these cards without a way of capturing the data they are looking for. If you could help me in getting this program together for them, I would greatly appreciate it.

 

[Len Boorman]

Okay
Point 1 Scanning in and preventing double scan on a particular day

Basically you willscan in the barcode and have this saved in a table say tbl_Attend, A second field in this table will be date with a default value of date(). These two fields used together as the primary key to that table. This will mean that a barcode may be scanned many times but on each scan the date must be different. You will get an error message message if this rule is violated and the duplicate record will not be saved.

Point 2
Another table will have all the people data. Primary key will be the barcode value.

Point 3
1 to many relationship between people table and tbl_Attend between the barcode values

So you can now determone who attended when

To do the count of how many attendances
query selecting barcode value from tbl_attend twice, click the Summation icon to convert to a summary query and on the second barcode value select Count. You can add into this query data from the people table so now you have the count of how many attendances a person has made.

Similarly you can get how many attended on a certain date. If you have the details of the event on particular dates you can also determine who attended a certain event

L