automatic numbering question (not Auto

[Guest]

I have a database of Agreements. I have a table for Agreements p/k
Autonumber, CompanyInformation, etc. These documents are going to be filed
in binders by Company Name and numbered, for instance:

1. Abigen
2. Affibird
3. Axxx
4. Azzz

1. Baaa
2. Bddd
3. Beee

I thought I would make a AgreementNumber, i.e., A0001, A0002, B0001, B0002,
etc. (The actual hard copies of the agreements are in a bind with section tab
A, and then number 1, 2, 3, B, 1, 2, 3)

The problem is that when we get new agreements, we will need to change all
of the numbers to correspond with the placement of that agreement in the
binder. That is why we are dividing them into separate letters so we don't
have to move as many documents if we have an agreement to insert.

I don't want to use the Autonumber feature because it will not correspond to
the physical tab.

Does anyone have any suggestions, etc.

Also, when you create a new record, how do you automatically insert the next
number in the field?

Thanks for any assistance.

 

[Guest]

Use the DMax function. First, you need a field in the table to hold the
number. You also need a field to hold the prefix (A, B, etc.). Then to
generate the next number use DMax and add 1:

lngNextNum = Nz(DMax("[SEQ_NUMBER]", "Agreements", "[SEQ_PREFIX] = '" _
& Me.txtPrefix & "'"),0) + 1
Me.txtBinderNumber = Me.txtPrefix & Format(lngNextNum, "0000")

You will, of course, have to change the name to suit you needs.

 

[Guest]

That is great, but what about when I have to insert an agreement that will
mess the numbers up, will it automatically change all of the numbers in the
new order?
--
S

Use the DMax function. First, you need a field in the table to hold the
number. You also need a field to hold the prefix (A, B, etc.). Then to
generate the next number use DMax and add 1:

lngNextNum = Nz(DMax("[SEQ_NUMBER]", "Agreements", "[SEQ_PREFIX] = '" _
& Me.txtPrefix & "'"),0) + 1
Me.txtBinderNumber = Me.txtPrefix & Format(lngNextNum, "0000")

You will, of course, have to change the name to suit you needs.

I have a database of Agreements. I have a table for Agreements p/k
Autonumber, CompanyInformation, etc. These documents are going to be filed
in binders by Company Name and numbered, for instance:

1. Abigen
2. Affibird
3. Axxx
4. Azzz

1. Baaa
2. Bddd
3. Beee

I thought I would make a AgreementNumber, i.e., A0001, A0002, B0001, B0002,
etc. (The actual hard copies of the agreements are in a bind with section tab
A, and then number 1, 2, 3, B, 1, 2, 3)

The problem is that when we get new agreements, we will need to change all
of the numbers to correspond with the placement of that agreement in the
binder. That is why we are dividing them into separate letters so we don't
have to move as many documents if we have an agreement to insert.

I don't want to use the Autonumber feature because it will not correspond to
the physical tab.

Does anyone have any suggestions, etc.

Also, when you create a new record, how do you automatically insert the next
number in the field?

Thanks for any assistance.

 

[Guest]

Are you saying you would have A0001, A0002, A0003 and you would want to make
a new A0002 so you want to renumber 2 to 3 and 3 to 4?
No, it wont do that. You would have to do that programmatically.

That is great, but what about when I have to insert an agreement that will
mess the numbers up, will it automatically change all of the numbers in the
new order?
--
S

Use the DMax function. First, you need a field in the table to hold the
number. You also need a field to hold the prefix (A, B, etc.). Then to
generate the next number use DMax and add 1:

lngNextNum = Nz(DMax("[SEQ_NUMBER]", "Agreements", "[SEQ_PREFIX] = '" _
& Me.txtPrefix & "'"),0) + 1
Me.txtBinderNumber = Me.txtPrefix & Format(lngNextNum, "0000")

You will, of course, have to change the name to suit you needs.

I have a database of Agreements. I have a table for Agreements p/k
Autonumber, CompanyInformation, etc. These documents are going to be filed
in binders by Company Name and numbered, for instance:

1. Abigen
2. Affibird
3. Axxx
4. Azzz

1. Baaa
2. Bddd
3. Beee

I thought I would make a AgreementNumber, i.e., A0001, A0002, B0001, B0002,
etc. (The actual hard copies of the agreements are in a bind with section tab
A, and then number 1, 2, 3, B, 1, 2, 3)

The problem is that when we get new agreements, we will need to change all
of the numbers to correspond with the placement of that agreement in the
binder. That is why we are dividing them into separate letters so we don't
have to move as many documents if we have an agreement to insert.

I don't want to use the Autonumber feature because it will not correspond to
the physical tab.

Does anyone have any suggestions, etc.

Also, when you create a new record, how do you automatically insert the next
number in the field?

Thanks for any assistance.

 

[Guest]

Exactly, but I am not sure how to go about it. Any suggestions?
--
S

Are you saying you would have A0001, A0002, A0003 and you would want to make
a new A0002 so you want to renumber 2 to 3 and 3 to 4?
No, it wont do that. You would have to do that programmatically.

That is great, but what about when I have to insert an agreement that will
mess the numbers up, will it automatically change all of the numbers in the
new order?
--
S

Use the DMax function. First, you need a field in the table to hold the
number. You also need a field to hold the prefix (A, B, etc.). Then to
generate the next number use DMax and add 1:

lngNextNum = Nz(DMax("[SEQ_NUMBER]", "Agreements", "[SEQ_PREFIX] = '" _
& Me.txtPrefix & "'"),0) + 1
Me.txtBinderNumber = Me.txtPrefix & Format(lngNextNum, "0000")

You will, of course, have to change the name to suit you needs.

:

I have a database of Agreements. I have a table for Agreements p/k
Autonumber, CompanyInformation, etc. These documents are going to be filed
in binders by Company Name and numbered, for instance:

1. Abigen
2. Affibird
3. Axxx
4. Azzz

1. Baaa
2. Bddd
3. Beee

I thought I would make a AgreementNumber, i.e., A0001, A0002, B0001, B0002,
etc. (The actual hard copies of the agreements are in a bind with section tab
A, and then number 1, 2, 3, B, 1, 2, 3)

The problem is that when we get new agreements, we will need to change all
of the numbers to correspond with the placement of that agreement in the
binder. That is why we are dividing them into separate letters so we don't
have to move as many documents if we have an agreement to insert.

I don't want to use the Autonumber feature because it will not correspond to
the physical tab.

Does anyone have any suggestions, etc.

Also, when you create a new record, how do you automatically insert the next
number in the field?

Thanks for any assistance.

 

[Guest]

I would suggest you don't do it. It really makes no sense to try to keep a
database in sync with a paper filing system. Pardon my bluntness, but only a
bean counter with severe OCD could come up with a scheme like this.

But, I do understand we don't always have control over the situation. So,
one way to do it would be to create a query to use as a recordset that would
contain only the records that need to be changed. Order it in descending
order so the highest number will be the first record returned. Then add 1 to
the number and update the record. Do that until you get to where the new
agreement will be.

Exactly, but I am not sure how to go about it. Any suggestions?
--
S

Are you saying you would have A0001, A0002, A0003 and you would want to make
a new A0002 so you want to renumber 2 to 3 and 3 to 4?
No, it wont do that. You would have to do that programmatically.

That is great, but what about when I have to insert an agreement that will
mess the numbers up, will it automatically change all of the numbers in the
new order?
--
S


:

Use the DMax function. First, you need a field in the table to hold the
number. You also need a field to hold the prefix (A, B, etc.). Then to
generate the next number use DMax and add 1:

lngNextNum = Nz(DMax("[SEQ_NUMBER]", "Agreements", "[SEQ_PREFIX] = '" _
& Me.txtPrefix & "'"),0) + 1
Me.txtBinderNumber = Me.txtPrefix & Format(lngNextNum, "0000")

You will, of course, have to change the name to suit you needs.

:

I have a database of Agreements. I have a table for Agreements p/k
Autonumber, CompanyInformation, etc. These documents are going to be filed
in binders by Company Name and numbered, for instance:

1. Abigen
2. Affibird
3. Axxx
4. Azzz

1. Baaa
2. Bddd
3. Beee

I thought I would make a AgreementNumber, i.e., A0001, A0002, B0001, B0002,
etc. (The actual hard copies of the agreements are in a bind with section tab
A, and then number 1, 2, 3, B, 1, 2, 3)

The problem is that when we get new agreements, we will need to change all
of the numbers to correspond with the placement of that agreement in the
binder. That is why we are dividing them into separate letters so we don't
have to move as many documents if we have an agreement to insert.

I don't want to use the Autonumber feature because it will not correspond to
the physical tab.

Does anyone have any suggestions, etc.

Also, when you create a new record, how do you automatically insert the next
number in the field?

Thanks for any assistance.

 

[Guest]

Oh, you know my boss? Her last name is fussey.
--
S

I would suggest you don't do it. It really makes no sense to try to keep a
database in sync with a paper filing system. Pardon my bluntness, but only a
bean counter with severe OCD could come up with a scheme like this.

But, I do understand we don't always have control over the situation. So,
one way to do it would be to create a query to use as a recordset that would
contain only the records that need to be changed. Order it in descending
order so the highest number will be the first record returned. Then add 1 to
the number and update the record. Do that until you get to where the new
agreement will be.

Exactly, but I am not sure how to go about it. Any suggestions?
--
S

Are you saying you would have A0001, A0002, A0003 and you would want to make
a new A0002 so you want to renumber 2 to 3 and 3 to 4?
No, it wont do that. You would have to do that programmatically.

:

That is great, but what about when I have to insert an agreement that will
mess the numbers up, will it automatically change all of the numbers in the
new order?
--
S


:

Use the DMax function. First, you need a field in the table to hold the
number. You also need a field to hold the prefix (A, B, etc.). Then to
generate the next number use DMax and add 1:

lngNextNum = Nz(DMax("[SEQ_NUMBER]", "Agreements", "[SEQ_PREFIX] = '" _
& Me.txtPrefix & "'"),0) + 1
Me.txtBinderNumber = Me.txtPrefix & Format(lngNextNum, "0000")

You will, of course, have to change the name to suit you needs.

:

I have a database of Agreements. I have a table for Agreements p/k
Autonumber, CompanyInformation, etc. These documents are going to be filed
in binders by Company Name and numbered, for instance:

1. Abigen
2. Affibird
3. Axxx
4. Azzz

1. Baaa
2. Bddd
3. Beee

I thought I would make a AgreementNumber, i.e., A0001, A0002, B0001, B0002,
etc. (The actual hard copies of the agreements are in a bind with section tab
A, and then number 1, 2, 3, B, 1, 2, 3)

The problem is that when we get new agreements, we will need to change all
of the numbers to correspond with the placement of that agreement in the
binder. That is why we are dividing them into separate letters so we don't
have to move as many documents if we have an agreement to insert.

I don't want to use the Autonumber feature because it will not correspond to
the physical tab.

Does anyone have any suggestions, etc.

Also, when you create a new record, how do you automatically insert the next
number in the field?

Thanks for any assistance.

 

[Douglas J Steele]

You should be able to do it in a single Update statement:

UPDATE MyTable SET MyField = MyField + 1
WHERE MyField >= NewValue

then insert the new row

(I agree, though, that it's not a good thing to do)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I would suggest you don't do it. It really makes no sense to try to keep a
database in sync with a paper filing system. Pardon my bluntness, but only a
bean counter with severe OCD could come up with a scheme like this.

But, I do understand we don't always have control over the situation. So,
one way to do it would be to create a query to use as a recordset that would
contain only the records that need to be changed. Order it in descending
order so the highest number will be the first record returned. Then add 1 to
the number and update the record. Do that until you get to where the new
agreement will be.

Exactly, but I am not sure how to go about it. Any suggestions?
--
S

Are you saying you would have A0001, A0002, A0003 and you would want to make
a new A0002 so you want to renumber 2 to 3 and 3 to 4?
No, it wont do that. You would have to do that programmatically.

:

That is great, but what about when I have to insert an agreement that will
mess the numbers up, will it automatically change all of the numbers in the
new order?
--
S


:

Use the DMax function. First, you need a field in the table to hold the
number. You also need a field to hold the prefix (A, B, etc.). Then to
generate the next number use DMax and add 1:

lngNextNum = Nz(DMax("[SEQ_NUMBER]", "Agreements", "[SEQ_PREFIX] = '" _
& Me.txtPrefix & "'"),0) + 1
Me.txtBinderNumber = Me.txtPrefix & Format(lngNextNum, "0000")

You will, of course, have to change the name to suit you needs.

:

I have a database of Agreements. I have a table for Agreements p/k
Autonumber, CompanyInformation, etc. These documents are going to be filed
in binders by Company Name and numbered, for instance:

1. Abigen
2. Affibird
3. Axxx
4. Azzz

1. Baaa
2. Bddd
3. Beee

I thought I would make a AgreementNumber, i.e., A0001, A0002, B0001, B0002,
etc. (The actual hard copies of the agreements are in a bind with section tab
A, and then number 1, 2, 3, B, 1, 2, 3)

The problem is that when we get new agreements, we will need to change all
of the numbers to correspond with the placement of that agreement in the
binder. That is why we are dividing them into separate letters so we don't
have to move as many documents if we have an agreement to insert.

I don't want to use the Autonumber feature because it will not correspond to
the physical tab.

Does anyone have any suggestions, etc.

Also, when you create a new record, how do you automatically insert the next
number in the field?

Thanks for any assistance.

 

[Guest]

I would agree with the "don't do it" comment, at least as far as how you are
trying to do it now. Being a "Bean Counter" myself, here's an additional
suggestion to using the DMAX function. What if you were to number your
agreements by COMPANY NAME, instead of just generally by alpha? You could
add a field in your database something like "CO ABRV," and when you enter a
new company in the database, give them an abbreviation. Then you could begin
your numbering PER COMPANY.

For example you add "John's Flooring" to your database. Give them the
abbreviation "JFL" or something similar. Then you could connect the
abbreviation with the DMAX feature, and number your agreements PER COMPANY.

Would that work?

Oh, you know my boss? Her last name is fussey.
--
S

I would suggest you don't do it. It really makes no sense to try to keep a
database in sync with a paper filing system. Pardon my bluntness, but only a
bean counter with severe OCD could come up with a scheme like this.

But, I do understand we don't always have control over the situation. So,
one way to do it would be to create a query to use as a recordset that would
contain only the records that need to be changed. Order it in descending
order so the highest number will be the first record returned. Then add 1 to
the number and update the record. Do that until you get to where the new
agreement will be.

Exactly, but I am not sure how to go about it. Any suggestions?
--
S


:

Are you saying you would have A0001, A0002, A0003 and you would want to make
a new A0002 so you want to renumber 2 to 3 and 3 to 4?
No, it wont do that. You would have to do that programmatically.

:

That is great, but what about when I have to insert an agreement that will
mess the numbers up, will it automatically change all of the numbers in the
new order?
--
S


:

Use the DMax function. First, you need a field in the table to hold the
number. You also need a field to hold the prefix (A, B, etc.). Then to
generate the next number use DMax and add 1:

lngNextNum = Nz(DMax("[SEQ_NUMBER]", "Agreements", "[SEQ_PREFIX] = '" _
& Me.txtPrefix & "'"),0) + 1
Me.txtBinderNumber = Me.txtPrefix & Format(lngNextNum, "0000")

You will, of course, have to change the name to suit you needs.

:

I have a database of Agreements. I have a table for Agreements p/k
Autonumber, CompanyInformation, etc. These documents are going to be filed
in binders by Company Name and numbered, for instance:

1. Abigen
2. Affibird
3. Axxx
4. Azzz

1. Baaa
2. Bddd
3. Beee

I thought I would make a AgreementNumber, i.e., A0001, A0002, B0001, B0002,
etc. (The actual hard copies of the agreements are in a bind with section tab
A, and then number 1, 2, 3, B, 1, 2, 3)

The problem is that when we get new agreements, we will need to change all
of the numbers to correspond with the placement of that agreement in the
binder. That is why we are dividing them into separate letters so we don't
have to move as many documents if we have an agreement to insert.

I don't want to use the Autonumber feature because it will not correspond to
the physical tab.

Does anyone have any suggestions, etc.

Also, when you create a new record, how do you automatically insert the next
number in the field?

Thanks for any assistance.

 

[Jeff Boyce]

Scotty

A potential downside to using a "concatenated" approach like you've
described shows up when you have more than one entity that could have the
same abbreviation. Even though you are setting the abbreviation, you have
to review ALL existing abbreviations before giving, say, "Jim's Flowers" an
abbreviation ... ?JFL?!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I would agree with the "don't do it" comment, at least as far as how you
are
trying to do it now. Being a "Bean Counter" myself, here's an additional
suggestion to using the DMAX function. What if you were to number your
agreements by COMPANY NAME, instead of just generally by alpha? You could
add a field in your database something like "CO ABRV," and when you enter
a
new company in the database, give them an abbreviation. Then you could
begin
your numbering PER COMPANY.

For example you add "John's Flooring" to your database. Give them the
abbreviation "JFL" or something similar. Then you could connect the
abbreviation with the DMAX feature, and number your agreements PER
COMPANY.

Would that work?

Oh, you know my boss? Her last name is fussey.
--
S

I would suggest you don't do it. It really makes no sense to try to
keep a
database in sync with a paper filing system. Pardon my bluntness, but
only a
bean counter with severe OCD could come up with a scheme like this.

But, I do understand we don't always have control over the situation.
So,
one way to do it would be to create a query to use as a recordset that
would
contain only the records that need to be changed. Order it in
descending
order so the highest number will be the first record returned. Then
add 1 to
the number and update the record. Do that until you get to where the
new
agreement will be.

:

Exactly, but I am not sure how to go about it. Any suggestions?
--
S


:

Are you saying you would have A0001, A0002, A0003 and you would
want to make
a new A0002 so you want to renumber 2 to 3 and 3 to 4?
No, it wont do that. You would have to do that programmatically.

:

That is great, but what about when I have to insert an agreement
that will
mess the numbers up, will it automatically change all of the
numbers in the
new order?
--
S


:

Use the DMax function. First, you need a field in the table to
hold the
number. You also need a field to hold the prefix (A, B, etc.).
Then to
generate the next number use DMax and add 1:

lngNextNum = Nz(DMax("[SEQ_NUMBER]", "Agreements",
"[SEQ_PREFIX] = '" _
& Me.txtPrefix & "'"),0) + 1
Me.txtBinderNumber = Me.txtPrefix & Format(lngNextNum, "0000")

You will, of course, have to change the name to suit you needs.

:

I have a database of Agreements. I have a table for
Agreements p/k
Autonumber, CompanyInformation, etc. These documents are
going to be filed
in binders by Company Name and numbered, for instance:

1. Abigen
2. Affibird
3. Axxx
4. Azzz

1. Baaa
2. Bddd
3. Beee

I thought I would make a AgreementNumber, i.e., A0001, A0002,
B0001, B0002,
etc. (The actual hard copies of the agreements are in a bind
with section tab
A, and then number 1, 2, 3, B, 1, 2, 3)

The problem is that when we get new agreements, we will need
to change all
of the numbers to correspond with the placement of that
agreement in the
binder. That is why we are dividing them into separate
letters so we don't
have to move as many documents if we have an agreement to
insert.

I don't want to use the Autonumber feature because it will
not correspond to
the physical tab.

Does anyone have any suggestions, etc.

Also, when you create a new record, how do you automatically
insert the next
number in the field?

Thanks for any assistance.

 

[Guest]

I thought about that, too. You could just set the property of that control
to be indexed with no duplicates allowed. That should solve that problem.

Scotty

A potential downside to using a "concatenated" approach like you've
described shows up when you have more than one entity that could have the
same abbreviation. Even though you are setting the abbreviation, you have
to review ALL existing abbreviations before giving, say, "Jim's Flowers" an
abbreviation ... ?JFL?!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I would agree with the "don't do it" comment, at least as far as how you
are
trying to do it now. Being a "Bean Counter" myself, here's an additional
suggestion to using the DMAX function. What if you were to number your
agreements by COMPANY NAME, instead of just generally by alpha? You could
add a field in your database something like "CO ABRV," and when you enter
a
new company in the database, give them an abbreviation. Then you could
begin
your numbering PER COMPANY.

For example you add "John's Flooring" to your database. Give them the
abbreviation "JFL" or something similar. Then you could connect the
abbreviation with the DMAX feature, and number your agreements PER
COMPANY.

Would that work?

Oh, you know my boss? Her last name is fussey.
--
S


:

I would suggest you don't do it. It really makes no sense to try to
keep a
database in sync with a paper filing system. Pardon my bluntness, but
only a
bean counter with severe OCD could come up with a scheme like this.

But, I do understand we don't always have control over the situation.
So,
one way to do it would be to create a query to use as a recordset that
would
contain only the records that need to be changed. Order it in
descending
order so the highest number will be the first record returned. Then
add 1 to
the number and update the record. Do that until you get to where the
new
agreement will be.

:

Exactly, but I am not sure how to go about it. Any suggestions?
--
S


:

Are you saying you would have A0001, A0002, A0003 and you would
want to make
a new A0002 so you want to renumber 2 to 3 and 3 to 4?
No, it wont do that. You would have to do that programmatically.

:

That is great, but what about when I have to insert an agreement
that will
mess the numbers up, will it automatically change all of the
numbers in the
new order?
--
S


:

Use the DMax function. First, you need a field in the table to
hold the
number. You also need a field to hold the prefix (A, B, etc.).
Then to
generate the next number use DMax and add 1:

lngNextNum = Nz(DMax("[SEQ_NUMBER]", "Agreements",
"[SEQ_PREFIX] = '" _
& Me.txtPrefix & "'"),0) + 1
Me.txtBinderNumber = Me.txtPrefix & Format(lngNextNum, "0000")

You will, of course, have to change the name to suit you needs.

:

I have a database of Agreements. I have a table for
Agreements p/k
Autonumber, CompanyInformation, etc. These documents are
going to be filed
in binders by Company Name and numbered, for instance:

1. Abigen
2. Affibird
3. Axxx
4. Azzz

1. Baaa
2. Bddd
3. Beee

I thought I would make a AgreementNumber, i.e., A0001, A0002,
B0001, B0002,
etc. (The actual hard copies of the agreements are in a bind
with section tab
A, and then number 1, 2, 3, B, 1, 2, 3)

The problem is that when we get new agreements, we will need
to change all
of the numbers to correspond with the placement of that
agreement in the
binder. That is why we are dividing them into separate
letters so we don't
have to move as many documents if we have an agreement to
insert.

I don't want to use the Autonumber feature because it will
not correspond to
the physical tab.

Does anyone have any suggestions, etc.

Also, when you create a new record, how do you automatically
insert the next
number in the field?

Thanks for any assistance.

 

[Jeff Boyce]

"Solve" is relative. If I, as a user, get in the habit of thinking about
JFL instead of John's Flooring, finding JF2 for "Jim's..." and JF3 for
"Joanne's..." and JF4 for ... will make it harder on me/user.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I thought about that, too. You could just set the property of that control
to be indexed with no duplicates allowed. That should solve that problem.

Scotty

A potential downside to using a "concatenated" approach like you've
described shows up when you have more than one entity that could have the
same abbreviation. Even though you are setting the abbreviation, you
have
to review ALL existing abbreviations before giving, say, "Jim's Flowers"
an
abbreviation ... ?JFL?!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I would agree with the "don't do it" comment, at least as far as how you
are
trying to do it now. Being a "Bean Counter" myself, here's an
additional
suggestion to using the DMAX function. What if you were to number your
agreements by COMPANY NAME, instead of just generally by alpha? You
could
add a field in your database something like "CO ABRV," and when you
enter
a
new company in the database, give them an abbreviation. Then you could
begin
your numbering PER COMPANY.

For example you add "John's Flooring" to your database. Give them the
abbreviation "JFL" or something similar. Then you could connect the
abbreviation with the DMAX feature, and number your agreements PER
COMPANY.

Would that work?

:

Oh, you know my boss? Her last name is fussey.
--
S


:

I would suggest you don't do it. It really makes no sense to try to
keep a
database in sync with a paper filing system. Pardon my bluntness,
but
only a
bean counter with severe OCD could come up with a scheme like this.

But, I do understand we don't always have control over the
situation.
So,
one way to do it would be to create a query to use as a recordset
that
would
contain only the records that need to be changed. Order it in
descending
order so the highest number will be the first record returned. Then
add 1 to
the number and update the record. Do that until you get to where
the
new
agreement will be.

:

Exactly, but I am not sure how to go about it. Any suggestions?
--
S


:

Are you saying you would have A0001, A0002, A0003 and you would
want to make
a new A0002 so you want to renumber 2 to 3 and 3 to 4?
No, it wont do that. You would have to do that
programmatically.

:

That is great, but what about when I have to insert an
agreement
that will
mess the numbers up, will it automatically change all of the
numbers in the
new order?
--
S


:

Use the DMax function. First, you need a field in the table
to
hold the
number. You also need a field to hold the prefix (A, B,
etc.).
Then to
generate the next number use DMax and add 1:

lngNextNum = Nz(DMax("[SEQ_NUMBER]", "Agreements",
"[SEQ_PREFIX] = '" _
& Me.txtPrefix & "'"),0) + 1
Me.txtBinderNumber = Me.txtPrefix & Format(lngNextNum,
"0000")

You will, of course, have to change the name to suit you
needs.

:

I have a database of Agreements. I have a table for
Agreements p/k
Autonumber, CompanyInformation, etc. These documents are
going to be filed
in binders by Company Name and numbered, for instance:

1. Abigen
2. Affibird
3. Axxx
4. Azzz

1. Baaa
2. Bddd
3. Beee

I thought I would make a AgreementNumber, i.e., A0001,
A0002,
B0001, B0002,
etc. (The actual hard copies of the agreements are in a
bind
with section tab
A, and then number 1, 2, 3, B, 1, 2, 3)

The problem is that when we get new agreements, we will
need
to change all
of the numbers to correspond with the placement of that
agreement in the
binder. That is why we are dividing them into separate
letters so we don't
have to move as many documents if we have an agreement to
insert.

I don't want to use the Autonumber feature because it will
not correspond to
the physical tab.

Does anyone have any suggestions, etc.

Also, when you create a new record, how do you
automatically
insert the next
number in the field?

Thanks for any assistance.