help!!!

[Test_1]

hello!

I have a table with an autonumber field, I need to know
the value generated for the autonumber field everytime I
post a new record to the table. How can I do that? I
suposse this is a very basic question but I'm new to
access.
The database will be accessed by multiple users at the
same time and I cannot query the last record of the table
to find out the autonumber assigned for it's possible
that a new record has been entered by other user.
Please help me with this one!!

thanks in advance!

 

[Tim Ferguson]

I have a table with an autonumber field, I need to know
the value generated for the autonumber field everytime I
post a new record to the table. How can I do that? I
suposse this is a very basic question but I'm new to
access.

It's not a basic question, it's quite a sticky one. The short answere is
that the only safe way to get the next autonumber is using DAO:


' we only need the recordset, we don't want any records
Set rs = db.OpenRecordset( _
"SELECT MyNumber, MyRequiredField " & _
"FROM MyTable WHERE FALSE;", _
dbOpenDynaset)

' ready new record and get the autonumber
' any Required Fields have to go here (and in the SQL)
rs.AddNew
rs!RequiredField = "Some Required Value"
dwNewRecord = rs!MyNumber
rs.Update

' close everything up
rs.Close

' and return the value
FunctionResult = dwNewRecord



Hope that helps


Tim F

 

[Test_1]

Thanks for your help!

I tried it but didn't work for me. I understand the
theory involved in your solution, a brilliant one, but
perhaps I'm doing something wrong, the syntax perhaps...
Can you give me more explanation?
Thanks in advance!!!

 

[Tim Ferguson]

I tried it but didn't work for me. I understand the
theory involved in your solution, a brilliant one, but
perhaps I'm doing something wrong, the syntax perhaps...
Can you give me more explanation?

You don't say what exactly didn't work. I have just tested this, and it
works exactly as planned:

Public Function GetNewID() As Long

' make sure you have DAO set as default reference!!
Dim db As Database
Dim rs As Recordset
Dim strSQL As String

' temp holder for result: strictly speaking unneccessary
Dim dwNewID As Long

' set up DAO objects
Set db = CurrentDb()
strSQL = "SELECT PrimaryKey FROM TestTable WHERE FALSE;"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

' use the recordset to make the new record
' and get the new autonumber value
With rs
.AddNew
dwNewID = !PrimaryKey
.Update

End With

' return the number
GetNewID = dwNewID


End Function


I wonder if you have a DAO reference problem?

HTH


Tim F