Auto Number

[Robert Smith]

Hello, I'm new to access and trying to learn by reading and trying...

I would like to use a primary key ID in a table that would be the current
year - 0001 and then auto count up for each record.

2008-0001
2008-0002

Then 1/1/2009 start

2009-0001
2009-0002

and so on.

If this is a stupid idea I would like to do this in another field in the
table.

I'm also looking for a way to Close a record so it can't be changed without
first reopening the record.

Thanks,
Robert

[email protected]

 

[BruceM]

That wouldn't be autonumber as the term is used in Access. Autonumber is an
automatically generated number that is almost certain to be unique, but it
is for use by Access, and in most cases will not be seen by the user. Also,
it cannot be guaranteed not to leave gaps in the numbering sequence. If it
is possible to avoid gaps it is very, very difficult.

However, what you want can be done with an expression. First, though, there
is no need to store the year if there is a date field in the record. I will
call that field DateField, and the incrementing number NumInc. For purposes
of this reply, they are both in a table named tblMain, which is the Record
Source for a form.

On the form, bind a text box to the NumInc field (that is, select NumInc as
its Control Source). This is done on the text box property sheet. To see
the Property Sheet, open the form in design view, right click on the text
box, and select Properties. This will open a box with five tabs (if it is
not already open): Format, Data, Event, Other, and All. Click the Data tab
to find the place to set the Control Source. You may already know about the
Property Sheet, but you identify yourself as a beginner, so there it is just
in case. Also on the Data tab is Default Value. This applies only to new
records. In the Default Value row add the expression:
=Nz(DMax("NumInc","tblMain","Year([DateField]) = " & Year(Date())),0) + 1

You can hide this text box. In another text box, set the Control Source to:
=Year([DateField]) & "-" & Format([NumInc],"0000")

Look up the functions Nz, DMax, Year, Date, and Format in Help to learn more
about what is going on here. In general, the first expression is telling
Access to find the largest value in the NumInc field in a record in which
the year in DateField is the same as the current year, and to add one to
that number. Nz is for the first record of the year. There is no maximum
value, so the DMax result is null. Instead of Null, Access uses a 0 as the
value, and adds 1 to it.

The second expression combines the Year from DateField, a hyphen, and the
number formatted with leading zeros as needed for numbers less than 1000.

 

[Ken Sheridan]

You'll need to set the DefaultValue property in code. If you set it in the
properties sheet its value will remain static until the form is closed and
reopened again. Set it in the form's Current event procedure with:

Me.NumInc.DefaultValue = _
"""" & Nz(DMax("[NumInc]", "[tblMain]", _
"Year([DateField]) = " & Year(Date)), 0) + 1 & """"

Note that the DefaultValue property is a string expression regardless of the
data type of the field in question so should be wrapped in quotes characters
as above. In fact in this case it wouldn't make any difference if the quotes
characters were omitted, but in other circumstances they are crucial,
particularly with a date/time data type.

BTW the control Robert meant you could hide was the NumInc control, not the
unbound control which concatenates the current year value with the formatted
NumInc value.

Another point is that you won't be able to use the DateField and NumInc
fields as the primary key as this would not prevent duplicate NumInc values
within one year. You would need to create another field, CurrentYear say,
with a DefaultValue property of Year(Date()) and use this and NumInc as the
composite primary key. In which case the above code could be changed to:

Me.NumInc.DefaultValue = _
"""" & Nz(DMax("[NumInc]", "[tblMain]", _
"[CurrentYear] = " & Year(Date)), 0) + 1 & """"

The CurrentYear field need not be shown on the form of course.

Ken Sheridan
Stafford, England

Thanks for the information. I tried it exactly like you described and even
tried it with a blank database using your exact names but I'm even greener
than green and didn't get it to work.

The box you said I could hide says 2008-39448 Whenever I press Add Record
button I get #Error in both boxes.

I'm trying to learn Access but it doesn't seem to be willing...

That wouldn't be autonumber as the term is used in Access. Autonumber is
an automatically generated number that is almost certain to be unique, but
it is for use by Access, and in most cases will not be seen by the user.
Also, it cannot be guaranteed not to leave gaps in the numbering sequence.
If it is possible to avoid gaps it is very, very difficult.

However, what you want can be done with an expression. First, though,
there is no need to store the year if there is a date field in the record.
I will call that field DateField, and the incrementing number NumInc. For
purposes of this reply, they are both in a table named tblMain, which is
the Record Source for a form.

On the form, bind a text box to the NumInc field (that is, select NumInc
as its Control Source). This is done on the text box property sheet. To
see the Property Sheet, open the form in design view, right click on the
text box, and select Properties. This will open a box with five tabs (if
it is not already open): Format, Data, Event, Other, and All. Click the
Data tab to find the place to set the Control Source. You may already
know about the Property Sheet, but you identify yourself as a beginner, so
there it is just in case. Also on the Data tab is Default Value. This
applies only to new records. In the Default Value row add the expression:
=Nz(DMax("NumInc","tblMain","Year([DateField]) = " & Year(Date())),0) + 1

You can hide this text box. In another text box, set the Control Source
to:
=Year([DateField]) & "-" & Format([NumInc],"0000")

Look up the functions Nz, DMax, Year, Date, and Format in Help to learn
more about what is going on here. In general, the first expression is
telling Access to find the largest value in the NumInc field in a record
in which the year in DateField is the same as the current year, and to add
one to that number. Nz is for the first record of the year. There is no
maximum value, so the DMax result is null. Instead of Null, Access uses a
0 as the value, and adds 1 to it.

The second expression combines the Year from DateField, a hyphen, and the
number formatted with leading zeros as needed for numbers less than 1000.

Hello, I'm new to access and trying to learn by reading and trying...

I would like to use a primary key ID in a table that would be the current
year - 0001 and then auto count up for each record.

2008-0001
2008-0002

