Incrementing numbers by one?

[Dave]

Just trying to write a simple database for the first time. Data to show
student name, type of certificate and certificate number. I want to
start certificate numbers from eg. 1910. then each new student added to
ahve the certificate number automatically incremented by one. So the
last record would show certificate number 1910 the next new one 1911 and
so on. Is this possible and, if so, how do I go about it.
Not sure I have explained myself too well here. Also keep in mind that I
am very new to Access!

Appreciate any help.

Thanks

Dave

 

[Al Campagna]

Dave,
Make the DefaultValue of your CertNos field...
=NZ(DMax("[CertNo]","tblYourTableName")) +1

Your first record would be 1, but override that with 1910, and from then
on each new record will increment by 1.
Then Lock that field on your form so users can't edit it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."

 

[Bob Quintal]

Dave,
Make the DefaultValue of your CertNos field...
=NZ(DMax("[CertNo]","tblYourTableName")) +1

Your first record would be 1, but override that with 1910, and
from then
on each new record will increment by 1.
Then Lock that field on your form so users can't edit it.

=NZ(DMax("[CertNo]","tblYourTableName"),1909) +1
means not having to edit the first record.

 

[Bob Quintal]

Dave,
Make the DefaultValue of your CertNos field...
=NZ(DMax("[CertNo]","tblYourTableName")) +1

Your first record would be 1, but override that with 1910,
and from then
on each new record will increment by 1.
Then Lock that field on your form so users can't edit it.

=NZ(DMax("[CertNo]","tblYourTableName"),1909) +1
means not having to edit the first record.

Thanks Bob.
Did not see your reply until a few minutes ago. Have just asked Al
for a little more details as to where I enter this formula.
Properties of the box, then Format or Validation Rule ??

Regards

Dave

Al was explicit in stating the .DefaultValue property of the CertNos
control (textbox)

 

[Dave]

Dave,
Make the DefaultValue of your CertNos field...
=NZ(DMax("[CertNo]","tblYourTableName")) +1

Your first record would be 1, but override that with 1910, and from then
on each new record will increment by 1.
Then Lock that field on your form so users can't edit it.

Had a problem finding your answer as it disappeared from my NG posting
within seconds of seeing it. (Probably a bug in Thunderbird).
Thank you for taking the time to answer. Unfortunately I am none the
wiser as I am a total newbie at Access.
Do I open the form, select the box where the value is to be auto-entered
and goto the properties section (F4)? If so is the formula you suggested
entered in the Validation Rule field or the Format field?

Would that then look like this: =NZ(DMax("[1910]","Certificates")) +1 ?

Thanks for your help.

Regards

Dave

 

[Dave]

Dave,
Make the DefaultValue of your CertNos field...
=NZ(DMax("[CertNo]","tblYourTableName")) +1

Your first record would be 1, but override that with 1910, and
from then
on each new record will increment by 1.
Then Lock that field on your form so users can't edit it.

=NZ(DMax("[CertNo]","tblYourTableName"),1909) +1
means not having to edit the first record.

Thanks Bob.
Did not see your reply until a few minutes ago. Have just asked Al for a
little more details as to where I enter this formula.
Properties of the box, then Format or Validation Rule ??

Regards

Dave

 

[Rick Brandt]

Dave,
Make the DefaultValue of your CertNos field...
=NZ(DMax("[CertNo]","tblYourTableName")) +1

Your first record would be 1, but override that with 1910, and
from then on each new record will increment by 1.
Then Lock that field on your form so users can't edit it.

Had a problem finding your answer as it disappeared from my NG posting
within seconds of seeing it. (Probably a bug in Thunderbird).
Thank you for taking the time to answer. Unfortunately I am none the
wiser as I am a total newbie at Access.
Do I open the form, select the box where the value is to be
auto-entered and goto the properties section (F4)? If so is the
formula you suggested entered in the Validation Rule field or the
Format field?

Not the Validation Rule. The Default Value (the fourth word in Al's post).

Note that using the Default Value does not work well with multiple users
entering records simutaneously and does not work at all in a continuous or
datasheet form. In those cases it is better to apply the value in code using
the BeforeUpdate event of the form. That event fires just as the record is
being saved.

If Me.NewRecord Then
Me.CertNo = NZ(DMax("[CertNo]","tblYourTableName"),1909) +1
End If

 

[Dave]

Dave,
Make the DefaultValue of your CertNos field...
=NZ(DMax("[CertNo]","tblYourTableName")) +1

Your first record would be 1, but override that with 1910,
and from then
on each new record will increment by 1.
Then Lock that field on your form so users can't edit it.
=NZ(DMax("[CertNo]","tblYourTableName"),1909) +1
means not having to edit the first record.

Thanks Bob.
Did not see your reply until a few minutes ago. Have just asked Al
for a little more details as to where I enter this formula.
Properties of the box, then Format or Validation Rule ??

Regards

Dave

Al was explicit in stating the .DefaultValue property of the CertNos
control (textbox)

My apologies. I was too busy trying to figure out the formula missing
the main point!
Thanks for your help.

Regards

Dave

 

[Dave]

Dave,
Make the DefaultValue of your CertNos field...
=NZ(DMax("[CertNo]","tblYourTableName")) +1

Your first record would be 1, but override that with 1910, and
from then on each new record will increment by 1.
Then Lock that field on your form so users can't edit it.

Had a problem finding your answer as it disappeared from my NG posting
within seconds of seeing it. (Probably a bug in Thunderbird).
Thank you for taking the time to answer. Unfortunately I am none the
wiser as I am a total newbie at Access.
Do I open the form, select the box where the value is to be
auto-entered and goto the properties section (F4)? If so is the
formula you suggested entered in the Validation Rule field or the
Format field?

Not the Validation Rule. The Default Value (the fourth word in Al's post).

Note that using the Default Value does not work well with multiple users
entering records simutaneously and does not work at all in a continuous or
datasheet form. In those cases it is better to apply the value in code using
the BeforeUpdate event of the form. That event fires just as the record is
being saved.

If Me.NewRecord Then
Me.CertNo = NZ(DMax("[CertNo]","tblYourTableName"),1909) +1
End If

Thanks Rick. I have read the post more closeley this time!
Will try both solutions. As I said earlier I am a complete newbie to
Access but am learning fast!

Thanks again and appreciate your time and help.

Regards

Dave