Hello, this is my first post. Ive been reading the web for a few days and trying to design an access form around a table I created. The form is pretty much complete, except for one detail. Im hoping someone here can help me out, as I just cant seem to find much info on this.
I have a number field called "ID" in my table that is not a primary key and is not an autonumber, although it does not allow duplicates. Every record entered into my form is given a new number in a consecutive order 1,2,3,4,etc. I would like it to work like excel where I can delete or add a row anywhere in the form (i.e. adding a record after row 200, even if there are already 500 total rows) and the row number will always stay sequential, never skipping a digit.
Right now I have this statement running after update:
Private Sub Form_BeforeInsert(Cancel As Integer)
ID = Nz(DMax("ID", "Schedule Status")) + 1
End Sub
Its very simple and allows me to manipulate numbers as required and gets around autonumber skipping numbers utilized by records that have been deleted, but if I have 500 rows, that is a lot of typing to renumber if I delete or add a record that is not the last row.
I am new to databases, so my programming skill is close to null, but I will learn. This is just driving me nuts.
This form is given to coworkers and customers and they just cant seem to get over a skipped number, which is why autnumber does not work. When I used autonumber and deleted records, I would have larges gaps in the numbering scheme and would constantly get emails and phone calls about missing entries (because autnonumber does not reuse previous numbers). Its even worse if I add a new record at row 200, but the ID order went 200,522,201.