Return Autonumber

[Question Boy]

Good morning,

I need to try and automatically process e-mails. I already have the
function to parse the content so I can work with it. Now however I need to
create the code to actually append each e-mail into my db. Problem being the
data need to go into diferrent table but are all related by the first
AutoNumber.

Typically I use something like:
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("TableName")

With rs
.AddNew
![Field1] = Variable1
![Field2] = Variable2
...
.Update
End With
rs.Close

Set rs = Nothing
Set db = Nothing


How can I get the autonumber (PK) which is created when the recordset is
created so I can then pass it to the other table updates?

Thank you,

QB

 

[Stuart McCall]

Good morning,

I need to try and automatically process e-mails. I already have the
function to parse the content so I can work with it. Now however I need
to
create the code to actually append each e-mail into my db. Problem being
the
data need to go into diferrent table but are all related by the first
AutoNumber.

Typically I use something like:
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("TableName")

With rs
.AddNew
![Field1] = Variable1
![Field2] = Variable2
...
.Update
End With
rs.Close

Set rs = Nothing
Set db = Nothing


How can I get the autonumber (PK) which is created when the recordset is
created so I can then pass it to the other table updates?

Thank you,

QB

The autonumber is created when the .AddNew command is executed, so you can
grab the value anywhere between there and .Update:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim pk As Long

Set db = CurrentDb()
Set rs = db.OpenRecordset("TableName")

With rs
.AddNew

pk = !AutoNumberField

![Field1] = Variable1
![Field2] = Variable2
...
.Update
End With
rs.Close

Set rs = Nothing
Set db = Nothing

 

[Stuart McCall]

Good morning,

I need to try and automatically process e-mails. I already have the
function to parse the content so I can work with it. Now however I need
to
create the code to actually append each e-mail into my db. Problem being
the
data need to go into diferrent table but are all related by the first
AutoNumber.

Typically I use something like:
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("TableName")

With rs
.AddNew
![Field1] = Variable1
![Field2] = Variable2
...
.Update
End With
rs.Close

Set rs = Nothing
Set db = Nothing


How can I get the autonumber (PK) which is created when the recordset is
created so I can then pass it to the other table updates?

Thank you,

QB

The autonumber is created when the .AddNew command is executed, so you can
grab the value anywhere between there and .Update:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim pk As Long

Set db = CurrentDb()
Set rs = db.OpenRecordset("TableName")

With rs
.AddNew

pk = !AutoNumberField

![Field1] = Variable1
![Field2] = Variable2
...
.Update
End With
rs.Close

Set rs = Nothing
Set db = Nothing

 

[Question Boy]

So simple! Thank you so very much. Please don't take this out of context,
but as the expression goes: "I could kiss you right now!". I've been racking
my brain on this for a little bit wihtout any success. These Forums are
great...Thanks to people like you!!!!

QB

Good morning,

I need to try and automatically process e-mails. I already have the
function to parse the content so I can work with it. Now however I need
to
create the code to actually append each e-mail into my db. Problem being
the
data need to go into diferrent table but are all related by the first
AutoNumber.

Typically I use something like:
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("TableName")

With rs
.AddNew
![Field1] = Variable1
![Field2] = Variable2
...
.Update
End With
rs.Close

Set rs = Nothing
Set db = Nothing


How can I get the autonumber (PK) which is created when the recordset is
created so I can then pass it to the other table updates?

Thank you,

QB

The autonumber is created when the .AddNew command is executed, so you can
grab the value anywhere between there and .Update:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim pk As Long

Set db = CurrentDb()
Set rs = db.OpenRecordset("TableName")

With rs
.AddNew

pk = !AutoNumberField

![Field1] = Variable1
![Field2] = Variable2
...
.Update
End With
rs.Close

Set rs = Nothing
Set db = Nothing