How can I create a field (not autonumber) which automatically increments

[Found33]

Hi Tim


Well, I created the table and I have tried to get this event to work without success. With or without the event, when I open a new form, the Ref No shows a zero.
I did remember to change your OrdID control name to my Ref control name but other than that I left everything else the same. (Of course I also tried again, changing anything with the word "order" to see if that made any difference but it didn't)
Do any other names refer to controls on your form? Should I have put anything in the table other than creating those 2 fields with the names and properties that you said? There are still no entries in the table despite all my tries with the event procedure.
Sorry to be a pain but I'm really struggling with this. Thanks

[HMEpartsmanager]

Just in case you didn't know, the autonumber will not be generated until AFTER the record saves.

The event you put it under should be in the BEFORE_UPDATE event.

In my usage for say a generated PO Number, the form will not show the purchase order number in that field until after the record has been filled out and it now needs to save the record, so get the next purchase order number.

If the record is new, then it will get a new number, increment the file, and then save the record with that new number.

In my case, I have a Purchase Order Form, and a Line Item Sub Form. As I finish entering a purchase order and advance to the subform to enter line items, the record is saved and as it switches to the subform data area, the purchase order number in the form is now viewable.

Could that be it?

Also check for any spaces that may have been entered because of formatting issues with messages. In case you copied and pasted the lines I have listed, just follow the logic on each line so you know that there is no empty spaces.

You are getting there.

Tim

[Found33]

Hi Tim
Thanks for getting back to me. I have to admit that when I was first trying, it didn't occur to me about it applying the number AFTER saving. However, then I carried on and it still didn't work.
I did put the code in the BEFORE_EVENT item on the property sheet for the form.
I don't have a sub form. Mine is just a very simple form structure. The only complicated bit at all is the concatenated person's name field and that was a straight copy and paste from another database. Other than that, totally straight forward entry of the fields in the dogs table via the dogs input form.
I did copy and paste the lines you listed to ensure against me making typing errors but I don't understand the logic (such as what part strOrderName plays, ie why is it there at all?) to be able to check it, eg that last instruction completely baffles me.
Any thoughts please?
Thanks

[HMEpartsmanager]

Ok, let's see if I can break it down for you a little bit.

Create a Table to hold the different counters you want to use in your datebase. I now have over 661 counters in my table. This is for my database that is about 6 years old or so. I have a new number generated for each new Purchase Order; Line Item Order, Vendor Part Number, Mfg Part Number, Invoice Number, Invoice Line Item, etc.....

So create a table and create just 2 fields, 1 to hold the Counter Name (A String), and another one to handle the actual number (Long Integer) that is incremented and holds the current count.

Table: tblCounter

Field Name: nxtCountName
DataType: Text
Description: 16 Characters - Counter Name
Fields Size 16, Format >, Required Yes, Allow Zero Length No, Indexed with no Duplicates.

Filed Name: nxtCountInteger
DataType: Number
Description: Long Integer - Next Incremental Number
Field Size Long Integer, Format Fixed, Decimal 0, Default value 1, Required Yes, Indexed No.

That should give you a new counter table to hold any and all counters you want to create for your database.


Now onto your form. I am assumming here that your form is called DOGS or something similar.

Form: Dogs
When you go into your form, you wan the DESIGN VIEW. From there you want to go into PROPERTIES and look for the BeforeUpdate event. When you click on the right side to open up the code window; I believe the form will already have this part entered for you.

Private Sub Form_BeforeUpdate(Cancel As Integer)

End Sub


What you want to do is put any code you want to happen during this BeforeUpdate process. Since we want to get a new DOGS id number, that is where we place our code.


' This is just a comment line to show what is happening
' and are ignored in your code. It is only to help comment
' what your code is doing so you can remember when you
' look at it later down the road.
'
' Check to see if this a new record, and therefor needs a new
' ID number; an incremented counter
'
If Me.NewRecord Then
'
' If this is true, here is how we go get a new number
'
' We declare the variables we want to work with
' We need a String to hold the Counter Name
' We need a Long Integer to hold the actual number
Dim strOrderName As String
Dim intTestCount As Long
'
' The name of our counter we want to retrieve is DOGS2012
' You can change the quotes to be any counter name you
' want for anytime you use this code and need a counter.
' Set the counter 0 just in case this is the first time we are
' using this counter name.
strOrderName = "DOGS2012"
intTestCount = 0
'
' This ONE LINE goes to the counter table and reads the counter name
' you are going for, and gets the current count number.
'
intTestCount = Nz(DLookup("[nxtCountInteger]", "tblCounter", "[nxtCountName] = '" & strOrderName & "'"))
'
' We retrieved the current count, so let's increment it 1 number
intTestCount = intTestCount + 1
'
' YOURFIELD will be whatever your field name is. Mine was [ordId]
' Yours may be something else like DogIdNumber or whatever.
' Just put your field name into the YOURFIELD to edit this. The
' following line just says to make your field = to the incremented number.
[YOURFIELD] = intTestCount
'
' Now that we have a new number used, let's go set the counter
' table to the new number so it is written. If we didn't write it
' back to the counter table, it would never increment. -)
' The next SINGLE LINE does that.
CurrentDb.Execute "UPDATE tblCounter SET nxtCountInteger = " & intTestCount & " WHERE [nxtCountName] = '" & strOrderName & "'"
'
' That is all, we are done with this routine.
End If


Maybe that will help explain the process and how it is working.

YOURFIELD from above should now equal the incremented number you retrieved. After the update and save of the record, I am not sure what your database/form does next. Does it go to the next record to enter the next dog? If so, maybe arrowing back 1 record to the one you just created will show the field YOURFIELD with the number now.


Let's see if that gets you further.

Tim

[Found33]

Thanks Tim. You're a star. I'll give that a go.

[Found33]

Hi Tim
Thank you so much for all your explanations. At least I can see what it should be doing now. I'm afraid I still can't get it to work though. I double checked what I had done before and found that the field names in tblcounter were not spelt the same as in the event procedure so I've corrected that and proof read everything now but it is still not updating the table and therefore not updating the Ref field in the form.
I have therefore put in the value for the counter name (ie DOGS2012) in the tblcounter table but that has made no difference.
I've saved everything, closed the database, reopened it and started entering records again. Still not changing tblcounter or the Ref field in the form.
I've again double checked all the spellings and to make sure that there are no spurious spaces in the event procedure. Nothing.
Could it be anything to do with the autonumber field (DogID)that I already have as the primary key for the Dogs table? Would that affect it as it is generated as soon as I enter any data in the form?

BTW, to save the form, all I normally do is to either move on to a new (blank) form or move back to the previous form. Neither seems to affect the outcome of this counter saga.

The only other thing I can think that might possibly affect things is the property sheet for nxtCountName also shows No for IME mode and for IME sentence mode and Yes for Unicode compression, none of which means anything to me.
Any clues here at all?

[HMEpartsmanager]

Could it be anything to do with the autonumber field (DogID)that I already have as the primary key for the Dogs table? Would that affect it as it is generated as soon as I enter any data in the form?

Ok, now may be a good time to show us what the BeforeUpdate event looks like. Can you paste here the code so we can see?

Mine works, so let's see if there is anything we need to do to make yours work.

Tim

BTW, this event will only kick IF this is a new record. That is what the If Me.New is doing, checking to see if this is a new record. It dawned on me that maybe your DogID is 'creating' the record to begin with, so the code to run during the BeforeUpdate, won't run as the record already exists and it is record number DOGID.

If that is true, the record already exists, then you will need to put the field into another spot, or change the DogID primary key with this new key that is generated on a new entry.

So with that said, can you make a duplicate of the database, change your autonumber field not to do this, and then run the code so that when you advance the record (saving it), the BeforeUpdate event can run and get you a new incremented record number.

[Found33]

Hi Tim
Well, I tried the following with a duplicate copy: I changed the autonumber field to a number field in the table Dogs. I tried to change the primary key to be the Ref field (actually called ID) but I got the error that duplicates were allowed so to either change the primary key or change the field property so I reverted back to DogID as the primary key, albeit that it was now a manual number field rather than autonumber. I then created 3 new records and paged between them. they had had their Ref No (field name ID) as zero.

I've copied the event procedure and pasted it below:
Private Sub Form_BeforeUpdate(Cancel As Integer)
'
' This is just a comment line to show what is happening
' and is ignored in your code. It is only to help comment
' what your code is doing so you can remember when you
' look at it later down the road.
'
' Check to see if this a new record, and therefore needs a new
' ID number; an incremented counter
'
If Me.NewRecord Then
'
' If this is true, here is how we go get a new number
'
' We declare the variables we want to work with
' We need a String to hold the Counter Name
' We need a Long Integer to hold the actual number
'
Dim strOrderName As String
Dim intTestCount As Long
'
' The name of our counter we want to retrieve is DOGS2012
' You can change the quotes to be any counter name you
' want for anytime you use this code and need a counter.
' Set the counter 0 just in case this is the first time we are
' using this counter name.
strOrderName = "DOGS2012"
intTestCount = 0
'
' This ONE LINE goes to the counter table and reads the counter name
' you are going for, and gets the current count number.
'
intTestCount = Nz(DLookup("[nxtCountInteger]", "tblCounter", "[nxtCountName] = '" & strOrderName & "'"))
'
' We retrieved the current count, so let's increment it 1 number
intTestCount = intTestCount + 1
'
' YOURFIELD will be whatever your field name is. Mine was [ordId]
' Yours may be something else like DogIdNumber or whatever.
' Just put your field name into the YOURFIELD to edit this. The
' following line just says to make your field = to the incremented number.
[ID] = intTestCount
'
' Now that we have a new number used, let's go set the counter
' table to the new number so it is written. If we didn't write it
' back to the counter table, it would never increment. -)
' The next SINGLE LINE does that.
CurrentDb.Execute "UPDATE tblCounter SET nxtCountInteger = " & intTestCount & " WHERE [nxtCountName] = '" & strOrderName & "'"
'
' That is all, we are done with this routine.
'
End If

End Sub

I've gone through it again but can't see any flaw so I've looked at the properties of the field "ID" in my table "Dogs" and that shows a default value of "0" . Could that be the problem?

Thanks ever so for all your help. Much appreciated.

[HMEpartsmanager]

I think the problem is that you are creating a new record and entering data, so the record now exists. The BeforeUpdate event has the first line check to see if this is a NEW RECORD and if it is, continue through the code. Since it failed, meaning the record exists, it will not run the routine and skip the basic code and jump straight to the end where the line reads END IF.

So when you see code that says;

If SomethingSomething Then
Do this and that
And some more things
We are all done
End If

If the SomethingSomething is TRUE, the next 3 lines will run. If the SomethingSomething is FALSE, then it will skip the IF THEN statement and go to the END IF. Since there is nothing after the END IF, the routine is done and control is put back to your form. The code is checking that a condition is met, then taking the appropriate action. New record, do the code. Existing record, it don't need to run.

Can you confirm that the record is already created and that is happening?

In my database, I have a form that will let me add all the data I want, and then when I am ready, I will advance the record and the BeforeUpdate event will run. It checks that this is a new record, and then goes and gets an incremented number.

If I am working on the records throughout the database and making changes, the BeforeUpdate even will see my IF THEN statement, and come back FALSE because it already exists; so nothing happens. The routine is only running when there is a new record added.

I hope that makes sense.


My [ORDID] is my primary key.

Your FORM is using DogID as the primary key and is being generated when you create the record; I think. So when you run the BeforeUpdate event, had it not been an existing record, it would have pulled the counter name of [ID]. Then that [ID] could be displayed on your form.

Can you keep DogID as the primary key, change it to a number instead of an autonumber, and then in your form, don't have it create the record until your data is in and you are ready to create it. I say this as however it is being done now, I think it grabs an autonumber the moment you start the form. Then change the code portion to be DogID instead of ID and it 'should' work? ....

One more thing. If you are going to test this, you will need to go into the Counter Table and add the DogID countername and a count of 900 or something. The reason is if you have already entered 900 dogs into the system, then if the counter started at zero; the first incremented number would be number ONE. Well, you already have a primary key entered for number one. See how many records you have created in your database, and then go tell the table the name DogID has a value of that plus some. So when it goes to get the next incremented number, it will be higher than the numbers you already have in use.


All my forms use this routine to get incremented numbers, and it has been a long time since I have had to create a new form/subform, so there could be something real simple that can be changed in your Form; maybe someone will speak up who are following this thread.

Tim

[June7]

I have been reading bits and pieces. I take a different approach (post #5). My users want to see the new ID as soon as new record is initiated. So the ID is built and new record saved which makes it an existing record and I open form for user to input to this 'new' record. But people will be people and sometimes change their mind so I incorporate an abort procedure. I can't delete the record because all ID's must be accounted for and with multi-users a subsequent ID might have been established already, so the abort blanks the record fields except for the ID and it will be reused for the next 'new' sample login.

Yes, with Access autonumber field, number is grabbed as soon as data entered into any field. If entry canceled and record not saved or is deleted, the number is used but 'gone'. Results in sequence gaps. I have only one table in my project that uses an autonumber for key field. None of the other tables have an autonumber field.

[HMEpartsmanager]

Thanks for popping in. Your post illustrates the differences in whether you want a number now, or only if it is valid record and you are saving it. Nice idea for the ABORT button.

And your message also confirms that a record has already been created, so the routine will have no need to run.

With this information, hopefully a solution/answer can be resolved that satisfies what is looking to be done.

Tim

[Found33]

I have now tried not including the autonumber primary key field in the form but that makes no difference, presumably because it is still being created in the record even though it is not being displayed. Oh well, worth a try.

Might the idea work if the procedure could be adapted to run on DataChange of the DogID (autonumber) field?

[Found33]

June7, ideally I also would like to see the new ID as soon as a new record is initiated. Now that I have had an introduction to how an event procedure works, thanks to HMEpartsmanager, I can have a better go at understanding what you suggested in post 5.
The main thing I really didn't (and still don't) understand about that post is where to put all of those different pieces of code. If you could give me an idea as to where each bit goes, then I can have a try in the morning at adapting them to my database.

Thanks ever so

[June7]

Do you want to provide project for analysis?

Make copy, remove confidential data, run Compact & Repair, zip if still large. The Attachment Manager is below the Advanced post editor.

[HMEpartsmanager]

June7, ideally I also would like to see the new ID as soon as a new record is initiated. Now that I have had an introduction to how an event procedure works, thanks to HMEpartsmanager, I can have a better go at understanding what you suggested in post 5.
The main thing I really didn't (and still don't) understand about that post is where to put all of those different pieces of code. If you could give me an idea as to where each bit goes, then I can have a try in the morning at adapting them to my database.

Thanks ever so

I am glad you are still moving forward and not giving up. One of the reasons why I put the comments into the code I gave you was so you could follow logically what was happening. Do that for any code you are given or want to use, follow the logic.

You will learn as time goes on where to put the code based on when you want it to run. You can see on your form a few different areas where an event can trigger.

Keep at it! Maye post #5 is more clear now to you and you will be able to follow the logic of what is happening.

Tim