next value

[djam]

i have a linked table, how would I get the next Id number to use before the insert? Is there a function I can use?

 

[gusbrunston]

[tt]
Hi:

There are two ways I know of:

1) Use the data type autonumber for your "ID" field. It has many advantages for large, multi-user databases.

2) Another way is to use the DMax function. This is advantageous in that the number is not carved in stone. But there are inherent dangers when this is used in large, multi-user databases. Use this method at your own risk, which is, another user may slip in an identical Primary Key (ID?) and you'd be in the soup (to paraphrase my friend Michael).

Select your "ID" control.
Select Properties, then Data, then Default Value.
Enter:

=DMax("[MyIDfieldName]","[tblMyTable]&quot+1

This will increment the default value of the highest ID number in your table by 1.

I hope this is helpful. Gus Brunston An old PICKer, using Access2000
Intermediate skills. Back up before taking my suggestions!

 

[RickSpr]

Do you actually need to know the ID number before you begin to insert the record? Gus' first method won't do that--but it lets you get the number before you finish inserting it, using code. If you insert the record using a Recordset object, the autonumber will be filled in as soon as you call the AddNew method. Rick Sprague
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[TorF]

In case there are no records in table, i suggest you to use:

=Nz(DMax("[MyIDfieldName]","[tblMyTable]&quot,-1)+1