Increments in Tables (1 Viewer)

[k209310]

Is it at all possible to increment a number in a table depending upon the number in the row previous.

Eg
Field1
C1
C2
C3
C4

I know it is possible as the auto number field set by access when no primary key is defined carries a similar feature out. I have looked at the field properties for the autonumber and an option of increment is available in a new value section however for any fields that i create I can not get this option to appear.

I have tried to use the default value to try and increment the cell previous but im not too sure to do this either.I can dio basic sums bu im not too sure how to increment a number or how to reference other cells. Im not too sure if this is possible tho.

Has anybody got any idea how to do this. Is there a way of doing this via VBE? I am familiar with loops but am not too sure how to manipulate tables in access using code.



Thanks for your time

Chris

 

[varunmathur]

increment fd

hi
try using two field one with just the preffix and the other with the regular increment field then use then together to get C1,C2 etc by concatenation.
varun

 

[The_Doc_Man]

It depends on what you wanted to do and whether there is a way to specify the number that you want to use as the basis for your problem. For instance, suppose you wanted to program the nearest possible equivalent of an autonumber field.

You would have to do the following somewhere. Assume the field is called MyAuto and it is of type LONG.

DMax("[MyAuto]", "MyTable") + 1

This would work as long as the database isn't shared. But if it IS shared, then you run into the problem of what would happen if you and your work partner executed the same function at the same time from two different machines. You would both get the same answer, both would choose the same "next number", and whoever got there second would either get an Insert error (non-unique key) or would overwrite the other person's record. Neither would be good.

It is the latter problem (destructive interference) that makes Access less than a perfect solution for some classes of problem. If you cannot just use an autonumber, then you might have some difficulty in expressing this function explicitly.

You could do this better in code by taking out a recordset on the table and locking it pessimistically, but all that would do is add further complexity to the code you had to write. This is because Access is not the best distributed tool in the world for managing multiple users in a single database across a network. You have a LOT of programming to do to make the application "share aware."