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
BruceM said:
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.
Ken Sheridan said:
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
(e-mail address removed)
