Creating a formula on a table column

Hi.
Some background first...
The tables are in SQL server 2000 and the front end is an Access
project.
I have a table with multiple ID fields which is populated by several
forms and other tables. The Main ID has a autonumber function on it. 1
other is made from various components from the row in the table.
The column i am having trouble with, all i want to do is increment it
by 1 ( just to prevent any duplication of numbers ) but i want to do
this on the table,
I could do it on the form when data is inputted, but due to large
number of forms etc... if it could be done on the table it would be
easier.
There is a formula function but i can't get it return the Max column
value from a table. Is this type of thing possible.
I can retun values, but how do i define a query within this function?
Many thanks for any help / advice
john."john" <j.w.horlock@.leeds.ac.uk> wrote in message
news:8456800a.0310020656.c97d681@.posting.google.com...
> Hi.
> Some background first...
> The tables are in SQL server 2000 and the front end is an Access
> project.
> I have a table with multiple ID fields which is populated by several
> forms and other tables. The Main ID has a autonumber function on it. 1
> other is made from various components from the row in the table.
> The column i am having trouble with, all i want to do is increment it
> by 1 ( just to prevent any duplication of numbers ) but i want to do
> this on the table,
> I could do it on the form when data is inputted, but due to large
> number of forms etc... if it could be done on the table it would be
> easier.
> There is a formula function but i can't get it return the Max column
> value from a table. Is this type of thing possible.
> I can retun values, but how do i define a query within this function?
> Many thanks for any help / advice
> john.
On the SQL table define the column thus:
ALTER TABLE tblFlibble(
Col1 int Identity (1,1) NOT NUL,
col2,
col3, etc)
If you already have data in the table and want to preserve the existing
values, change the IDENTITY to
IDENTITY(*number higher than your highest existing number*,1)
To find the number you have just created when you ad a row, select the
@.@.IDENTITY variable
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.522 / Virus Database: 320 - Release Date: 29/09/2003