Barcode system

[jeffnos]

Hi all
I hope someone can steer me in the right direction, I have been trying to work this problem out for ages.
I want to have barcode labels on equipment that is situated at various locations.
I have a barcode scanner that can read these labels it reads the barcode fields,Time and date.
My database is Access 2003
My main customer form has the barcode number field.
I have a sub form linked to the main customer form which has the Date Field,Time Field.
I know how to import the scanned details into the database as a table.
I want the barcode number if it is the same from the scanner as to the one in the main form that it will import the time & date into the sub form.
Do I need something like append query?
I hope this all makes sense.
All the best to everybody for Xmas.
Jeff

 

[ncrcn]

The barcode scanner we use works like a really fast keyboard, so what it does is it scans in a serial number and a carriage return. You have to code the after_update event of the target text box to do something else (like add the date and time [now()] to another text box...)

 

[ncrcn]

Does your barcode reader output these three peices of data in rapid succession? Do you need three sequential text fields to receive these pieces of data? Are all three pieces of data maybe output in one piece of text you need to break down into smaller tokens? Do the second two need to be unbound on the main form that update children?

 

[jeffnos]

Thanks for your help. I am using a portable memory scanner outdoors.
When I return to the office I download the barcode details which are also time and date stamped into the supplied software. I than convert this into an Excel spreadsheet and import this into Access 2003 as a new table.
The rest is what I am having problems with;
If the imported barcode details match the barcode details in the form, then I want this to up date the time and date fields in the linked subform.
Regards
Jeff

 

[ncrcn]

If you expect to be doing this a lot ,this sounds like a visual basic import/export compare-to-a-table problem to me.

One way to do this is to always upload the data to the same file, have the file linked in acces.s (close the file before you open the linked table in access) Everytime you open access access checks the table for updates and reads them into a table. This may not be for beginners and is complicated -- so after writing this up, I'll try something more basic.

1) When you bring in the scanner at intervals, there is a process to download it to a file.
a- If you do this a lot and it involves a lot of clicking around and get tired of it eventually you may want to automate that, I used AUTO-IT (on a PC) to record and replay that process. Auto It let a dummy like me record a process that involved a lot of clicking around, there's an inspector that shows the "handle" for the things you're clicking so you can programmatically describe and repeat the process.

Your scanner downloads to a CSV file, and then you have to use that as a linked table? Is this what you are already doing?

I think the history of all serial number movement be one table, and the list of all your items by serial number would be another table, probably with the current status of these items (instock or out of stock or what shelf they're on) would be another table.

I've also done it in one table when we had some very rowdy serial numbers and we had to tally up inconsistencies at the end. (all the serial numbers as they were recorded in a flawed pre-barcoded way, and use a query to total them and where they ought to be, then manually troll for duplicates and errors)

Getting your data into Access sounds like a Visual Basic problem. What I think I would do is on opening your table, compare your table of serial numbers and dates and times and read it into your existing table of movements. Per each movement, if the movement affects the serial number record, update the table.

When I open a switchboard, it launches a program that looks at a linked table. It opens the table as a recordset, you also open your target access table (or tables) as a recordset. Once you are able to do that, you create a loop where you "movenext" through the records from your scanner and compare each row to your target tables. You act accordingly by assigning values to your target tables as you move through the source data until you reach the EOF.

It should poll for duplicates from the upload table and not add duplicates and only add the rows you need. This is a problem I have with digital cameras -- the photos get uploaded but not always deleted on the camera. Are your serial numbers always deleted from the barcode reader when you upload them?

Using a form or a subform is a way to look at a table, I think you just want to bring your data into a table and act on it row-by-row with Visual Basic.

 

[ncrcn]

Short answer for smaller projects:
If the barcode scanner is uploading to a .csv files, you can link directly to those files in Access. Under tables, click link tables, find your source file, and choose it.

You might:
-keep a library of links to these CSV files and build a merge query to bundle them all together for the life of your project

Base your subform on a query, and you use that form to update a table that exists within Access with the data from your scans
-the query is the source for your subform, but you can edit either the query so it targets a new linked file, or you can edit the linked file so it targets another source on your drive

I haven't tried helping anyone on these forums before -- but I didn't see a response and this is how I solved similar problems. If someone sees I'm in the wrong here I'd also be happy to see other responses to this problem, or for a correction to the way I would solve a problem like this.
-keep changing the target of

 

[jeffnos]

NCRN
Thanks very much for your help which has been of great assistance.
I now have the issue solved.
I downloaded the scanner to a csv file and converted this to an Excel spreadsheet.
I linked this spreadsheet to a table in Access 2003.
From the table I created a query the query is than linked as a subform.
I linked a barcode number in my main form to the barcode number in my subform.
This appears to be working find.
Again thank you for your help.
Regards