Get next autonumber in table

[timotai]

Hi all

I want to be able to figure out the next autonumber in a table using vb. at first i had the stupid idea of taking the last number and adding 1. But this was without the forthought of what if a record has been deleted. when it takes the last number and adds 1 its does not get the true next autonumber.

Is there a way I can get this though VB?

all help and advise is greatly apreciated.

Many Thanks

Tim

 

[cheerio]

If you are trying to do this there is probably something wrong with your design/logic.

For example, what happens if between your check and any attempted action another user adds a record to the table?

In practice I do not think it can be done.

 

[spiralmind]

if you are just trying to get a count of records in the table you can do that by looking up the tables properties in vba. this would look something like:

lngRecords = tables("tblSomeTable&quot.recordcount

you might have to use recordsets for this, though. it's been a long time since i have looked for a count of records myself, so i don't remember which path i took to get there.

if you are trying to guess at the next record's actual autonumber than you would want to look at the highest autonumber in the table, not neccessarily that in the last record, just the one with the greatest value

this does, however - as our favorite breakfast cerial has already said - present some problems if another record might be added between the check and the record you are running the check for you will have some unpredictable and generally inaccurate results, and should be looking for a better way to go about whatever you are trying to do.

- may seeds of dreams fall from my hands
and by yours be pressed into the ground

 

[timotai]

My problem is I am adding a record to a table with an autonumber using SQL. simple enough. The table is for overtimes which have been assigned to an employee. within the same peice of coding another SQL script runs to add an entry into a virtual diary table so when the employeelooks into their virtual diary they can see that they are doing overtime.

If for some reason the employee cannot do it the overtime entry can be selected by a supervisor and moved to another employee. however this also mean I have to change the diary entry. But there is no way to locate the diary entry. So the problem solution I thought was to add the autonumber asigned in the overtime table to a field on the record for the virtual diary entry and this way I would have some way of locating the relevant diary entry and can then also change that over to the other employee.

Maybe there is any easier way around all this that my excuse for a brain cannot think of.

Any help or advise is greatly appreciated.

Thanks both so far!

Tim

 

[KenReay]

Hi

Two ideas to work on,

maybe @@Id would help you, this is teh unique Id of last intserted record on a given connection, it is available in ADO, but not DAO I believe

or

.LastModified may help, if you are inserting via DAO . Addnew so

With rs
.AddNew
..etc
.Update
.Bookmark = .LastModified
TheJustInsertedREcordsAutoNumber = Rs!Id ' aasume Autonumber field is called Id
End With

Hope this helps

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[MichaelRed]

cheerio's comment is on target. faq700-184 has some code to generate key values which you can manipulate. It is not specific to your purpose, but could be modified to suit.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places