Customer Autonumber increments?

[Sam Zuhbi]

Hi,

I know this question is like a broken record but I cannot find a simple answer to follow, I am new to Access/VBA but I can find my way round so I do not have a problem with following instructions so I hope someone will have the time to help me out on this one.

Here goes, I have a table with a number of fields as follows
Job Number <---- Auto Number and Primary Key
Customer
Work Done


I need to make sure that each job number is increment and also a unique key which cannot be duplicated. Now when I use the Access Autonumber it goes stupid if I accidently add a new record to a form and press escape. so when I decide to add another record the sequence is gone.

I know this cannot be done on a table level, So it needs to be done on a form level, is there a way to create a method to increment the numbers based on the last record?

I would appreciate the help and I am desperate :(

Thank you
Sam

[liimra]

DMAX is the solution. Easy, Simple and straight forward.

Change the Autonumber field to Normal Number field. and make the default value for that field

= DMAX ("NumberFieldNAME","TableNAME") + 1

Of course there are many workarounds to set the DMAX number through Macros or VB (On Load, After update..etc); but the simplest is the default value.

If the table is new and there is no least number, the above expression will
give no outcome. that is why you use the NZ:

= NZ(DMAX ("NumberFieldNAME","TableNAME"),0) + 1

Now, if there is no value, the value given will be 1.

Please change to given names.


Regards,
Ali

[NeoPa]

This question is unusually asked clearly. You're right in that it's asked quite frequently. It's rarely asked clearly though. This is a pleasant exception.

As far as it goes Ali's answer is good. It's well explained and covers the basic concepts.

What may be additionally required though, but only in some circumstances, is the handling of this in multi-user environments. In such an environment, or for when developing work which may be used that way in future, it is important only to check the table and prepare a value at the point of writing away the record (In the Form_BeforeInsert() event procedure).

This does mean that the value cannot be shown to the operator beforehand. Many find this strange and un-user friendly, but that's only because they don't properly grasp the concepts. You'll never see the value of an AutoNumber showing until the record is created. That's because until the record is created it doesn't exist. If you keep to this design you won't go far wrong.

Lastly, the sequence numbers cannot be guaranteed if records are removed after new ones have been created. This only guarantees that the numbers are created in the correct sequence.

[TheSmileyCoder]

One problem with the DefaultValue approach is that defaultValues are "loaded" when the record starts being edited. This means that if user A starts record 10 (9 was the last saved record) and user B starts a record before user A saves, both records will be 10 (and the save will fail, with a message about creating duplicate values in index.)

I would recommend doing it in the Forms Before update event. If you select your FORM's properties (click top left box of your form) and select Events, go for BeforeUpdate. Click the 3 ... on the right hand side, and select Event Procedure and you will be shown a Visual Basic for Applications (VBA) editor window. Access will have filled in some information for you allready, looking like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
End Sub

This is the event access will run before a update is performed. Now add following:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  If Me.NewRecord 'Only do following if its a newrecord
     Me.tb_ID=Dmax("ID_Field","tbl_Whatever")+1
  End If
End Sub

Where tb_ID is the name of a textbox on your field, with its controlsource being your primary key of type Number, Long (so not autonumber)

[NeoPa]

Bizarre! The time of your post appears on my screen as earlier than the one on mine, yet it appears afterwards in the list. Maybe you edited it or something.

Anyway, a good point Smiley, but I would draw your attention to a couple of points :

1. The Nz() part of Ali's post. This needs to be included.
2. Although it's possible to use a Before... event procedure, it's more logical to use an After... one. No it isn't!! What am I on about. My solution should have used Form_BeforeInsert(). I'm glad you posted. It made me think properly.
3. Form_BeforeInsert() is preferable to Form_BeforeUpdate() as it already works only on new records (you needn't add the extra code).
   Forget this point! BeforeInsert Event [Access 2003 VBA Language Reference] explains at which point the event is triggered and it's not where I thought. Nor is it appropriate for this.

It seems Smiley had more right than I did all along :-(

[Sam Zuhbi]

I tried this method but I am getting an error, is that because I cannot use spaces in the names and I need to use under scores?

Should I change the Table name and field name to names with out Spaces?

[Sam Zuhbi]

Is there some code missing from the IF command such as then?

[TheSmileyCoder]

Hi, yes there is missing a Then part.

Private Sub Form_BeforeUpdate(Cancel As Integer)
  If Me.NewRecord Then 'Only do following if its a newrecord
    Me.tb_ID=Nz(Dmax("[ID Field]","[tbl Whatever]"),0)+1
  End If
End Sub

If your tables (and/or fields) have spaces you should enclose them in square brackets.It is also correct that you should use the Nz function to guard against the case of no result being returned (if its the first record in the table)

As a general rule, I personally always avoid using spaces in my table and field names.

NeoPa, you're correct it could also be placed in BeforeInsert. I personally just have alot of code running in the beforeUpdate and prefer to keep the code gathered there. Either way will work though.

NeoPa:
Please ignore this as it turned out I wasn't correct at all - as explained in post #5.

[liimra]

Yes Neopa is right and I totally agree to create the number just before saving the record in case you are working in multi-user environment; Though I would set the value (number) through a "Save" button.

In case you want to have all numbers in sequence, you have to update the table (query) manually or automatically everynow and then when old records are deleted because as Neopa stated
"the sequence numbers cannot be guaranteed if records are removed after new ones have been created. This only guarantees that the numbers are created in the correct sequence."

Example: Suppose you have five records with the following numbers (1,2,3,4,5); If you delete the third record, the sequence is lost because the next record you create will have number 6 (5+1). To overcome this, you change the numbers back in order manually or automatically using VB. Is that everything? Not quite, because I would really advice not to delete any record meaning the number. For example, suppose this number is used for invoices. Later you wanted to delete the third invoice because it was canceled; in this case I would advise to change the value, quantity and any other related field to null, and state the reason for canceling, while keeping the number (and the "date".)


Hope that helps,
Regards
Ali

[NeoPa]

Smiley:
NeoPa, you're correct it could also be placed in BeforeInsert. I personally just have alot of code running in the beforeUpdate and prefer to keep the code gathered there. Either way will work though.

It certainly works your way and I can see your rationale (particularly for your own situation). Actually, including different approaches exposes the thread to more available options anyway. Me.NewRecord is an interesting one that inexperienced programmers are unlikely to know about first off.

[NeoPa]

Sam, notwithstanding the tone of my reply in the other thread (since deleted and private) we can make this solution more understandable for you should you but explain where your confusion lies.

There are some pretty simple steps in here, but there is also a lot of other discussions around the subject and other (very similar) solutions posted. If you need help understanding which to focus on then just ask. Actually, Ali's post #2 gives a pretty clear breakdown of the concept. My post #3 explains some of the multi-user complications should that be relevant to your requirements. Smiley's post #4 gives some actual code to copy/paste if that's what you need. The code was updated later, in post #8, after some of the discussion, but seems to be what you asked for.

Reading your posts I get the impression of someone who can express themself clearly. Let us know what is missing if you still feel it is.

[Sam Zuhbi]

thank you for everyone who helped I realised the problem, I wasn't referring to the correct field and table name, as I got confused of the format, I even managed to completely ruin the form to the point where access was spitting out an error of low memory. But it is all good and thank you for your help my problem is now solved, I know the solution is not water tight but for what I am doing it does the job. and I do apologise if I was out of line with my other post, no intention of upsetting anyone but I am out of my depth with this stuff and I should be more clear next time.
Thanks again
you guys been great.
Sam

[NeoPa]

Good for you Sam. I'm pleased you managed to find your way through in the end, and we'll be here if you need us again :)

[liimra]

Glad it worked for you. Would you kindly mark this post as answered
so it becomes more helpful for others.

Regards,
Ali

[Sam Zuhbi]

How do I mark it as answered?

[NeoPa]

I recently found that the Form_BeforeInsert() event procedure is triggered as soon as a new record is dirtied (IE. Any data is entered into it.), and not when the new record is actually added to the record source. With that in mind I'd like to amend my post #5.