How I know what number was generated?

[Andrés Guerrero]

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?

[Scott McDaniel]

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?

[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

[Salad]

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'")

[(Pete Cresswell)]

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