Field ID

[chenko]

Im adding a new record to a database using ASP.

The problem is i want to create a new record which i can do fine, and get the ID fields value that is set to autonumber, whats the easiest way to do this?

[Sastraxi]

Read the last record's autonumber, increment it, and put it back in. Though it may yell at you since it's an automatic field.

[chenko]

What if there is another record put in before I can read that field?

[Sastraxi]

Then you could get the numberofrecords from the recordset object and increment that to put into your autonumber field.

[chenko]

That wouldnt work if there are going to be records deleted will it? I could always turn autonumber off.

[Psyrus]

chenko,

Couldn't you just grab the FieldID right after you insert the new record by moving to the last record?
Maybe by combining these procedures into the same sub or function?


Chris

[chenko]

They are in the same sub/function. Right after the .Update I want to get it....

If I add a record, will I have to reopen the recordset to get the new entry?

[Psyrus]

If you already have the recordset open I don't see why you would, maybe I'm wrong. Try moving to the last record after the insert, grab the ID and see if they match.


Chris

[monte96]

Are you using Insert statements or are you using the AddNew method of the recordset object?

If your using the later, ADO moves the cursor to the newly added record so just read in the value. If your using Insert statements, then what your describing is an issue.

If you need to add new records AND you need to load them immediately for use, you should use the AddNew method and make sure your recordset is adOpenKeySet or adOpenDynamic and your lock type is not adReadOnly.

[chenko]

I am using the AddNew statment, and im using a Update query after, I have it open for adOpenDynamic


Cheers

[chenko]

Ok I got back around trying that and it works

Cheers!!