Then 1/1/2009 start

2009-0001
2009-0002

and so on.

If this is a stupid idea I would like to do this in another field in the
table.

I'm also looking for a way to Close a record so it can't be changed
without first reopening the record.

Thanks,
Robert

[email protected]

 

[BruceM]

For some reason I don't see Robert's reply in my newsreader. Your response
to my comments (including Robert's reply, is the first I have seen).
I'm not sure what you're saying about the Default Value property on the
property sheet. If you're saying the Default Value property remains the
same for all records until the form is closed and reopened, that has not
been my experience.

You'll need to set the DefaultValue property in code. If you set it in
the
properties sheet its value will remain static until the form is closed and
reopened again. Set it in the form's Current event procedure with:

Me.NumInc.DefaultValue = _
"""" & Nz(DMax("[NumInc]", "[tblMain]", _
"Year([DateField]) = " & Year(Date)), 0) + 1 & """"

Note that the DefaultValue property is a string expression regardless of
the
data type of the field in question so should be wrapped in quotes
characters
as above. In fact in this case it wouldn't make any difference if the
quotes
characters were omitted, but in other circumstances they are crucial,
particularly with a date/time data type.

BTW the control Robert meant you could hide was the NumInc control, not
the
unbound control which concatenates the current year value with the
formatted
NumInc value.

Another point is that you won't be able to use the DateField and NumInc
fields as the primary key as this would not prevent duplicate NumInc
values
within one year. You would need to create another field, CurrentYear say,
with a DefaultValue property of Year(Date()) and use this and NumInc as
the
composite primary key. In which case the above code could be changed to:

Me.NumInc.DefaultValue = _
"""" & Nz(DMax("[NumInc]", "[tblMain]", _
"[CurrentYear] = " & Year(Date)), 0) + 1 & """"

The CurrentYear field need not be shown on the form of course.

Ken Sheridan
Stafford, England

Thanks for the information. I tried it exactly like you described and
even
tried it with a blank database using your exact names but I'm even
greener
than green and didn't get it to work.

The box you said I could hide says 2008-39448 Whenever I press Add Record
button I get #Error in both boxes.

I'm trying to learn Access but it doesn't seem to be willing...

That wouldn't be autonumber as the term is used in Access. Autonumber
is
an automatically generated number that is almost certain to be unique,
but
it is for use by Access, and in most cases will not be seen by the
user.
Also, it cannot be guaranteed not to leave gaps in the numbering
sequence.
If it is possible to avoid gaps it is very, very difficult.

However, what you want can be done with an expression. First, though,
there is no need to store the year if there is a date field in the
record.
I will call that field DateField, and the incrementing number NumInc.
For
purposes of this reply, they are both in a table named tblMain, which
is
the Record Source for a form.

On the form, bind a text box to the NumInc field (that is, select
NumInc
as its Control Source). This is done on the text box property sheet.
To
see the Property Sheet, open the form in design view, right click on
the
text box, and select Properties. This will open a box with five tabs
(if
it is not already open): Format, Data, Event, Other, and All. Click
the
Data tab to find the place to set the Control Source. You may already
know about the Property Sheet, but you identify yourself as a beginner,
so
there it is just in case. Also on the Data tab is Default Value. This
applies only to new records. In the Default Value row add the
expression:
=Nz(DMax("NumInc","tblMain","Year([DateField]) = " & Year(Date())),0) +
1

You can hide this text box. In another text box, set the Control
Source
to:
=Year([DateField]) & "-" & Format([NumInc],"0000")

Look up the functions Nz, DMax, Year, Date, and Format in Help to learn
more about what is going on here. In general, the first expression is
telling Access to find the largest value in the NumInc field in a
record
in which the year in DateField is the same as the current year, and to
add
one to that number. Nz is for the first record of the year. There is
no
maximum value, so the DMax result is null. Instead of Null, Access
uses a
0 as the value, and adds 1 to it.

The second expression combines the Year from DateField, a hyphen, and
the
number formatted with leading zeros as needed for numbers less than
1000.

Hello, I'm new to access and trying to learn by reading and trying...

I would like to use a primary key ID in a table that would be the
current
year - 0001 and then auto count up for each record.

2008-0001
2008-0002

Then 1/1/2009 start

2009-0001
2009-0002

and so on.

If this is a stupid idea I would like to do this in another field in
the
table.

I'm also looking for a way to Close a record so it can't be changed
without first reopening the record.

Thanks,
Robert

[email protected]

 

[Ken Sheridan]

Sorry, I got the two of you mixed up; I meant 'Bruce's reply'.

As regards the DefaultValue property, for simplicity I was unduly brief in
my explanation as it’s a little more complex in fact. The behaviour if you
set it to an expression of the type you posted in the properties sheet is in
my experience as follows:

1. If a series of new records are inserted in the form it updates the value
correctly for each new record.

2. If a record prior to the latest one is deleted then this obviously
leaves a gap in the sequence, correctly so for most purposes (it is of course
possible to 'fill the gaps' with a more complex solution if that should be
required).

3. However, if a new row is inserted, then deleted in the same session of
the form before a further record is inserted then the DefaultValue property
remains static so the next number is 2 after the last number in the table
rather than 1 after. Now, its arguable that this is correct behaviour as
there is no difference in principle between deleting the latest record and a
prior one in the sequence. However, in practice that would for most purposes
be regarded as incorrect behaviour, as most people would want the number
following the latest row existing in the table as of now to be inserted,
rather than the number following the latest record which existed before its
deletion. It might be thought that its unlikely that this situation would
arise, but Murphy's Law states otherwise!

So neither solutions are 'incorrect', but setting the value in code seems to
me to safely answer the more usual requirements for this sort of thing.

The above is really a question of the 'mechanics' of the application,
however, and not overly significant. What's more important is the need to
control the uniqueness of the year/incrementing number value combination in
the table definition, which does require the current year value to be
inserted at a column position in each row. This does mean that the table is
not properly normalized of course as the DateField column is functionally
dependent on only one part of the key, not 'the key, the whole key and
nothing but the key, so help me Codd'. But I can't see any way to avoid that
(though I'm more than happy to be corrected if otherwise).

Another possible solution of course is not to have a current year or
incrementing number column at all, but to compute the serial numbers within
each year on the fly from the date column. I suspect this would be
inappropriate, however, as the numbers would vary as rows were deleted from
the table or if a row were inserted out of date sequence, so as part of a
fixed identifier would be useless.

Ken Sheridan
Stafford, England

For some reason I don't see Robert's reply in my newsreader. Your response
to my comments (including Robert's reply, is the first I have seen).
I'm not sure what you're saying about the Default Value property on the
property sheet. If you're saying the Default Value property remains the
same for all records until the form is closed and reopened, that has not
been my experience.

You'll need to set the DefaultValue property in code. If you set it in
the
properties sheet its value will remain static until the form is closed and
reopened again. Set it in the form's Current event procedure with:

Me.NumInc.DefaultValue = _
"""" & Nz(DMax("[NumInc]", "[tblMain]", _
"Year([DateField]) = " & Year(Date)), 0) + 1 & """"

Note that the DefaultValue property is a string expression regardless of
the
data type of the field in question so should be wrapped in quotes
characters
as above. In fact in this case it wouldn't make any difference if the
quotes
characters were omitted, but in other circumstances they are crucial,
particularly with a date/time data type.

BTW the control Robert meant you could hide was the NumInc control, not
the
unbound control which concatenates the current year value with the
formatted
NumInc value.

Another point is that you won't be able to use the DateField and NumInc
fields as the primary key as this would not prevent duplicate NumInc
values
within one year. You would need to create another field, CurrentYear say,
with a DefaultValue property of Year(Date()) and use this and NumInc as
the
composite primary key. In which case the above code could be changed to:

Me.NumInc.DefaultValue = _
"""" & Nz(DMax("[NumInc]", "[tblMain]", _
"[CurrentYear] = " & Year(Date)), 0) + 1 & """"

The CurrentYear field need not be shown on the form of course.

Ken Sheridan
Stafford, England

Thanks for the information. I tried it exactly like you described and
even
tried it with a blank database using your exact names but I'm even
greener
than green and didn't get it to work.

The box you said I could hide says 2008-39448 Whenever I press Add Record
button I get #Error in both boxes.

I'm trying to learn Access but it doesn't seem to be willing...



That wouldn't be autonumber as the term is used in Access. Autonumber
is
an automatically generated number that is almost certain to be unique,
but
it is for use by Access, and in most cases will not be seen by the
user.
Also, it cannot be guaranteed not to leave gaps in the numbering
sequence.
If it is possible to avoid gaps it is very, very difficult.

However, what you want can be done with an expression. First, though,
there is no need to store the year if there is a date field in the
record.
I will call that field DateField, and the incrementing number NumInc.
For
purposes of this reply, they are both in a table named tblMain, which
is
the Record Source for a form.

On the form, bind a text box to the NumInc field (that is, select
NumInc
as its Control Source). This is done on the text box property sheet.
To
see the Property Sheet, open the form in design view, right click on
the
text box, and select Properties. This will open a box with five tabs
(if
it is not already open): Format, Data, Event, Other, and All. Click
the
Data tab to find the place to set the Control Source. You may already
know about the Property Sheet, but you identify yourself as a beginner,
so
there it is just in case. Also on the Data tab is Default Value. This
applies only to new records. In the Default Value row add the
expression:
=Nz(DMax("NumInc","tblMain","Year([DateField]) = " & Year(Date())),0) +
1

You can hide this text box. In another text box, set the Control
Source
to:
=Year([DateField]) & "-" & Format([NumInc],"0000")

Look up the functions Nz, DMax, Year, Date, and Format in Help to learn
more about what is going on here. In general, the first expression is
telling Access to find the largest value in the NumInc field in a
record
in which the year in DateField is the same as the current year, and to
add
one to that number. Nz is for the first record of the year. There is
no
maximum value, so the DMax result is null. Instead of Null, Access
uses a
0 as the value, and adds 1 to it.

The second expression combines the Year from DateField, a hyphen, and
the
number formatted with leading zeros as needed for numbers less than
1000.

Hello, I'm new to access and trying to learn by reading and trying...

I would like to use a primary key ID in a table that would be the
current
year - 0001 and then auto count up for each record.

2008-0001
2008-0002

Then 1/1/2009 start

2009-0001
2009-0002

and so on.

If this is a stupid idea I would like to do this in another field in
the
table.

I'm also looking for a way to Close a record so it can't be changed
without first reopening the record.

Thanks,
Robert

[email protected]

 

[BruceM]

Thanks for the explanation. I did not know that about inserting and
deleting a record immediately, causing the value to skip as you have
described. Perhaps this is because I tend to use code rather than the
Default Value of a text box. Actually, I tend to use:
If Me.NewRecord Then etc.
I learned it that way a while ago, and often other things happen for a new
record (controls are hidden or disabled or whatever), so there isn't any
extra coding (that is, DefaultValue uses one line to accomplish what If
Me.NewRecord needs several lines to do, unless there is already is "If
Me.NewRecord"). I have since learned that using "If Me.NewRecord" causes
the Before Insert event to run before the user has a chance to type
anything, and the Dirty event does not run at all, while when using the
DefaultValue property the Before Insert event runs when typing begins , and
the Dirty event runs too (I think that's how it went). I'm not sure what
else happens, but it does tell me that one approach or the other may be
needed in specific situations.
When people ask about incrementing numbers I usually point them to Roger
Carlson's sample database on the topic, which used the Default Value
property of a text box. If more complex incrementing is needed I still tend
to use the text box Default Value when possible because it is simpler to
explain than using VBA, and until now I thought it worked exactly the same
way.

Sorry, I got the two of you mixed up; I meant 'Bruce's reply'.

As regards the DefaultValue property, for simplicity I was unduly brief in
my explanation as it’s a little more complex in fact. The behaviour if
you
set it to an expression of the type you posted in the properties sheet is
in
my experience as follows:

1. If a series of new records are inserted in the form it updates the
value
correctly for each new record.

2. If a record prior to the latest one is deleted then this obviously
leaves a gap in the sequence, correctly so for most purposes (it is of
course
possible to 'fill the gaps' with a more complex solution if that should be
required).

3. However, if a new row is inserted, then deleted in the same session of
the form before a further record is inserted then the DefaultValue
property
remains static so the next number is 2 after the last number in the table
rather than 1 after. Now, its arguable that this is correct behaviour as
there is no difference in principle between deleting the latest record and
a
prior one in the sequence. However, in practice that would for most
purposes
be regarded as incorrect behaviour, as most people would want the number
following the latest row existing in the table as of now to be inserted,
rather than the number following the latest record which existed before
its
deletion. It might be thought that its unlikely that this situation would
arise, but Murphy's Law states otherwise!

So neither solutions are 'incorrect', but setting the value in code seems
to
me to safely answer the more usual requirements for this sort of thing.

The above is really a question of the 'mechanics' of the application,
however, and not overly significant. What's more important is the need to
control the uniqueness of the year/incrementing number value combination
in
the table definition, which does require the current year value to be
inserted at a column position in each row. This does mean that the table
is
not properly normalized of course as the DateField column is functionally
dependent on only one part of the key, not 'the key, the whole key and
nothing but the key, so help me Codd'. But I can't see any way to avoid
that
(though I'm more than happy to be corrected if otherwise).

Another possible solution of course is not to have a current year or
incrementing number column at all, but to compute the serial numbers
within
each year on the fly from the date column. I suspect this would be
inappropriate, however, as the numbers would vary as rows were deleted
from
the table or if a row were inserted out of date sequence, so as part of a
fixed identifier would be useless.

Ken Sheridan
Stafford, England

For some reason I don't see Robert's reply in my newsreader. Your
response
to my comments (including Robert's reply, is the first I have seen).
I'm not sure what you're saying about the Default Value property on the
property sheet. If you're saying the Default Value property remains the
same for all records until the form is closed and reopened, that has not
been my experience.

You'll need to set the DefaultValue property in code. If you set it in
the
properties sheet its value will remain static until the form is closed
and
reopened again. Set it in the form's Current event procedure with:

Me.NumInc.DefaultValue = _
"""" & Nz(DMax("[NumInc]", "[tblMain]", _
"Year([DateField]) = " & Year(Date)), 0) + 1 & """"

Note that the DefaultValue property is a string expression regardless
of
the
data type of the field in question so should be wrapped in quotes
characters
as above. In fact in this case it wouldn't make any difference if the
quotes
characters were omitted, but in other circumstances they are crucial,
particularly with a date/time data type.

BTW the control Robert meant you could hide was the NumInc control, not
the
unbound control which concatenates the current year value with the
formatted
NumInc value.

Another point is that you won't be able to use the DateField and NumInc
fields as the primary key as this would not prevent duplicate NumInc
values
within one year. You would need to create another field, CurrentYear
say,
with a DefaultValue property of Year(Date()) and use this and NumInc as
the
composite primary key. In which case the above code could be changed
to:

Me.NumInc.DefaultValue = _
"""" & Nz(DMax("[NumInc]", "[tblMain]", _
"[CurrentYear] = " & Year(Date)), 0) + 1 & """"

The CurrentYear field need not be shown on the form of course.

Ken Sheridan
Stafford, England

:

Thanks for the information. I tried it exactly like you described and
even
tried it with a blank database using your exact names but I'm even
greener
than green and didn't get it to work.

The box you said I could hide says 2008-39448 Whenever I press Add
Record
button I get #Error in both boxes.

I'm trying to learn Access but it doesn't seem to be willing...



That wouldn't be autonumber as the term is used in Access.
Autonumber
is
an automatically generated number that is almost certain to be
unique,
but
it is for use by Access, and in most cases will not be seen by the
user.
Also, it cannot be guaranteed not to leave gaps in the numbering
sequence.
If it is possible to avoid gaps it is very, very difficult.

However, what you want can be done with an expression. First,
though,
there is no need to store the year if there is a date field in the
record.
I will call that field DateField, and the incrementing number
NumInc.
For
purposes of this reply, they are both in a table named tblMain,
which
is
the Record Source for a form.

On the form, bind a text box to the NumInc field (that is, select
NumInc
as its Control Source). This is done on the text box property
sheet.
To
see the Property Sheet, open the form in design view, right click on
the
text box, and select Properties. This will open a box with five
tabs
(if
it is not already open): Format, Data, Event, Other, and All.
Click
the
Data tab to find the place to set the Control Source. You may
already
know about the Property Sheet, but you identify yourself as a
beginner,
so
there it is just in case. Also on the Data tab is Default Value.
This
applies only to new records. In the Default Value row add the
expression:
=Nz(DMax("NumInc","tblMain","Year([DateField]) = " &
Year(Date())),0) +
1

You can hide this text box. In another text box, set the Control
Source
to:
=Year([DateField]) & "-" & Format([NumInc],"0000")

Look up the functions Nz, DMax, Year, Date, and Format in Help to
learn
more about what is going on here. In general, the first expression
is
telling Access to find the largest value in the NumInc field in a
record
in which the year in DateField is the same as the current year, and
to
add
one to that number. Nz is for the first record of the year. There
is
no
maximum value, so the DMax result is null. Instead of Null, Access
uses a
0 as the value, and adds 1 to it.

The second expression combines the Year from DateField, a hyphen,
and
the
number formatted with leading zeros as needed for numbers less than
1000.

Hello, I'm new to access and trying to learn by reading and
trying...

I would like to use a primary key ID in a table that would be the
current
year - 0001 and then auto count up for each record.

2008-0001
2008-0002

Then 1/1/2009 start

2009-0001
2009-0002

and so on.

If this is a stupid idea I would like to do this in another field
in
the
table.

I'm also looking for a way to Close a record so it can't be changed
without first reopening the record.

Thanks,
Robert

[email protected]

 

[Ken Sheridan]

Robert:

I wouldn't throw in the towel just yet. Are you by any chance entering the
code directly in the properties sheet rather than in the event procedure? If
so, this is how to do it:

Select the form object by clicking on the small black square in its top left
corner in form design view and open its properties sheet if its not already
open. Then select the On Current event property in the properties sheet.
Click on the 'build' button; that's the one on the right with 3 dots. Select
'Code Builder' in the dialogue, and click OK. The VBA window will open at
the event procedure with the first and last lines already in place. Enter
the lines of code between these two existing lines. Make sure that the table
and field names in the code exactly match their actual names.

BTW 'Me' is just a shorthand way of referring to the current form. Strictly
speaking it refers to the instance of the class in which the code is running,
but don't worry about that; just think of it as referring to the form.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

I'd completely agree that its far better to use the DefaultValue property in
situations like this rather than setting the Value of a control as the former
doesn't Dirty the form, so the user can back out without having to Undo the
insert operation. Similarly when passing a value from one form to another
via the OpenArgs property, e.g. when opening a separate form to insert a row
into a table which references the first form's underlying table, its best to
use this to set the DefaultValue property of the control bound to the foreign
key in the second form rather than its Value as it again allows the user to
bail out gracefully if necessary.

Another circumstance when the DefaultValue property is more appropriate is
when inserting a new row into a referenced table via the NotInList event
procedure of a combo box where its necessary to open a form to insert values
into other columns besides that into which the NewData value is to be
inserted. Here's an example for adding a new city which opens a form and
passes the NewData value to it:

Private Sub cboCities_NotInList(NewData As String, Response As Integer)

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmCities", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmCities closed
DoCmd.Close acForm, "frmCities"
' ensure city has been added
If Not IsNull(DLookup("CityID", "Cities", "City = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Cities table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub

The frmCities form's Open event procedure then uses the OpenArgs property to
set the City control's DefaultValue property with:

Private Sub Form_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then
Me.City.DefaultValue = """" & Me.OpenArgs & """"
End If

End Sub

Consequently the user can just close the form if they decide not to add the
city, or they can proceed to add other required data such as the County or
State in which the city is located. As you'll have noticed the NotInList
event procedure's code checks to see if the new city has been added before
adding it to the combo box's list and updating the control.

Getting back to the question of deleting records in a form this does require
a bit of care as if the normal user interface is used the record disappears
from the form immediately, but is of course not finally deleted until the
deletion is confirmed. I've always felt this to be a little confusing for
users as they see the record disappear, which suggests its been deleted, but
are then asked to confirm its deletion apparently after the event. Its not
difficult to create a custom confirmation routine with some code in the
Delete and BeforeDelConfirm event procedures, however.

Ken Sheridan
Stafford, England

Thanks for the explanation. I did not know that about inserting and
deleting a record immediately, causing the value to skip as you have
described. Perhaps this is because I tend to use code rather than the
Default Value of a text box. Actually, I tend to use:
If Me.NewRecord Then etc.
I learned it that way a while ago, and often other things happen for a new
record (controls are hidden or disabled or whatever), so there isn't any
extra coding (that is, DefaultValue uses one line to accomplish what If
Me.NewRecord needs several lines to do, unless there is already is "If
Me.NewRecord"). I have since learned that using "If Me.NewRecord" causes
the Before Insert event to run before the user has a chance to type
anything, and the Dirty event does not run at all, while when using the
DefaultValue property the Before Insert event runs when typing begins , and
the Dirty event runs too (I think that's how it went). I'm not sure what
else happens, but it does tell me that one approach or the other may be
needed in specific situations.
When people ask about incrementing numbers I usually point them to Roger
Carlson's sample database on the topic, which used the Default Value
property of a text box. If more complex incrementing is needed I still tend
to use the text box Default Value when possible because it is simpler to
explain than using VBA, and until now I thought it worked exactly the same
way.

Sorry, I got the two of you mixed up; I meant 'Bruce's reply'.

As regards the DefaultValue property, for simplicity I was unduly brief in
my explanation as it’s a little more complex in fact. The behaviour if
you
set it to an expression of the type you posted in the properties sheet is
in
my experience as follows:

1. If a series of new records are inserted in the form it updates the
value
correctly for each new record.

2. If a record prior to the latest one is deleted then this obviously
leaves a gap in the sequence, correctly so for most purposes (it is of
course
possible to 'fill the gaps' with a more complex solution if that should be
required).

3. However, if a new row is inserted, then deleted in the same session of
the form before a further record is inserted then the DefaultValue
property
remains static so the next number is 2 after the last number in the table
rather than 1 after. Now, its arguable that this is correct behaviour as
there is no difference in principle between deleting the latest record and
a
prior one in the sequence. However, in practice that would for most
purposes
be regarded as incorrect behaviour, as most people would want the number
following the latest row existing in the table as of now to be inserted,
rather than the number following the latest record which existed before
its
deletion. It might be thought that its unlikely that this situation would
arise, but Murphy's Law states otherwise!

So neither solutions are 'incorrect', but setting the value in code seems
to
me to safely answer the more usual requirements for this sort of thing.

The above is really a question of the 'mechanics' of the application,
however, and not overly significant. What's more important is the need to
control the uniqueness of the year/incrementing number value combination
in
the table definition, which does require the current year value to be
inserted at a column position in each row. This does mean that the table
is
not properly normalized of course as the DateField column is functionally
dependent on only one part of the key, not 'the key, the whole key and
nothing but the key, so help me Codd'. But I can't see any way to avoid
that
(though I'm more than happy to be corrected if otherwise).

Another possible solution of course is not to have a current year or
incrementing number column at all, but to compute the serial numbers
within
each year on the fly from the date column. I suspect this would be
inappropriate, however, as the numbers would vary as rows were deleted
from
the table or if a row were inserted out of date sequence, so as part of a
fixed identifier would be useless.

Ken Sheridan
Stafford, England

For some reason I don't see Robert's reply in my newsreader. Your
response
to my comments (including Robert's reply, is the first I have seen).
I'm not sure what you're saying about the Default Value property on the
property sheet. If you're saying the Default Value property remains the
same for all records until the form is closed and reopened, that has not
been my experience.

You'll need to set the DefaultValue property in code. If you set it in
the
properties sheet its value will remain static until the form is closed
and
reopened again. Set it in the form's Current event procedure with:

Me.NumInc.DefaultValue = _
"""" & Nz(DMax("[NumInc]", "[tblMain]", _
"Year([DateField]) = " & Year(Date)), 0) + 1 & """"

Note that the DefaultValue property is a string expression regardless
of
the
data type of the field in question so should be wrapped in quotes
characters
as above. In fact in this case it wouldn't make any difference if the
quotes
characters were omitted, but in other circumstances they are crucial,
particularly with a date/time data type.

BTW the control Robert meant you could hide was the NumInc control, not
the
unbound control which concatenates the current year value with the
formatted
NumInc value.

Another point is that you won't be able to use the DateField and NumInc
fields as the primary key as this would not prevent duplicate NumInc
values
within one year. You would need to create another field, CurrentYear
say,
with a DefaultValue property of Year(Date()) and use this and NumInc as
the
composite primary key. In which case the above code could be changed
to:

Me.NumInc.DefaultValue = _
"""" & Nz(DMax("[NumInc]", "[tblMain]", _
"[CurrentYear] = " & Year(Date)), 0) + 1 & """"

The CurrentYear field need not be shown on the form of course.

Ken Sheridan
Stafford, England

:

Thanks for the information. I tried it exactly like you described and
even
tried it with a blank database using your exact names but I'm even
greener
than green and didn't get it to work.

The box you said I could hide says 2008-39448 Whenever I press Add
Record
button I get #Error in both boxes.

I'm trying to learn Access but it doesn't seem to be willing...



That wouldn't be autonumber as the term is used in Access.
Autonumber
is
an automatically generated number that is almost certain to be
unique,
but
it is for use by Access, and in most cases will not be seen by the
user.
Also, it cannot be guaranteed not to leave gaps in the numbering
sequence.
If it is possible to avoid gaps it is very, very difficult.

However, what you want can be done with an expression. First,
though,
there is no need to store the year if there is a date field in the
record.
I will call that field DateField, and the incrementing number
NumInc.
For
purposes of this reply, they are both in a table named tblMain,
which
is
the Record Source for a form.

On the form, bind a text box to the NumInc field (that is, select
NumInc
as its Control Source). This is done on the text box property
sheet.
To
see the Property Sheet, open the form in design view, right click on
the
text box, and select Properties. This will open a box with five
tabs
(if
it is not already open): Format, Data, Event, Other, and All.
Click
the
Data tab to find the place to set the Control Source. You may
already
know about the Property Sheet, but you identify yourself as a
beginner,
so
there it is just in case. Also on the Data tab is Default Value.
This
applies only to new records. In the Default Value row add the
expression:
=Nz(DMax("NumInc","tblMain","Year([DateField]) = " &
Year(Date())),0) +
1

You can hide this text box. In another text box, set the Control
Source
to:
=Year([DateField]) & "-" & Format([NumInc],"0000")

Look up the functions Nz, DMax, Year, Date, and Format in Help to
learn
more about what is going on here. In general, the first expression
is
telling Access to find the largest value in the NumInc field in a
record
in which the year in DateField is the same as the current year, and
to
add
one to that number. Nz is for the first record of the year. There
is
no
maximum value, so the DMax result is null. Instead of Null, Access
uses a
0 as the value, and adds 1 to it.

The second expression combines the Year from DateField, a hyphen,
and
the
number formatted with leading zeros as needed for numbers less than
1000.

Hello, I'm new to access and trying to learn by reading and
trying...

I would like to use a primary key ID in a table that would be the
current
year - 0001 and then auto count up for each record.

2008-0001
2008-0002

Then 1/1/2009 start

2009-0001
2009-0002

and so on.

If this is a stupid idea I would like to do this in another field
in
the
table.

I'm also looking for a way to Close a record so it can't be changed
without first reopening the record.

Thanks,
Robert

[email protected]

 

[BruceM]

Wow, thanks! I had not considered that using Default Value does not dirty
the form, so the user can back out without creating an otherwise empty
record. I had observed that phenomenon, as I mentioned earlier, but had not
put the pieces together. When I back out of a new record in which a Value
is set I have needed to test for other fields being filled in, and undo if
the Value is the only field with data. Using DefaultValue will greatly
simplify things in that regard. Learning on my own has certainly left some
gaps in my understanding.
Thanks you for the time and attention you have given to my questions. You
have imparted a lot of valuable information that will help me greatly in the
future.

I'd completely agree that its far better to use the DefaultValue property
in
situations like this rather than setting the Value of a control as the
former
doesn't Dirty the form, so the user can back out without having to Undo
the
insert operation. Similarly when passing a value from one form to another
via the OpenArgs property, e.g. when opening a separate form to insert a
row
into a table which references the first form's underlying table, its best
to
use this to set the DefaultValue property of the control bound to the
foreign
key in the second form rather than its Value as it again allows the user
to
bail out gracefully if necessary.

Another circumstance when the DefaultValue property is more appropriate is
when inserting a new row into a referenced table via the NotInList event
procedure of a combo box where its necessary to open a form to insert
values
into other columns besides that into which the NewData value is to be
inserted. Here's an example for adding a new city which opens a form and
passes the NewData value to it:

Private Sub cboCities_NotInList(NewData As String, Response As Integer)

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmCities", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmCities closed
DoCmd.Close acForm, "frmCities"
' ensure city has been added
If Not IsNull(DLookup("CityID", "Cities", "City = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Cities table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub

The frmCities form's Open event procedure then uses the OpenArgs property
to
set the City control's DefaultValue property with:

Private Sub Form_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then
Me.City.DefaultValue = """" & Me.OpenArgs & """"
End If

End Sub

Consequently the user can just close the form if they decide not to add
the
city, or they can proceed to add other required data such as the County or
State in which the city is located. As you'll have noticed the NotInList
event procedure's code checks to see if the new city has been added before
adding it to the combo box's list and updating the control.

Getting back to the question of deleting records in a form this does
require
a bit of care as if the normal user interface is used the record
disappears
from the form immediately, but is of course not finally deleted until the
deletion is confirmed. I've always felt this to be a little confusing for
users as they see the record disappear, which suggests its been deleted,
but
are then asked to confirm its deletion apparently after the event. Its
not
difficult to create a custom confirmation routine with some code in the
Delete and BeforeDelConfirm event procedures, however.

Ken Sheridan
Stafford, England

Thanks for the explanation. I did not know that about inserting and
deleting a record immediately, causing the value to skip as you have
described. Perhaps this is because I tend to use code rather than the
Default Value of a text box. Actually, I tend to use:
If Me.NewRecord Then etc.
I learned it that way a while ago, and often other things happen for a
new
record (controls are hidden or disabled or whatever), so there isn't any
extra coding (that is, DefaultValue uses one line to accomplish what If
Me.NewRecord needs several lines to do, unless there is already is "If
Me.NewRecord"). I have since learned that using "If Me.NewRecord" causes
the Before Insert event to run before the user has a chance to type
anything, and the Dirty event does not run at all, while when using the
DefaultValue property the Before Insert event runs when typing begins ,
and
the Dirty event runs too (I think that's how it went). I'm not sure what
else happens, but it does tell me that one approach or the other may be
needed in specific situations.
When people ask about incrementing numbers I usually point them to Roger
Carlson's sample database on the topic, which used the Default Value
property of a text box. If more complex incrementing is needed I still
tend
to use the text box Default Value when possible because it is simpler to
explain than using VBA, and until now I thought it worked exactly the
same
way.

Sorry, I got the two of you mixed up; I meant 'Bruce's reply'.

As regards the DefaultValue property, for simplicity I was unduly brief
in
my explanation as it’s a little more complex in fact. The behaviour if
you
set it to an expression of the type you posted in the properties sheet
is
in
my experience as follows:

1. If a series of new records are inserted in the form it updates the
value
correctly for each new record.

2. If a record prior to the latest one is deleted then this obviously
leaves a gap in the sequence, correctly so for most purposes (it is of
course
possible to 'fill the gaps' with a more complex solution if that should
be
required).

3. However, if a new row is inserted, then deleted in the same session
of
the form before a further record is inserted then the DefaultValue
property
remains static so the next number is 2 after the last number in the
table
rather than 1 after. Now, its arguable that this is correct behaviour
as
there is no difference in principle between deleting the latest record
and
a
prior one in the sequence. However, in practice that would for most
purposes
be regarded as incorrect behaviour, as most people would want the
number
following the latest row existing in the table as of now to be
inserted,
rather than the number following the latest record which existed before
its
deletion. It might be thought that its unlikely that this situation
would
arise, but Murphy's Law states otherwise!

So neither solutions are 'incorrect', but setting the value in code
seems
to
me to safely answer the more usual requirements for this sort of thing.

The above is really a question of the 'mechanics' of the application,
however, and not overly significant. What's more important is the need
to
control the uniqueness of the year/incrementing number value
combination
in
the table definition, which does require the current year value to be
inserted at a column position in each row. This does mean that the
table
is
not properly normalized of course as the DateField column is
functionally
dependent on only one part of the key, not 'the key, the whole key and
nothing but the key, so help me Codd'. But I can't see any way to
avoid
that
(though I'm more than happy to be corrected if otherwise).

Another possible solution of course is not to have a current year or
incrementing number column at all, but to compute the serial numbers
within
each year on the fly from the date column. I suspect this would be
inappropriate, however, as the numbers would vary as rows were deleted
from
the table or if a row were inserted out of date sequence, so as part of
a
fixed identifier would be useless.

Ken Sheridan
Stafford, England

:

For some reason I don't see Robert's reply in my newsreader. Your
response
to my comments (including Robert's reply, is the first I have seen).
I'm not sure what you're saying about the Default Value property on
the
property sheet. If you're saying the Default Value property remains
the
same for all records until the form is closed and reopened, that has
not
been my experience.

message
You'll need to set the DefaultValue property in code. If you set it
in
the
properties sheet its value will remain static until the form is
closed
and
reopened again. Set it in the form's Current event procedure with:

Me.NumInc.DefaultValue = _
"""" & Nz(DMax("[NumInc]", "[tblMain]", _
"Year([DateField]) = " & Year(Date)), 0) + 1 & """"

Note that the DefaultValue property is a string expression
regardless
of
the
data type of the field in question so should be wrapped in quotes
characters
as above. In fact in this case it wouldn't make any difference if
the
quotes
characters were omitted, but in other circumstances they are
crucial,
particularly with a date/time data type.

BTW the control Robert meant you could hide was the NumInc control,
not
the
unbound control which concatenates the current year value with the
formatted
NumInc value.

Another point is that you won't be able to use the DateField and
NumInc
fields as the primary key as this would not prevent duplicate NumInc
values
within one year. You would need to create another field,
CurrentYear
say,
with a DefaultValue property of Year(Date()) and use this and NumInc
as
the
composite primary key. In which case the above code could be
changed
to:

Me.NumInc.DefaultValue = _
"""" & Nz(DMax("[NumInc]", "[tblMain]", _
"[CurrentYear] = " & Year(Date)), 0) + 1 & """"

The CurrentYear field need not be shown on the form of course.

Ken Sheridan
Stafford, England

:

Thanks for the information. I tried it exactly like you described
and
even
tried it with a blank database using your exact names but I'm even
greener
than green and didn't get it to work.

The box you said I could hide says 2008-39448 Whenever I press Add
Record
button I get #Error in both boxes.

I'm trying to learn Access but it doesn't seem to be willing...



That wouldn't be autonumber as the term is used in Access.
Autonumber
is
an automatically generated number that is almost certain to be
unique,
but
it is for use by Access, and in most cases will not be seen by
the
user.
Also, it cannot be guaranteed not to leave gaps in the numbering
sequence.
If it is possible to avoid gaps it is very, very difficult.

However, what you want can be done with an expression. First,
though,
there is no need to store the year if there is a date field in
the
record.
I will call that field DateField, and the incrementing number
NumInc.
For
purposes of this reply, they are both in a table named tblMain,
which
is
the Record Source for a form.

On the form, bind a text box to the NumInc field (that is, select
NumInc
as its Control Source). This is done on the text box property
sheet.
To
see the Property Sheet, open the form in design view, right click
on
the
text box, and select Properties. This will open a box with five
tabs
(if
it is not already open): Format, Data, Event, Other, and All.
Click
the
Data tab to find the place to set the Control Source. You may
already
know about the Property Sheet, but you identify yourself as a
beginner,
so
there it is just in case. Also on the Data tab is Default Value.
This
applies only to new records. In the Default Value row add the
expression:
=Nz(DMax("NumInc","tblMain","Year([DateField]) = " &
Year(Date())),0) +
1

You can hide this text box. In another text box, set the Control
Source
to:
=Year([DateField]) & "-" & Format([NumInc],"0000")

Look up the functions Nz, DMax, Year, Date, and Format in Help to
learn
more about what is going on here. In general, the first
expression
is
telling Access to find the largest value in the NumInc field in a
record
in which the year in DateField is the same as the current year,
and
to
add
one to that number. Nz is for the first record of the year.
There
is
no
maximum value, so the DMax result is null. Instead of Null,
Access
uses a
0 as the value, and adds 1 to it.

The second expression combines the Year from DateField, a hyphen,
and
the
number formatted with leading zeros as needed for numbers less
than
1000.

Hello, I'm new to access and trying to learn by reading and
trying...

I would like to use a primary key ID in a table that would be
the
current
year - 0001 and then auto count up for each record.

2008-0001
2008-0002

Then 1/1/2009 start

2009-0001
2009-0002

and so on.

If this is a stupid idea I would like to do this in another
field
in
the
table.

I'm also looking for a way to Close a record so it can't be
changed
without first reopening the record.

Thanks,
Robert

[email protected]

 

[p120mb]

--
p120mb

Thanks for the information. I tried it exactly like you described and even
tried it with a blank database using your exact names but I'm even greener
than green and didn't get it to work.

The box you said I could hide says 2008-39448 Whenever I press Add Record
button I get #Error in both boxes.

I'm trying to learn Access but it doesn't seem to be willing...

That wouldn't be autonumber as the term is used in Access. Autonumber is
an automatically generated number that is almost certain to be unique, but
it is for use by Access, and in most cases will not be seen by the user.
Also, it cannot be guaranteed not to leave gaps in the numbering sequence.
If it is possible to avoid gaps it is very, very difficult.

However, what you want can be done with an expression. First, though,
there is no need to store the year if there is a date field in the record.
I will call that field DateField, and the incrementing number NumInc. For
purposes of this reply, they are both in a table named tblMain, which is
the Record Source for a form.

On the form, bind a text box to the NumInc field (that is, select NumInc
as its Control Source). This is done on the text box property sheet. To
see the Property Sheet, open the form in design view, right click on the
text box, and select Properties. This will open a box with five tabs (if
it is not already open): Format, Data, Event, Other, and All. Click the
Data tab to find the place to set the Control Source. You may already
know about the Property Sheet, but you identify yourself as a beginner, so
there it is just in case. Also on the Data tab is Default Value. This
applies only to new records. In the Default Value row add the expression:
=Nz(DMax("NumInc","tblMain","Year([DateField]) = " & Year(Date())),0) + 1

You can hide this text box. In another text box, set the Control Source
to:
=Year([DateField]) & "-" & Format([NumInc],"0000")

Look up the functions Nz, DMax, Year, Date, and Format in Help to learn
more about what is going on here. In general, the first expression is
telling Access to find the largest value in the NumInc field in a record
in which the year in DateField is the same as the current year, and to add
one to that number. Nz is for the first record of the year. There is no
maximum value, so the DMax result is null. Instead of Null, Access uses a
0 as the value, and adds 1 to it.

The second expression combines the Year from DateField, a hyphen, and the
number formatted with leading zeros as needed for numbers less than 1000.

Hello, I'm new to access and trying to learn by reading and trying...

I would like to use a primary key ID in a table that would be the current
year - 0001 and then auto count up for each record.

2008-0001
2008-0002

Then 1/1/2009 start

2009-0001
2009-0002

and so on.

If this is a stupid idea I would like to do this in another field in the
table.

I'm also looking for a way to Close a record so it can't be changed
without first reopening the record.

Thanks,
Robert

[email protected]