copy a tables Barcode calculated field into another field

[ravencsr]

Sorry if this seems so simple, but its doing my head in

I have a calculated field in a PRODUCT table that auto increments a 12 digit barcode number by 1 from a certain start number (hence the reason its not the autonumber pk).


I have VBA code that formats that 12 digit barcode into a printable bar code with the extra checksum digit added on to it.
However, I need to export all this product data into a POS system and I cant export the 13 digit barcode image, I need to export the actual 13 digit value. The VBA code doesnt actually store the 13 digit value anywhere.

I have another table called BARCODE where I have all the 12 digit barcodes (~7000, more than I'll need) in one field and then the corresponding 13 digit barcodes in another field.

However Access wont let me link my BARCODE to PRODUCT tables because the PRODUCT.PROD_BARCODE field is a calculated field.

So what I was wanting to do was when the PRODUCT table's PROD_BARCODE value got incremented, that value would be placed in another field TEMP_BARCODE in the same table. That way I could join both tables and have access to the 13 digit barcode that relates to that product for exporting purposes. All this being done from a form of course.

I did try an update table query that seemed to work, however I would rather not use that method as it always updated the previous records that were already updated (if that makes sense????)

Any help greatly appreciated.

[Micron]

IMHO, you should not have the calculation done in the table, and if you weren't doing that, you'd be OK. This might be one of those few cases where it's OK to store a calculated value (which is not the same as having the table perform the calculation) since you need to append the checksum value and the result won't be used in any sort of invoicing or similar function. The calculation should be done by a query, and in your case, the result stored or the first part and the checksum digit could be in separate fields.

That being said, if you have code that builds the value, why not modify that to store it where it's needed? Or you need a way to link the code to the product; perhaps by having the product id in the code table, and joining on product id. Not sure if that starts to violate any sort of normalization rules without knowing more about the tables and relationships.

[ravencsr]

I have just tried taking the calc out of the table, and have put it in a text box on the form, but how do you then put that value from the text box back into a field in the table? I tried putting the table field name PROD_BARCODE as the control source in the text box but it comes up blank, take it back out and the barcode number is there.

Also the code I used for all the checksum/barcode generation stuff was found on the web, i just copied the module over and it works. Unfortunately I have not had any experience with vba yet.

[Micron]

how do you then put that value from the text box back into a field in the table?

Either run a stored query via a macro or code, and reference the form textbox holding the complete bar code value. I can't tell for sure if now it's an append or update situation, but I think it's an update because of

I did try an update table query that seemed to work, however I would rather not use that method as it always updated the previous records that were already updated

which means you didn't specify any (or the correct) criteria for the update. You need to zero in on the record that needs the update; perhaps by referencing a form control that holds the unique record ID or some other unique value. If you have no such ID or value, you'll update every record in the table that contains the referenced value, or every record of the table in the updating field. Should that be the case but you don't want that, consider adding an autonumber field to the table you're updating and bring that value on to the form (in a hidden control). You can then restrict the update to the one record based on the autonumber ID

You definitely want to be playing around with copies of your tables.
you might want to consider uploading a zipped copy of your db if you get stuck.