Auto Numbering missing records

[Gary W. Graley]

While it isn't missing, I think what is happening is
1. a person is 'going' to enter a record
2. Access assigns a record number for that entry
3. that person backs out and doesn't enter the information
4. Access says 'fine' but that place marker, aka Auto number
stays allocated but there are no records, even blank
5. so, the next time the person/s view the entry forms they see
that the record number does not match the total number of records
example, they see a total number of 930 records, but are only on 919
when checking the table, it is missing those records.

Sorry for my long winded scenario, but, is there a way to prevent this from
happening? Even if they decide to back out, I'd like to at least keep an
empty record as a place holder.

Thanks in advance,
G2

 

[Mile-O]

While it isn't missing, I think what is happening is
1. a person is 'going' to enter a record
2. Access assigns a record number for that entry
3. that person backs out and doesn't enter the information
4. Access says 'fine' but that place marker, aka Auto number
stays allocated but there are no records, even blank
5. so, the next time the person/s view the entry forms they see
that the record number does not match the total number of records
example, they see a total number of 930 records, but are only on 919
when checking the table, it is missing those records.

That's exactly what's happening.

... is there a way to prevent this from
happening? Even if they decide to back out, I'd like to at least keep an
empty record as a place holder.

You shouldn't use the Autonumber as anything other than a unique identifier. If you need to assign numbers, then you would be best to assign it in the BeforeUpdate event of your form, by using the DMax() domain aggregate function and adding 1 to it.

 

[Gary W. Graley]

That's exactly what's happening.

You shouldn't use the Autonumber as anything other than a unique identifier. If you need to assign numbers, then you would be best to assign it in the BeforeUpdate event of your form, by using the DMax() domain aggregate function and adding 1 to it.

Sweet, much thanks to you for that, will research on how to enter that
and use that from now on!

I just love creating databases, it's educating the others that seems
to be such a taxing career
I wish that my job was in the IT dept, some day, I keep knocking on that
door but they find more important things for me to do, but, I am the go to
guy for databases in the company. If I were actually trained, I'd love it
even more, as it is, I try, try, try and try again.

Again, thank you,
G2

 

[Mile-O]

I wish that my job was in the IT dept

Same here.

I am the go to guy for databases in the company.

Same here.

 

[missinglinq]

Mile-O's advice is dead on! The rule of thumb is, if you're worried about the appearance, order or gaps of your AutoNumber, you're using it incorrectly, and should be using an auto-incrementing hack! Aside from the scenario you detailed, gaps will also appear for a number of other reasons including when deleting records and adding records using an Append Query. Replicating the database will automatically make your AutoNumbers random. John Vinson, MVP, said it best "AutoNumbers are not fit for human consumption!

Here's a typical Auto-incrementing Number hack. The first code here would be for an IDNumber that is defined in the table as Text datatype. "Number" fields that aren't used for math really should be defined as Text.

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

Here's the same code for an IDNumber defined as Numerical:

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

Linq

 

[Gary W. Graley]

Funny how that works!

I am not getting it, so to speak, stuck in the macro world, forgive me,
tried to create a macro so that when they left the first field of input
that it would update that counter field by 1 over what the Max number
currently was, but, I am having trouble deciphering the syntax of this all.

I have the following

Table is
T_Purge

Field I made as a number field
PurgeID

what I tried was making a macro that would RunCommand

Dmax (PurgeID, T_Purge) + 1

but, that is failing, miserably

What would or should it be?
Thanks
G2

 

[Gary W. Graley]

Thanks Linq, we were typing at the same time, I will try to
put that into effect.

G2

 

[gemma-the-husky]

two ways of getting sequential numbers

either

a) store the next number in a separate table, then when you need a number, read the next number, and increment the counter

or

b) find the highest number in the current table, and add one to it

-----------
but its not macros you want - if you enter your data in a form, then get the number as part of the forms beforeupdate event, when you are entering a new record

so

event handler
sub beforeupdate_click

if me.newrecord then
     indexnumber = getnumber
end if

end sub

where getnumber is a function that retrieves the number by either method a or b

 

[Gary W. Graley]

Thanks, I certainly need to study more on VBA, forgot to mention the
database is in Access 2003 format.

This change in counting will help in several database where a number is
referenced for internal and external customers, instead of relying on the
deadly Auto numbering system

And yes, understood that they'd lose sequence of numbers if they delete
or append, but in these database, they are used for historical reference and
no records are removed or appended. Also there is no replicating done, they
are kept on our server and entered from there, not taken on the 'road'
So for the most part, years actually, it's not been TOO big a deal but every now
and then someone hiccups and counts are off.
A FUN thing to bring it back into balance but that's what I've been having to do.

So, once I sort out and make this work, I'll be a happier guy all around

There are probably some good books listed on other threads, I'll do a search, but
if one of you have a favourite book that may help in regards to learning VBA
I'd appreciate that reference,
Thanks again for your help,
G2

 

[Gary W. Graley]

Ok, cool got it to work,
My one problem is I'm dealing with people that are upset if they
can't or don't see ALL the complete sequence of numbers
so this should help, As long as they DON'T go deleting they will
be fine, and if they do, I can now add in a record back in a lot easier!
Thanks,
G2

 

[missinglinq]

Glad you got it working!

Linq

 

[Sum Guy]

I downloaded this off another forum some time ago. The paper is yellow its so old but it works great.
I don't recall whose code it is but it works like a charm for year and sequence. If you recognize it please claim it.

(quote)

Create two fields in your table:
TheYear - long
TheSequence – long

Then enter this code in your form's BeforeInsert
Me.TheYear = CLng(DatePart("YYYY", Date()))
Me.TheSequence = Nz(DMax("[TheSequence]", "YourTableName", "[TheYear] = "&Me.TheYear),0) +1

That’s all the code you need. When a new year starts the DMax function returns a Null, which the Nz function changes to a 0. Then it just adds 1 to get the next value.

Why two fields? You will spend a lot of time writing code to continuously tear them apart. It’s much easier to store and group them when they're stored separately.

It’s also easier to format them in a query.

(end quote)

What happens is when you make your first keystroke on the form the year and sequence are entered automatically. So if your last entry was
TheYear - 2006 TheSequence – 14
the next will be
TheYear - 2006 TheSequence – 15
assuming its still 2006. If this is the first entry in 2007 you will get:
TheYear – 2007 TheSequence – 1

Be sure to set TheYear and TheSequence text boxes on your forms to Enabled-NO Locked-YES. This will prevent people from over riding the pre-set numbers.

 

[missinglinq]

That's a nice piece of code, but if you read the OP's posts again, you'll see that nowhere does he mention wanting to reset these numbers every year!

 

[ROC]

Milo

Milo,

I am new at this and not sure if I even pushed the right button. I saw your response with regard to auto numbering a field. I have been looking for a solution to that problem for a while now. I have never seen anything like your response. Most solutions I see have long lines of code associated with it. Even then it doesn't work the way I want it to. Could you be a little more explicit for someone new at this.

Thanks.

ROC

 

[missinglinq]

The code I posted is actually pretty standard stuff. It only really gets complicated if you want to do something fancy, such as in the code Sum Guy's posted, where the number includes the year and resets to 1 at the start of a new year.

I'm not really sure what you mean by being a little more explicit, the code's pretty straight forward. Which snippet of code you use, as stated, depends on whether your field is defined as numeric or text.

If Me.NewRecord Then 'Only assign a number if the record is a new record

If RecordsetClone.RecordCount = 0 Then
Me.IDNumber = 1 ' If this is the first record in the table then the number is 1

Else 'Otherwise add one to the highest number already in the table
Me.IDNumber = DMax("[IDNumber]", "YourTableName") + 1


Me.IDNumber = DMax("val([IDNumber])", "YourTableName") + 1

This is the name of the textbox on your form that will hold/display the number

Me.IDNumber = DMax("val([IDNumber])", "YourTableName") + 1

This is the name of the field in your table that will hold the number. Depending on how you set up your db, it may or may not have the same name as the textbox on your form.

Me.IDNumber = DMax("val([IDNumber])", "YourTableName") + 1

The name of your table where the data resides.

Be sure and look up DMax() in Access' Help for all the details on how it works.

Hope this helps!

 

[swift]

That code worked fine for me after i had to bin out the Autonumber function. I have one issue though - I need my users to see the number as its generated. At the moment it only appears when you use the record selectors to go back one record then skip forward again.

Can anybody help out there please?

thanks in advance

swift

 

[missinglinq]

You could place it in the Form_BeforeInsert, which will generate the date when the first keystroke is entered in the new record. The problem with this, if you have multiple, simultaneous users, is that you could have two records with the same number.

UserA starts a record, number is inserted, and more data entered.

UserB starts a record, number is inserted, and more data entered

Using DMax() both could get the same auto-incrementing number if their entering of the data overlaps..

Using the Form_BeforeUpdate all but eliminates this, as it's added immediately before the record is saved not when the record is started.

So if you place the code in the Form_BeforeInsert event, so that it's immediately visible, you'll have to force a save immediately after generating the number, using Me.Dirty = False.

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.NewRecord Then
  If RecordsetClone.RecordCount = 0 Then
   Me.IDNumber = "1"
   [B]Me.Dirty = False[/B]
  Else
   Me.IDNumber = DMax("val([IDNumber])", "YourTableName") + 1
   [B]Me.Dirty = False[/B]
     End If
End If

The only problem this presents is that the record is saved immediately, and if the user decides to dump it, it will have to be deleted rather dumped than using Me.Undo.

 

[swift]

Thats great stuff, Missinglinq, worked a treat first time.
Thanks for taking the time and effort to reply!

Cheers,

john

 

[gemma-the-husky]

Ok, cool got it to work,
My one problem is I'm dealing with people that are upset if they
can't or don't see ALL the complete sequence of numbers
so this should help, As long as they DON'T go deleting they will
be fine, and if they do, I can now add in a record back in a lot easier!
Thanks,
G2

the trouble with adding stuff in to fill a blank sequence is

a) its hard to find the gaps automatically
b) you will end up eith stuff out of sequence or
c) even worse you will end up with otherwise blank records, which might give you issues down the line

where sequential numbers have been allocated, you probably shouldnt delete the itesm - just mark them as not required in some way