In need of a Cancel Entry button (1 Viewer)

JWPratt8

Registered User.
Local time
Today, 15:25
Joined
Jul 15, 2013
Messages
23
Hi all,

I'm looking for a button that cancels an entry on a form. I want everything within that form to be cancelled including the Primary Key field. I currently have a button the uses the me.Undo code and then automatically closes, however this only clears the data that has been entered by the user. So when the user re-opens the form the primary key field is showing a 2 instead of a 1.

Your help would be much appreciated!

Thanks,

J.
 

CazB

Registered User.
Local time
Today, 22:25
Joined
Jul 17, 2013
Messages
309
Not much help... but the only option I know to 'clear' an unused key is to repair and compact the database.... would be interested to hear any other options so I'll watch your thread with interest :)
 

missinglinq

AWF VIP
Local time
Today, 18:25
Joined
Jun 20, 2003
Messages
6,423
If your 'Primary Key Field' is referring to an AutoNumber, as it sounds, this Datatype is intended to be used for one purpose and only one purpose, to provide a unique identifier for each Record. Here's a post I've archived from a gentleman named John Vinson, MVP, explaining how Autonumbers work:

When using Autonumber, do be aware that there will be gaps in the numbering any record that's deleted will leave a gap; hitting <Esc> after starting a record will leave a gap; adding records using an Append query may leave a gap, often a huge one; replicating the database will make your invoice numbers random.

In short... it's best NOT to use Autonumbers for human consumption, and particularly not for consumption by accountants and auditors. Invoice sequences like 319, 321, 322, 385, 386, 221841246, 1083225152 make such people get very nervous.

For the kind of thing you want to do, here, you need to use an auto-incrementing number hack such as one of these.

If you’re using a Numerical field:

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
  If RecordsetClone.RecordCount = 0 Then
   Me.IDNumber = 1
  Else
   Me.IDNumber = DMax("[IDNum]", "YourTableName") + 1
  End If
End If
End Sub

The same thing for an IDNumber that is defined in the Table as Text datatype.

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
  If RecordsetClone.RecordCount = 0 Then
   Me.IDNumber = "1"
  Else
   Me.IDNumber = DMax("val([IDNumber])", "YourTableName") + 1
  End If
End If
End Sub

Linq ;0)>
 

JWPratt8

Registered User.
Local time
Today, 15:25
Joined
Jul 15, 2013
Messages
23
Thanks guys.

I think i will take your advice and just remove the autonumber, as I hate seeing the gaps. It's not a NEED in the database anyway as there is another field that will do a similar job but that is just a Number field.

However, thank you for your responses.

J.
 

Users who are viewing this thread

Top Bottom