Question on a autonumber function on cmdUndo

[d9pierce1]

Hi all,
When I add a new record and click undo, lets say it was a mistake,,,,
The undo function does not undo the record, see code below.
I am using a code to create a unique ID and it creates a record automatically and that doesnt Undo....



So, what would be the best practice in this to actually remove the record if I add new by mistake and hit Undo?
Would t be better to add a something to the cmdUndo to delete record and then go to Previous or
would it be better to have this code in an unbound field and use an Insert or Update sql on save or Close?

Private Sub cmdUndoAccount_Click()


    If (Form.NewRecord) Then
        Me.Undo
        DoCmd.GoToRecord , , acPrevious
    End If
    
    If (Not Form.NewRecord) Then
        Me.Undo
    End If
Call Form_Current
Me.cmdUnDoAccount.Enabled = False
Me.cmdSaveAccount.Enabled = True
End Sub
..................................


..................................


'This will return the last autonumber inserted into the table. It will be unique.
'You can format it into something else for display purposes.


Public Function fnHeaderID() As Long
    
    Dim sSql As String
    Dim db As Database
    
    sSql = "INSERT INTO tblUniqueIDs (UniqueDate) Values (Now())"
    Set db = CurrentDb
    
    db.Execute sSql, dbSeeChanges
    fnHeaderID = db.OpenRecordset("SELECT @@IDENTITY")(0)
    Set db = Nothing


End Function

Hope this makes sense as if I hit undo, i dont want a record created with just a UniqueID in it...
Thanks,
Dave

[CJ_London]

use the form beforeupdate/before insert event to cancel the update/insert. All undo does is undo your entries, it does not cancel the update/insert

an autonumber is allocated as soon as you start to complete a new record. Once allocated it cannot be unallocated - so if you undo/cancel then when you add the next record you will have a gap. Autonumbers serve no purpose other than uniquely identifying a record, you should not be assigning any meaning to it at all.

[Micron]

Looks to me like your problem is that you're breaking a cardinal rule - never use autonumber as meaningful data. Aside from that, you cannot control the generation and subsequent loss of a particular autonumber when you cancel a record. You can also demonstrate this by beginning a record in a table and then press esc. Then begin another record and you'll see that the number has incremented by one. It's the way it is.

I probably gave you a bunch of links in a prior response and this would have been one of them. If not, best that you read
- http://access.mvps.org/access/general/gen0025.htm

EDIT- actually, that's not the link I had in mind. There was a much better one that sadly, seems to have been lost when the site updated and they made complete mess of things.

[d9pierce1]

Hi all,
I am trying to wrap my head around all of this as I have not used Before Update and Insert a lot....

So, If I add something like this code to my BeforeInsert event, and I hit Undo button, will this code fire? Do i have to call on it?
Also, does this code fire all the time when you create a new record? Example, when i create a new record, fill out info and hit save, will this pop up a message box
or will it just save? i cannot figure out from the materials i have been reading on this what the behavior is? Or am I way off here with my thinking of how this works?
AND if I hat cancel, will that remove the uniqueID from the table and not create a new record?

Private Sub Form_BeforeInsert(Cancel As Integer)
 If MsgBox("Save New Record or Cancel?", _ 
 vbYesCancel) = vbCancel Then 
 Cancel = True 
 End If 
End Sub

thanks

[CJ_London]

suggest try it and see. The before events fire when you leave the current record

edit: or you take some sort of action to save the record such as in vba you have the code me.dirty=true or DoCmd.RunCommand acCmdSaveRecord or go to a subform on the mainform
edit#2: all this assumes your form is bound to a recordset

[Micron]

I hit Undo button, will this code fire?

No. It fires the moment you do anything to save the record. I've never tried to make code run that shouldn't be run (i.e. I want to cancel but also want to make a procedure fire even though the event that triggered it hasn't happened). None of this will prevent the loss of an autonumber, which as we said, you should not be using as data. I'd say you're building a Rube Goldberg machine here.

[d9pierce1]

Hi Micron,
I am trying to wrap my head around this and do not understand the meaningful data part? I am using this as a code, not an actual AutoID... I refer to it when opening specific forms to that "Code #" it creates
Why is this something not meaningful? Has to have meaning or no one would create it, correct? I dont understand when i create a new record, it creates this number or Code, it goes directly in to the table and
when i hit undo it does not remove it? There should be a way when i hit undo it does not create a record or cancels it? If I save it then all is good. I just dont understand why this is any different then when i type
in a number or text, it goes to table and If i hit undo, it cancels or undoes everything i have done and no receord is created. Why does this number not undo and everthing else does undo? All i am doing is Calling on
that function to create a number for me.
Thanks

[d9pierce1]

Hi all,
Just curious, The Field "AccountCode" is a number, and in the form properties under this field i have the defautlt fnHeaderID(). Is there somewhere I could move this to a different
event that wouldnt create this number till I save, close, or go to next record so if I did undo, then that number wouldnt even be created yet? Just asking if one of the before events
would do this. Something like Me.AccountCode = fnHeaderID() or something to that effect?
Thanks

[Welshgasman]

Here is some code from one of mine

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
    Me.CreatedDate = Now()
    Me.CreatedBy = Environ("username")
Else
    Me.AmendedDate = Now()
    Me.AmendedBy = Environ("username")
End If
End Sub

[d9pierce1]

thank you Welshgasman
Dave

[Micron]

I am using a code to create a unique ID

Sorry, I misinterpreted your first post to be about the use of an autonumber to use as your data, like some sort of invoice number or such. You might find these useful, but depending on your code and events, you either need to prevent the record from saving, or if you cannot, find a way to delete it. Perhaps the other responders have put you on the right track, so I'll leave it at this for now.

https://support.microsoft.com/en-us/...86553682f9#bm2

I spend a lot of time looking here and in the related areas. You will probably find it very useful
https://docs.microsoft.com/en-us/off...s/object-model

[d9pierce1]

Hi all,
I have been trying to figure this out and finally took out the call for the function and that seems not to be the issue here.
I beleive I have something else creating the record and not allowing it to undo. Could this be as I am using a query to populate
this form that has several calculations in it. See below... Would the calculations prevent this from undo the record?


Any Idea possibly? Just one table in qry with several calculations in qry... All Bound to table except Calc
Thanks
Dave

[Micron]

Would the calculations prevent this from undo the record?

IMO, no. Any chance you can post a zipped db copy to cut to the chase?

[d9pierce1]

New Account 4 - Copy.zip

Hi Micron,
I made a copy and took out account #'s so maybe you can shed some light on it. Keep in mind i am still working on it. I set to open to
the Account Form that is in question. I have been trying different things such as I set record source to the table with no calculations, I
removed all the call to function, I removed all the default values, and it just creates a record no matter what. I did all these changes in
a different copy so this is origional. I just done understand why on earth it makes a record? I have similar code in other apps and it works
just fine. Not sure what i have done on this to create this issue.
Thanks for taking a look.
Has many other forms and qurys in it but keep in mind i am still working on them.....

Thanks
Dave

[Micron]

Getting late here so I have only had a few minutes to inspect. If this doesn't id the problem, then I'll look further tomorrow.

I'm going to say it's because you have default values in some fields and
- in subCommon_Form_Current(frm As Form) you have If Me.Dirty Then Me.Dirty = False - so too late, record is saved.
- in Private Sub Form_Current() you also have Me.Dirty = False - same effect

#1 question (you might have been asked this many times before) Did you step through this code before posting?
It's vitally important that you do if you want to acquire any trouble shooting skills.