increment form field after record save

Hi,

I am trying to increment a field on a form after a record has been stored.
It is a standard fumeric field which may contain some blocks of consecutive
values in the table but is not meant to be an auto increment field.

I have been trying to store the value of the field in a variable and then
me.field +1 but it is not working - what event should I be using to a) store
current value, b) increment value on new record ?

thanks

Ian

Ian
If you posted your code it would help - but I'll give it a guess as to
what you are doing.
It sounds like you are trying to increment a field every time the
record is saved.
Your solution may be as simple as me.field =me.field +1
The form has an AfterUpdate event you might use.
A word of advice, if you are referencing the dataset and not a control,
you should reference it using a ! e.g. me!field =me!field +1. The Dot
will fail in some situations.
HTH
Pachydermitis

Hi,

Thanks for thie tip. I tried your suggestion:

Private Sub Form_AfterUpdate()
Me!TNum = Me!TNum + 1
End Sub

(where TNum is the name of the Text field with the value in!), but instead
of going to a new record when I click the standard next button the field
increments only - I don't get a blank record + increment field. Also when I
close the form the field increments once again and I get "you can't save
this record at this time. MS access may have encountered an error while
trying to save a record. if you close this object now the data changes you
have made will be lost. do you want to close anyway". If I force the close
then the record is stored but the incremented field is one less than the
form was displaying when I tried to close it.

Ian


<dedejavu@hotmail.com> wrote in message
news:1114620445.392052.120110@f14g2000cwb.googlegr oups.com...[color=blue]
> Ian
> If you posted your code it would help - but I'll give it a guess as to
> what you are doing.
> It sounds like you are trying to increment a field every time the
> record is saved.
> Your solution may be as simple as me.field =me.field +1
> The form has an AfterUpdate event you might use.
> A word of advice, if you are referencing the dataset and not a control,
> you should reference it using a ! e.g. me!field =me!field +1. The Dot
> will fail in some situations.
> HTH
> Pachydermitis
>[/color]

Ian, try using the controls's Default value.

Use the form's before update event. Like...

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.txtRecNum.DefaultValue = Me.txtRecNum + 1
End Sub

Don't worry about the dot instead of the bang. The dot works fine.
But, I would use a naming convention for the user created controls on
the form. See...
http://www.mvps.org/access/general/gen0012.htm

- Jim

On Wed, 27 Apr 2005 17:20:50 GMT, "ISmith" <nobody@nowhere.com> wrote:
[color=blue]
>Hi,
>
>Thanks for thie tip. I tried your suggestion:
>
>Private Sub Form_AfterUpdate()
> Me!TNum = Me!TNum + 1
>End Sub
>
>(where TNum is the name of the Text field with the value in!), but instead
>of going to a new record when I click the standard next button the field
>increments only - I don't get a blank record + increment field. Also when I
>close the form the field increments once again and I get "you can't save
>this record at this time. MS access may have encountered an error while
>trying to save a record. if you close this object now the data changes you
>have made will be lost. do you want to close anyway". If I force the close
>then the record is stored but the incremented field is one less than the
>form was displaying when I tried to close it.
>
>Ian
>
>
><dedejavu@hotmail.com> wrote in message
>news:1114620445.392052.120110@f14g2000cwb.googleg roups.com...[color=green]
>> Ian
>> If you posted your code it would help - but I'll give it a guess as to
>> what you are doing.
>> It sounds like you are trying to increment a field every time the
>> record is saved.
>> Your solution may be as simple as me.field =me.field +1
>> The form has an AfterUpdate event you might use.
>> A word of advice, if you are referencing the dataset and not a control,
>> you should reference it using a ! e.g. me!field =me!field +1. The Dot
>> will fail in some situations.
>> HTH
>> Pachydermitis
>>[/color]
>
>[/color]

Many thanks for this - it works great ! Thanks for the url too.

