getting auto number before form is saved

[James]

What is the correct way to handle this situation. i have a Order form and a
products sub form. i want to add products to my order but i need the orderID
generated first. is there a way to have the ability for the user to roll
back the whole order with out generating an order number manualy and using
the autonumber field? should i be looking into transactions? this has
allways confused me. i would rather not force a order form save before they
are alowed to insert products. but i also don't want to have to manualy
create my own procedure to generate a orderID.

 

[Rick Brandt]

What is the correct way to handle this situation. i have a Order form and a
products sub form. i want to add products to my order but i need the orderID
generated first. is there a way to have the ability for the user to roll back
the whole order with out generating an order number manualy and using the
autonumber field? should i be looking into transactions? this has allways
confused me. i would rather not force a order form save before they are alowed
to insert products. but i also don't want to have to manualy create my own
procedure to generate a orderID.

You can't do what you want with an AutoNumber but it is generally not
recommended to use an AutoNumber as a meaningful piece of data that is exposed
to your users anyway. You could use an AutoNumber as your main Primary Key and
generate a separate Order Number in code. Delaying assignment until after parts
have been entered and/or reusing them is much easier with a number you create
yourself.

 

[Anne]

Hi Rick,
How can you delay the assignment of the autonumber field?
AP

 

[Rick Brandt]

Hi Rick,
How can you delay the assignment of the autonumber field?
AP

You can't. I was saying that if you calculate the order number yourself
(instead of using autonumber) then you can.

 

[Anne]

Good, thought perhaps I was missing something.
I knew you could do it with a beforeUpdate event and a message box, but that
is not always desirable.
AP

 

[James]

so the custom id number would be like dMax(orderid) +1? and i would generate
that if the order form is a new record? and that would be the foreign key to
my productdetails table?

 

[James]

acctualy no dmax +1 wont' work because what if another user desides to
create a record at the same time?

 

[Rick Brandt]

acctualy no dmax +1 wont' work because what if another user desides to
create a record at the same time?

That depends on when you do it. If you assign the number in the form's
BeforeUpdate event the next value is retrieved, assigned to the record, and
then the record saved all in a fraction of a second. If you use the default
value or any other event then yes, DMax() + 1 has concurrency problems.

 

[James]

but if the number gets created when they go to save the record how would sub
forms get their forign key unless the parant form has allready be saved.
hence brings us back to my orignal post.

 

[Rick Brandt]

but if the number gets created when they go to save the record how
would sub forms get their forign key unless the parant form has
allready be saved. hence brings us back to my orignal post.

You make the assignment in the BeforeUpdate of the parent. This happens
BEFORE you make any child records. If you need to cancel the parent record
because no children records were created you would have to do so by deleting
the parent record. You could then re-use that parent ID number if you so
choose.

Re-using numbers from deleted records would mean a further modification to
your applcation beyond the DMax() + 1 strategy. I have one database where
ID values to be re-used are copied into a special table. My BeforeUpdate
event first looks to see if this "NumbersToRecycle" table contains any rows.
If it does then I grab the lowest value from that table and then delete it.
If the recycle table is empty then I use the DMax() + 1 value.

 

[James]

so i have to save the parent record before creating the child records? what
about transactions, have you worked with those? could transactions be used
in this situation?

 

[Rick Brandt]

so i have to save the parent record before creating the child
records? what about transactions, have you worked with those? could
transactions be used in this situation?

Won't work in bound forms as far as I know. Unless you bound your forms to
"work tables" and only copied the data to the final tables upon completion.
As you can imagine that strategy is not without its problems as well.