Incrementing a field without autonumber?

[Will]

I have a form with a number field.

What I would like to have happen is that when a new record is created,
the value of the number field is set by taking the value of the field from
the previous record and incrementing it by one.

There is existing data in this field so I can't (as far as I know) use an
autonumber field or change the field to one (or can I?)

Any ideas how best to achieve this?

Regards,
Will

 

[Guest]

You can, in a way, but if the existing data in meaningful, you will loose it.
To do this, you would need to delete the existing field and then add the
field back and make in an autonumber field.

If you need to maintain the existing data, but make in auto imcrement, you
can use the DMax function to add one to it.

lngNextNumber = Nz(DMax("[TheFieldName], "TheTableName"),0) + 1

 

[Will]

Thanks Dave;

Where should I enter this? Tentatively I've added it to the 'New Record'
button. However, upon entering in the code, I receive an error "Expected:
list seperator or )"

Ideas?
Will

You can, in a way, but if the existing data in meaningful, you will loose
it.
To do this, you would need to delete the existing field and then add the
field back and make in an autonumber field.

If you need to maintain the existing data, but make in auto imcrement, you
can use the DMax function to add one to it.

lngNextNumber = Nz(DMax("[TheFieldName], "TheTableName"),0) + 1


--
Dave Hargis, Microsoft Access MVP

I have a form with a number field.

What I would like to have happen is that when a new record is created,
the value of the number field is set by taking the value of the field
from
the previous record and incrementing it by one.

There is existing data in this field so I can't (as far as I know) use an
autonumber field or change the field to one (or can I?)

Any ideas how best to achieve this?

Regards,
Will

 

[Guest]

The error was because of a syntax error in my code. Should be:

lngNextNumber = Nz(DMax("[TheFieldName]", "TheTableName"),0) + 1

(missing quote after [TheFieldName])

There a a few ways you could do this. If you have a bound control on the
form for the field, you may want to put it in the Default Value property.

--
Dave Hargis, Microsoft Access MVP

Thanks Dave;

Where should I enter this? Tentatively I've added it to the 'New Record'
button. However, upon entering in the code, I receive an error "Expected:
list seperator or )"

Ideas?
Will

You can, in a way, but if the existing data in meaningful, you will loose
it.
To do this, you would need to delete the existing field and then add the
field back and make in an autonumber field.

If you need to maintain the existing data, but make in auto imcrement, you
can use the DMax function to add one to it.

lngNextNumber = Nz(DMax("[TheFieldName], "TheTableName"),0) + 1


--
Dave Hargis, Microsoft Access MVP

I have a form with a number field.

What I would like to have happen is that when a new record is created,
the value of the number field is set by taking the value of the field
from
the previous record and incrementing it by one.

There is existing data in this field so I can't (as far as I know) use an
autonumber field or change the field to one (or can I?)

Any ideas how best to achieve this?

Regards,
Will

 

[Will]

Well, this didn't error out, but neither is it producing any results. I've
tried both setting it as a default value on the field on the form itself and
tying it to a button on the form as well.

Will

The error was because of a syntax error in my code. Should be:

lngNextNumber = Nz(DMax("[TheFieldName]", "TheTableName"),0) + 1

(missing quote after [TheFieldName])

There a a few ways you could do this. If you have a bound control on the
form for the field, you may want to put it in the Default Value property.

--
Dave Hargis, Microsoft Access MVP

Thanks Dave;

Where should I enter this? Tentatively I've added it to the 'New Record'
button. However, upon entering in the code, I receive an error
"Expected:
list seperator or )"

Ideas?
Will

You can, in a way, but if the existing data in meaningful, you will
loose
it.
To do this, you would need to delete the existing field and then add
the
field back and make in an autonumber field.

If you need to maintain the existing data, but make in auto imcrement,
you
can use the DMax function to add one to it.

lngNextNumber = Nz(DMax("[TheFieldName], "TheTableName"),0) + 1


--
Dave Hargis, Microsoft Access MVP


:

I have a form with a number field.

What I would like to have happen is that when a new record is created,
the value of the number field is set by taking the value of the field
from
the previous record and incrementing it by one.

There is existing data in this field so I can't (as far as I know) use
an
autonumber field or change the field to one (or can I?)

Any ideas how best to achieve this?

Regards,
Will

 

[Guest]

Have you run the code in debug mode to see what, exactly, is happening?
--
Dave Hargis, Microsoft Access MVP

Well, this didn't error out, but neither is it producing any results. I've
tried both setting it as a default value on the field on the form itself and
tying it to a button on the form as well.

Will

The error was because of a syntax error in my code. Should be:

lngNextNumber = Nz(DMax("[TheFieldName]", "TheTableName"),0) + 1

(missing quote after [TheFieldName])

There a a few ways you could do this. If you have a bound control on the
form for the field, you may want to put it in the Default Value property.

--
Dave Hargis, Microsoft Access MVP

Thanks Dave;

Where should I enter this? Tentatively I've added it to the 'New Record'
button. However, upon entering in the code, I receive an error
"Expected:
list seperator or )"

Ideas?
Will

You can, in a way, but if the existing data in meaningful, you will
loose
it.
To do this, you would need to delete the existing field and then add
the
field back and make in an autonumber field.

If you need to maintain the existing data, but make in auto imcrement,
you
can use the DMax function to add one to it.

lngNextNumber = Nz(DMax("[TheFieldName], "TheTableName"),0) + 1


--
Dave Hargis, Microsoft Access MVP


:

I have a form with a number field.

What I would like to have happen is that when a new record is created,
the value of the number field is set by taking the value of the field
from
the previous record and incrementing it by one.

There is existing data in this field so I can't (as far as I know) use
an
autonumber field or change the field to one (or can I?)

Any ideas how best to achieve this?

Regards,
Will