Rollback on escape in form?

[silentwolf]

Hi guys,

I am just trying to find a way of undoing or rollback when I add a new record.

What I try to achive and what I got so far.

I have a frmCustomers, where I can enter a new Order via button.
The button opens frmOrders with a new Record.

All good and well.. however how can I rollback if I press the escape key?

When I decide to stop the Order then AutoNuber gets increased I guess it is not a problem but I think it would be nice to roll back the whole process.

Is there a simple way of getting around it or do I have to use the rollback function?

Cheers

Albert

 

[arnelgp]

sorry to tell you you can not "rollback" any autonumber used.
when you cancel addition of record it will advance to next number.

you can ofcourse create your own "autonumber" so it won't have a "gap"
when you cancel.

 

[silentwolf]

Hi arnelgp,

many thanks for your reply!
Ok no problem thought so.. (

Well I do have a Ordern Number besides the AutoNumber field.

What is the best way of doing that in your opinion?
Would you create a custom function for your own uniqu numbers for the Orders so it has something like Ord_0001 .. instead of 1 ?

I have in my table a field OrderName text field. and one with OrderNumber Number field and one AutoNumber

In my OrderNumber I have 101 for the first Order and it would increase by one for the next Order. With DMax function. Now I like to be able to have it displayed like Ord_101.

 

[arnelgp]

you can also remove OrderName field.
you can "get" that using Format() function or a Calculated field in a Query:

OrderName: "Ord_" & Format(OrderNumber, "0000")

 

[arnelgp]

did you used "pre-printed" orders before?
when the numbers are already printed?
the demo simulate the same, we only need to
check for the lowest number (in table tblTickets) and that it is not used.
open form1 and see the code on the form's BeforeUpdate event.

 

[silentwolf]

Hi thanks for your sample!!

I look into it!

I did not use any of that in a real environment yet so still looking for the best way to do so.

The sample brings me a huge step closer tough!

Cheers

 

[MajP]

I would recommend using an autonmumber for you Primary Key. Its a meaningless number but makes creating relationships easy. There are lots of safeguards you have to put in place if you roll your own PK. Then you can also have a uniquely indexed visible OrderNumber if it needs meaning (conitnguos, ordered). You can do it either way a prefer to separate the two.

 

[silentwolf]

Hi MajP,

I do have a AutoNumber for the Primary Key. And I used those keys to join the tables.

And pretty sure arnelgp' sample works fine and still having a AutoNumber for the Primary Key.

Just need to set it up in my environment..

 

[isladogs]

Sorry but I don't see the point of the example app in post #5.
Each new record grabs the next unused order number (after a delay whilst the supporting table is checked).
However, if a record is deleted that order number isn't reallocated for another record.
Fair enough but that means it just replicates the functionality of auto numbers.
It adds no additional functionality and is noticeably slower to use

If you really must calculate a new sequential order number, then using DMax("FieldName", "TableName") +1 will be faster and obviate the need for the lookup table with 500K records

 

[arnelgp]

Sorry but I don't see the point of the example app in post #5.

i open a ticket table and now it is fast.
the main purpose of this is for multi-user support and eliminating the "gap"
when Cancelling New record.

as i have said there are many advantages of using "pre-printed" numbers.
if it is a business rule to "purge" old orders (dormant) to history file and zero
out the Orders table, you probably will have a problem with autonumber.
you might get same number (as the one you archived to history table).
so keeping "ticket" table guarantee your continuity of series.

 

[silentwolf]

Hi sorry for the late responds,

was working on other things and had not much time.
I think that sample looks pretty good thanks arnelgp!

Will look into it better a bit later and get back to you!

Cheers

 

[isladogs]

@arnelgp
I've tried your updated version & agree its now very fast.
It contains some clever code and impressive coding skills, but my objections are as follows:

1. it requires a lookup table with a lot of records
2. eventually at some time in the possibly distant future, that table will be all 'used' and the code will fail to produce a new unique value
3. all that code is totally unnecessary when there is a simple alternative that is equally effective

As previously mentioned, you can achieve EXACTLY the same result with no lookup table or module code.

It requires just one line of code in the Dirty event of the Date field:

Private Sub Date_Dirty(Cancel As Integer)
    Me.OrderNumber = DMax("OrderNumber", "Orderheader") + 1
End Sub

Also, there won't be a bug waiting to trip up the clients in the distant future
Finally, the file size of my version is just 456 kB. Yours is 12880 kB

 

[silentwolf]

Hi isladogs,

Thanks for your sample!!
It looks great !


Cheers )

 

[isladogs]

I prefer the KISS approach (Keep it Simple Stupid)

 

[silentwolf]

Lol ok I will get back to you with that one
There might be still questions but for now I am trying to get other things out of the way sorry for that.

Cheers )
P.S. did you see my other thread?

 

[CJ_London]

I would have thought a bigger issue is the situation where a user decides to cancel an order having entered one or more line details (assuming an order consists of an order header table and an order line table).

At that point the header record has been created and now needs to be deleted.which if someone else has entered another order, leaves a gap in the orders

not looked at the examples provided so that may have been addressed.

 

[arnelgp]

I would have thought a bigger issue is the situation where a user decides to cancel an order having entered one or more line details (assuming an order consists of an order header table and an order line table).

with my approach that will be easy to roll.
you only reset the Used field to 0 (false) when the header is deleted, in the case where
there is no detail records (on Unload event).
i am using Index, and when it is time to get the New Order number,
it will "seek" the first record where Used = 0.

 

[isladogs]

Lol ok I will get back to you with that one
There might be still questions but for now I am trying to get other things out of the way sorry for that.

Cheers )
P.S. did you see my other thread?

Not sure whether you were addressing me?
If you were, I don't know which other thread you mean

 

[isladogs]

I would have thought a bigger issue is the situation where a user decides to cancel an order having entered one or more line details (assuming an order consists of an order header table and an order line table).

At that point the header record has been created and now needs to be deleted.which if someone else has entered another order, leaves a gap in the orders

not looked at the examples provided so that may have been addressed.

Using the DMax+1 approach, if the latest record is deleted/cancelled, that order number will be reused
If earlier records are deleted/archived at a later date, those order numbers won't be reused.

 

[arnelgp]

going back to my 500,000 "tickets".
if the OP is creating 100 Orders Per Day, in 1 Year time (x 365 days),
he need 36,500 ticket numbers in 1 Year.
Now divide Half million (500,000) by this number to get how many
years this ticket will last:
?500000 / 36500 = 13.7 Years.
with that years this db might already been migrated to another system.

100 orders per day! that is a busy shop, when ideally you only create 4 to 6 per day?