By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,761 Members | 950 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,761 IT Pros & Developers. It's quick & easy.

How I know what number was generated?

P: n/a
Hi, I'm working with Ms Access 97 and I have two tables like these:

Table: Person Table: Phone
Field: ID (Autonumeric) (primary key) Field: Person_ID (foering key)
Filed: Name Field: Phone_number

the point is that I must insert a record into Person using
DoCmd.RunSQL("INSERT INTO...;") and also I must do the same with
Phone, but How I know the key generated for Person before?
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies

P: n/a
You won't know using that syntax. Unfortunately, Access doesn't support the
@@IDENTITY function like many db systems. Instead, you'll have to use a
recordset. Sorry, but my DAO is rusty:

Dim rst As DAO.Recordset

Set rst = Currentdb.OpenDatabase("SELECT * FROM YourTAble WHERE 1=0")

rst.AddNew
rst("Field1") = Value1
rst("Field2") = Value2

YourId = rst("YourIDField")

rst.Update

Sorry if the .AddNew and .Update are out of place ... check online help for
full syntax explanations regarding recordsets ...

"Andrés Guerrero" <ao********@hotmail.com> wrote in message
news:82*************************@posting.google.co m...
Hi, I'm working with Ms Access 97 and I have two tables like these:

Table: Person Table: Phone
Field: ID (Autonumeric) (primary key) Field: Person_ID (foering key)
Filed: Name Field: Phone_number

the point is that I must insert a record into Person using
DoCmd.RunSQL("INSERT INTO...;") and also I must do the same with
Phone, but How I know the key generated for Person before?

Nov 12 '05 #2

P: n/a
DFS
"Andrés Guerrero" <ao********@hotmail.com> wrote in message
news:82*************************@posting.google.co m...
Hi, I'm working with Ms Access 97 and I have two tables like these:

Table: Person Table: Phone
Field: ID (Autonumeric) (primary key) Field: Person_ID (foering key)
Filed: Name Field: Phone_number

the point is that I must insert a record into Person using
DoCmd.RunSQL("INSERT INTO...;") and also I must do the same with
Phone, but How I know the key generated for Person before?


You may want to write a routine that does three things:

Public Sub addPerson()
add record to Person table
retrieve highest ID (the one just inserted)
use the ID to add child record to Phone
End Sub
If it's a multi-user system with fairly high transactions, you may want to
use a global indicator to "lock out" the routine until it's finished so no
mixup of IDs will occur:

Public Sub addPerson()
addingPerson = True
add record to Person table
retrieve highest ID (the one just inserted)
use the ID to add child record to Phone
addingPerson = False
End sub

Call the routine, which executes only if it's not in use by someone else:
If addingPerson = False then addPerson


Nov 12 '05 #3

P: n/a
Andrés Guerrero wrote:
Hi, I'm working with Ms Access 97 and I have two tables like these:

Table: Person Table: Phone
Field: ID (Autonumeric) (primary key) Field: Person_ID (foering key)
Filed: Name Field: Phone_number

the point is that I must insert a record into Person using
DoCmd.RunSQL("INSERT INTO...;") and also I must do the same with
Phone, but How I know the key generated for Person before?


I might use a recordset
Dim rstPerson As Recordset
rstPerson.AddNew
rstPerson.FirstName = "Andre:
rstPerson.Update
rstPerson.BookMark = rstPerson.LastModified 'at the last record
added

DoCmd.RunSQL("INSERT INTO.PhoneTable (PersonID, PhoneNumber) Values
(" & rstPerson!ID & ", '123-456-7890'")
Nov 12 '05 #4

P: n/a
RE/
the point is that I must insert a record into Person using
DoCmd.RunSQL("INSERT INTO...;") and also I must do the same with
Phone, but How I know the key generated for Person before?


You don't and you never will for sure.... The Max() thing probably works most
of the time if there aren't that many users banging on it, but it's not
technically tight.

I do one of two things, depending on the situation:

1) If it's a small record, I just do a .AddNew via DAO.

2) If it's a big record I might do a .AddNew for just an empty shell of a record
from which I capture the ID. Then I follow the .Update up with running a query
to fill in the rest of the info.

3) If there's going to be a lot of this going on, I change from AutoNumber to
just a Long for the PK,and create a table: zstblRecordNumberLatest. Then I go
to that table and increment a field in it to get my next ID number before adding
a record.

Option 3 has worked pretty well for a number of years. It adds some complexity
at first, but once you get a function fully debugged, all you have to code is
something like NewID = RecordNumberNextGet("TimesheetID").
--
PeteCresswell
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.