alternatives to having autonumber as Key ID

[pradh]

Hi All,

Is it possible to have a new ID created for each record whenever a form connected to that table is opened?
Autonumber option is irreversible, the main reason behind my question

Thank you.

Pradh

 

[Trendsetter]

In the form Open event you could could run a query that gets the highest record number and then simply add 1 to that for you new record number.
Then run code to set a new record into the table with this new number....

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[EBGreen]

There are many things to consider when autonumbering in Access they have been addressed several times in these fora. Search the Access and VBA fora for previous threads. As a matter of fact I think I've seen an FAQ on this.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[MorningSun]

I have the query that finds the largest record but how do I run that query on the form open event and have the result populate a field???

I'm trying to populate an Audit Number on a form. The query is called qrySAAutoNumber (even though it's not an auto number). I want the Audit Number on the form to equal qry result +1...

Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[PHV]

Another way, safe only in mono-user environment, is to play with the DMax function in the BeforeInsert event procedure of the form.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Trendsetter]

You can use the On_Open event to set the following code:

Dim rst as Recordset
Dim intHighest, NextNum as Integer
Set rst = CurrentDb.OpenRecordset("SELECT * FROM qrySAAutoNumber")
intHighest = rst!RecordID 'this assumes the output from the query is called RecordID
NextNum = intHighest + 1
rst.Close
Set rst = Nothing

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk