Auto Numbering

[Bob Line]

Hi, every time I add a new record I get a new reference number by using
Access Auto Numbering which is what I need and use.

Now what I need to do is if my claim field policy number starts with "TTR"
then I need to add an additional reference number to my POC field
automatically, is this possible? The POC field numbers come in batches of
500 so I must be able to update the start number from time to time.

Any help or idea's would be much appreciated.

Bob
--

 

[Pavel Romashkin]

Is POC the Autonumber field? If yes, then you can not do this and need
to change the design. Autonumber fields should not contain information
intended for a human user.
If POC is not an autonumber field, you can set it to anything you'd like
in your form. I would use the AfterUpdate event of your
ClaimPolicyNumber field to check if the policy name starts with "TTR",
and then insert the proper POC number.
I would recommend recording batches of POC as you receive them into a
separate table from which you can check them out as you need. You can do
that either by adding 500 records to a table and using a "isUsed" field
to check the used number, or you can denormalize it and simply record
the last available number, if they are sequantial.

Hope this helps,
Pavel

 

[John Vinson]

Hi, every time I add a new record I get a new reference number by using
Access Auto Numbering which is what I need and use.

You should NOT use autonumbering for any number that people will see.
Autonumbers have one purpose ONLY: to provide an almost-guaranteed
unique key. They will always have gaps and you cannot easily control
the values you get.

Now what I need to do is if my claim field policy number starts with "TTR"

If it's always TTR, then don't store the TTR at all - introduce it
with a Format expression.

then I need to add an additional reference number to my POC field
automatically, is this possible? The POC field numbers come in batches of
500 so I must be able to update the start number from time to time.

Since I have no idea what a POC number might be, nor what you mean by
"update the start number from time to time" I can't advise.

Any help or idea's would be much appreciated.

Use a Long Integer field and maintain it in VBA code rather than
attempting to use an Autonumber for either of these fields.

 

[Bob Line]

John you say maintain in using VBA how do I do it? sorry but I need a lot
more help than that.

Thanks Bob

 

[Bas Cost Budde]

John you say maintain in using VBA how do I do it? sorry but I need a lot
more help than that.

For one thing, Access cannot do much with values in tables. The general
solution in Access consists of these steps:

1) Edit, and mostly insert, records through forms. Always. Never
directly in the table (unless you know how to fiddle around of course--I
mean don't let your users)
2) In the BeforeUpdate event of the form, find the new number and assign
it to the field

Now 2 can be broken down to

* open the form in design mode
* view the property sheet (from the menu Viewroperties, for example)
* select the Events tab
* at BeforeInsert, choose [Event Procedure] from the list. AutoComplete
helps: typing the '[' is enough
* click the three dot button (it's called Build, I believe). You arrive
in the code module for the form
* type this between Sub...End Sub:
me!yourfieldname = nz(dmax("yourfieldname","yourtablename),0)+1

This works for an all-numerical field.
-> if you want to have a sequence number inside a compound primary key,
include a third parameter in the DMax() see Help
-> if you have a composite 'number' where only the numerical portion
should increase, you need more elaborate measures - or a redesign, it is
not wise to store non-atomic values in one field.

 

[dbl]

Wouldn't this up date the field every time the form was opened? I only want
to up date the field if a certain criteria is meet in a policy field. If
this criteria is meet then it would up date the field to the next number. I
believe that you have to have a table with the numbers in and it adds a
number each time it opens. But how do I code it?

Thanks Bob

John you say maintain in using VBA how do I do it? sorry but I need a lot
more help than that.

For one thing, Access cannot do much with values in tables. The general
solution in Access consists of these steps:

1) Edit, and mostly insert, records through forms. Always. Never
directly in the table (unless you know how to fiddle around of course--I
mean don't let your users)
2) In the BeforeUpdate event of the form, find the new number and assign
it to the field

Now 2 can be broken down to

* open the form in design mode
* view the property sheet (from the menu Viewroperties, for example)
* select the Events tab
* at BeforeInsert, choose [Event Procedure] from the list. AutoComplete
helps: typing the '[' is enough
* click the three dot button (it's called Build, I believe). You arrive
in the code module for the form
* type this between Sub...End Sub:
me!yourfieldname = nz(dmax("yourfieldname","yourtablename),0)+1

This works for an all-numerical field.
-> if you want to have a sequence number inside a compound primary key,
include a third parameter in the DMax() see Help
-> if you have a composite 'number' where only the numerical portion
should increase, you need more elaborate measures - or a redesign, it is
not wise to store non-atomic values in one field.

 

[Bas Cost Budde]

Wouldn't this update the field every time the form was opened?

No; only when you add a new record.

I only want to update the field if a certain criteria is met in a policy field. If
this criteria is met then it would update the field to the next number.

You mean *change* the field? I thought you were asking this for addition
of a *new* record.
I

believe that you have to have a table with the numbers in and it adds a
number each time it opens. But how do I code it?

You can have a table with the next free number, that is a very good
multi user approach, but still concerns insertion, not update.