Unbound Form: Get PK of Saved Record

[Tim Marshall]

This is an embarrassing question to which I should know the answer, but
I am not 100% sure of myself. My applications, whether they are Jet or
Oracle usually deal with reporting on existing apps that others have
done and except for the odd single user app, I don't do many transaction
type applications....

Anyways,

I have an unbound form which is a data entry form for new records or in
"add record" mode. Say this form is for creating requisitions. The
user enters all the criteria in numerous unbound controls. User then
clicks a save button or tool bar item and an insert/append statement
takes place and the table in question, now has a new record. A PK is
created for the new record via an autonumber (or trigger/sequence in
Oracle).

Say the new record is a requisition and the form has a subform for line
items which is enabled with the save procedure. To add these records to
the subform, I'm going to need the PK of the record I've just created.
I would appreciate any comment or alternate approach that is better than
the following approach:

1) Create and run the insert/append SQL statement

2) Use a DAO snapshot recordset (this is A97) and use .movelast to go to
last record.

3) Take the new PK and store it in a form variable or control.

My area of worry is the .movelast method on a multi user database.
Probably very remote, but don't I run the risk of moving last to another
user's new record that was inserted at about the same time as my own?

I need some reassurance or pointing in another direction, thanks very
much in advance.

--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Want some?" - Ditto

[Wayne Gillespie]

On Wed, 10 Sep 2003 17:11:06 -0230, Tim Marshall <tm******@Gunner.Sabot.Spam.On.Loaded.FIRE> wrote:

This is an embarrassing question to which I should know the answer, but
I am not 100% sure of myself. My applications, whether they are Jet or
Oracle usually deal with reporting on existing apps that others have
done and except for the odd single user app, I don't do many transaction
type applications....

Anyways,

I have an unbound form which is a data entry form for new records or in
"add record" mode. Say this form is for creating requisitions. The
user enters all the criteria in numerous unbound controls. User then
clicks a save button or tool bar item and an insert/append statement
takes place and the table in question, now has a new record. A PK is
created for the new record via an autonumber (or trigger/sequence in
Oracle).

Say the new record is a requisition and the form has a subform for line
items which is enabled with the save procedure. To add these records to
the subform, I'm going to need the PK of the record I've just created.
I would appreciate any comment or alternate approach that is better than
the following approach:

1) Create and run the insert/append SQL statement

2) Use a DAO snapshot recordset (this is A97) and use .movelast to go to
last record.

3) Take the new PK and store it in a form variable or control.

My area of worry is the .movelast method on a multi user database.
Probably very remote, but don't I run the risk of moving last to another
user's new record that was inserted at about the same time as my own?

I need some reassurance or pointing in another direction, thanks very
much in advance.

Even though the PK is an Autonumber you can predetermine the next available number using
Nz(Dmax("MyID","tblMyTable"))+1
Store this number in a variable and use it directly in your Append SQL statement.
You will need error handling to increment the number if the append fails (another user has added a record since you obtained the next available
number).
Once the append has succeeded, you already have the PK stored in your variable so there is no need for further checking.

This will work with JET, but I don't know about Oracle.
Wayne Gillespie
Gosford NSW Australia

[TC]

Would it be appropriate to change the PK from autonumber to long, have a
seperate table holding the next available PK value, then get the value from
that table, so you can plug it into the INSERT statement?

TC

"Tim Marshall" <tm******@Gunner.Sabot.Spam.On.Loaded.FIRE> wrote in message
news:3F***************@Gunner.Sabot.Spam.On.Loaded .FIRE...

This is an embarrassing question to which I should know the answer, but
I am not 100% sure of myself. My applications, whether they are Jet or
Oracle usually deal with reporting on existing apps that others have
done and except for the odd single user app, I don't do many transaction
type applications....

Anyways,

I have an unbound form which is a data entry form for new records or in
"add record" mode. Say this form is for creating requisitions. The
user enters all the criteria in numerous unbound controls. User then
clicks a save button or tool bar item and an insert/append statement
takes place and the table in question, now has a new record. A PK is
created for the new record via an autonumber (or trigger/sequence in
Oracle).

Say the new record is a requisition and the form has a subform for line
items which is enabled with the save procedure. To add these records to
the subform, I'm going to need the PK of the record I've just created.
I would appreciate any comment or alternate approach that is better than
the following approach:

1) Create and run the insert/append SQL statement

2) Use a DAO snapshot recordset (this is A97) and use .movelast to go to
last record.

3) Take the new PK and store it in a form variable or control.

My area of worry is the .movelast method on a multi user database.
Probably very remote, but don't I run the risk of moving last to another
user's new record that was inserted at about the same time as my own?

I need some reassurance or pointing in another direction, thanks very
much in advance.

--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Want some?" - Ditto

[Tim Marshall]

TC wrote:

Would it be appropriate to change the PK from autonumber to long, have a
seperate table holding the next available PK value, then get the value from
that table, so you can plug it into the INSERT statement?

Bingo, that'll work.

Actually, since I'm doing it in Oracle, I'll have a sequence generator
(in place of the table you suggested) for the PK which is fired by the
save procedure to set up a new number that is then placed in the INSERT
statement as you suggested.

Thanks. This will also solve my problem with .addnew in another
thread. FWIW, in Oracle, to set up the equivalent of an autonumber, you
create a sequence. In some apps, there's one sequence that generates a
number for all PKs used by all tables, in others, you have separate
sequences for separate tables. To have the sequence act like an
Access/Jet autonumber, you then set up a trigger that fires and
populates your PK (or whatever) column/field before or after the insert
takes place.
--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Want some?" - Ditto

[Tim Marshall]

Thanks Wayne, I'm filing this for when I do get involved with a Jet app
that has similar transactions. TC provided with an idea as to how I can
do this in Oracle.
--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Want some?" - Ditto