In my database, my records can have up to two barcodes to identify the same record, so Im thinking I need two separate fields to record the barcodes in..
No. Use a separate Barcodes table and relate records to the "Item" table. The barcodes than then be validated as unique. You an also have more than two barcodes.
A couple of approaches I can think of:
1. Set up a relationship between each barcode field and the whole list of barcodes from the other field—not sure that's possible directly, or either you would need a field to parse each barcode to text first—then you could use a boolean field to tell you whether there is a match to any in the other field.
2. Store all barcodes in a separate table—where you can make sure each exists only once, and then reference them in the main table of whatever you database is—where you can make sure each barcode is referenced only once.
Thanks "Vaughan" and "Keywords", for waking me up. Yes of-course a related seperate table is the logical answer. My case is a typical example of working in a existing working database and then later needing to add additional functionality to it and I was trying to fix it with a bandaid.
I was hoping someone would direct me to a function that could be used in Validation by Calculation to check the other field for uniquenss, but can now see many advantages within my Database to run with a New related table.