Ian
"Jim Allensworth" <jimNOT@datacentricsolutions.com> wrote in message
news:4270003d.1035266578@news.west.earthlink.net.. .[color=blue]
> Ian, try using the controls's Default value.
>
> Use the form's before update event. Like...
>
> Private Sub Form_BeforeUpdate(Cancel As Integer)
> Me.txtRecNum.DefaultValue = Me.txtRecNum + 1
> End Sub
>
> Don't worry about the dot instead of the bang. The dot works fine.
> But, I would use a naming convention for the user created controls on
> the form. See...
> http://www.mvps.org/access/general/gen0012.htm
>
> - Jim
>
> On Wed, 27 Apr 2005 17:20:50 GMT, "ISmith" <nobody@nowhere.com> wrote:
>[color=green]
>>Hi,
>>
>>Thanks for thie tip. I tried your suggestion:
>>
>>Private Sub Form_AfterUpdate()
>> Me!TNum = Me!TNum + 1
>>End Sub
>>
>>(where TNum is the name of the Text field with the value in!), but instead
>>of going to a new record when I click the standard next button the field
>>increments only - I don't get a blank record + increment field. Also when
>>I
>>close the form the field increments once again and I get "you can't save
>>this record at this time. MS access may have encountered an error while
>>trying to save a record. if you close this object now the data changes you
>>have made will be lost. do you want to close anyway". If I force the
>>close
>>then the record is stored but the incremented field is one less than the
>>form was displaying when I tried to close it.
>>
>>Ian
>>
>>
>><dedejavu@hotmail.com> wrote in message
>>news:1114620445.392052.120110@f14g2000cwb.google groups.com...[color=darkred]
>>> Ian
>>> If you posted your code it would help - but I'll give it a guess as to
>>> what you are doing.
>>> It sounds like you are trying to increment a field every time the
>>> record is saved.
>>> Your solution may be as simple as me.field =me.field +1
>>> The form has an AfterUpdate event you might use.
>>> A word of advice, if you are referencing the dataset and not a control,
>>> you should reference it using a ! e.g. me!field =me!field +1. The Dot
>>> will fail in some situations.
>>> HTH
>>> Pachydermitis
>>>[/color]
>>
>>[/color]
>[/color]

Jim,
I am guessing you are a conscientious developer or you wouldn't have
directed Ian on his control naming conventions.
The dot works fine "most" of the time. If you build enough databases
on enough systems you will run into situations where it doesn't.
There is a reason "both" dot and bang exist; his use of them and your
advise denote a lack of understanding as to their differences.
Why advise a beginner to start building habits that they will have to
change?
Pachydermitis

dedejavu@hotmail.com wrote:
[color=blue]
> The dot works fine "most" of the time. If you build enough databases
> on enough systems you will run into situations where it doesn't.[/color]

I have built only a few thousand databases (all bangless) and expect to
die sometime before the next century begins. Please, post the code for
or describe clearly these situations so that I can learn about them
before I go.

--
--
Lyle

"The aim of those who try to control thought is always the same. They
find one single explanation of the world, one system of thought and
action that will (they believe) cover everything; and then they try to
impose that on all thinking people."
- Gilbert Highet

On 27 Apr 2005 19:25:24 -0700, dedejavu@hotmail.com wrote:
[color=blue]
>Jim,
>I am guessing you are a conscientious developer or you wouldn't have
>directed Ian on his control naming conventions.
>The dot works fine "most" of the time. If you build enough databases
>on enough systems you will run into situations where it doesn't.
>There is a reason "both" dot and bang exist; his use of them and your
>advise denote a lack of understanding as to their differences.
>Why advise a beginner to start building habits that they will have to
>change?
>Pachydermitis[/color]

This has been debated many times here and many times I have requested
that a reproducable example be given of a scenario whereby the dot
fails but the bang does not. As yet no one has posted such an example.

It is not a bad habit, it is a non issue.

As others have mentioned, if you *know* of a way that the problems can
be demonstrated, please post them.

Here is an interesting article on the subject by Andy Baron.
http://www.advisor.com/Articles.nsf/aid/BAROA06

If you want to know what others including myself think of them then
just do a google groups search on dot bang allensworth

- Jim

On 27 Apr 2005 19:25:24 -0700, dedejavu@hotmail.com wrote:
[color=blue]
>Jim,
>I am guessing you are a conscientious developer or you wouldn't have
>directed Ian on his control naming conventions.
>The dot works fine "most" of the time. If you build enough databases
>on enough systems you will run into situations where it doesn't.
>There is a reason "both" dot and bang exist; his use of them and your
>advise denote a lack of understanding as to their differences.
>Why advise a beginner to start building habits that they will have to
>change?
>Pachydermitis
>[/color]