capture value of the autonumber field in Access

[sribu]

Hi,
I have a table consisting of 4 columns in a Access database.The primary key value is the autonumber field.The second column is a memo.I need to insert values into the third column in the format of:
Value of the autonumber field_a string.

eg:If the autonumber of the row I am currently inserting is 3 then the third column should have the value of 3_string.

For this I need to capture the value of the autonumber field for the row I am currently inserting .Can anyone tell me how I can do this in vb.net.

 

[Custom24]

Hi
That is an interesting one. With Oracle (and maybe SQL Server, I don't know) I generally use a stored procedure to do this kind of thing because you can use the returning clause
eg Insert into Table Fields values('value') returning Table.AutoNumber into MyVariable.

However, in Access, this is not possible, to the best of my knowledge.

One way around this might be

Generate a unique string to identify the record you are about to insert. The way I normally do this is to concatenate the computer name and the date.tooadate

Insert this value in your third column temporarily.

Perform an update statement

Update MyTable set ThirdColumn = Autonumber & "_" & AString where ThirdColumn = UniqueString

I hope this makes sense and is of some use to you. There might be a neater way.

Mark