Access increase by 1
Posted on 2009-04-05
I have not found an exact answer in the exchange for my question, so I thought I would ask. I have an access table which uses BATCH_NUM as the primary key. We are now doing multiple tests on each batch in our lab. I will add a value called LINE_NUM to this table. I want LINE_NUM to start at 1 and then increment 1 for each additonal test on the batch. So, in my form, lets say batch 1111 has it's first test and by default value property of LINE_NUM, LINE_NUM on the form shows 1. Now, the next day I am going to run another test on batch 1111 and I want LINE_NUM to be a value of 2 for this test. Can I use an expression or VB on the form to do this? Something like select BATCH_NUM where BATCH_NUM = txtBATCH_NUM and then find the MAX of LINE_NUM and increment by 1? Autonumber would work, but it would just keep increasing the number for all records, not by batch number.