Incremental numbering added to 2 different forms

[stormadvisor]

All,
This will probably end up as a multi part question. I am using Office
'03

I have a form (Log) that when a control button is pushed it opens
another form (Incident). When the entry is completed on the Incident
form, the Number (09-###) is entered into the Log. All of this can
happen at 3 locations simultaneously.

I want to click the button on the Log and open the Incident at a new
record. When completed a button click will close the Incident and an
incremental number assigned and entered onto the Log.

Right now I have both forms made and autonumber being used for the
Number. I have found, like most people, that autonumber will not work
if a record is deleted or with multiple users. Now I have to create
the incremental numbering scheme. I figure to use dmax for the
numbering but I am not sure how to do everything correctly.

Thanks
Matt

 

[BruceM]

Keep the autonumber field as a primary key, but use something like this in
code to increment the number:

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

IncrNum is the field in which the incrementing number is stored, and
txtIncrNum is the text box bound to the IncrNum field. It can be 0 x 0 in
size, but must have its Visible property set to Yes. To display the number
in the format you want, as the Control Source of an unbound text box:
=Format([DateField],"yy") & "-" & [IncrNum]

You can use the same expression in a query as a calculated field, which may
be used as the Control Source of a text box:
LogNum: Format([DateField],"yy") & "-" & [IncrNum]

This code can be in the form's Current event, or the After Insert event
(which waits for the user to start typing in the new record) if the user
needs to see the number right away. You can use the Before Update event to
wait until the last moment to add the number, minimizing the risk of other
users writing records at the same time and attempting to grab the number.
To avoid that problem, see the multi-user example here:
http://www.rogersaccesslibrary.com/...?TID=395&SID=abaa286afz79ze7195f427c7czzc4d74

The link is all on one line. It is the example entitle "Autonumber Problem"
here:
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=1

The method of incrementing the number there uses the Default Value of a text
box rather than code, but accomplishes the same thing. The error handling
code (if there are two users trying to grab the same number) is the same in
either case.

This example increments the number based on the date only in tblLog. If you
wish to start the numbering from 1 for each Incident you will need to add
additional criteria to DMax (assuming there is a link between Log records
and Incident records). See Help for more information about DMax. Post back
if you need asistance with that.

 

[stormadvisor]

Keep the autonumber field as a primary key, but use something like this in
code to increment the number:

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

So right now I have a query that does this to format the "Incident
Number" text box on the "Incidents" form. Here is the code
SELECT "09 - " & Format([Incident number],"000") AS Incident,
I have currently 253 records (Incidents) now. If I change this to VB
code, I should drop the format in the query and set up the "Incident
Number" text box to your example? How would I make it assign the
number when the "Save Record" button (Command32) is clicked and Insert
it in the "Incident Number" text box and on the "Log09" form,
"Incident Ref #" text box? This will be a multi-user setup.

IncrNum is the field in which the incrementing number is stored, and
txtIncrNum is the text box bound to the IncrNum field.  It can be 0 x 0in
size, but must have its Visible property set to Yes.  To display the number
in the format you want, as the Control Source of an unbound text box:
=Format([DateField],"yy") & "-" & [IncrNum]

Do I still need to do this if I use the VB code?

You can use the same expression in a query as a calculated field, which may
be used as the Control Source of a text box:
LogNum: Format([DateField],"yy") & "-" & [IncrNum]

This code can be in the form's Current event, or the After Insert event
(which waits for the user to start typing in the new record) if the user
needs to see the number right away.  You can use the Before Update event to
wait until the last moment to add the number, minimizing the risk of other
users writing records at the same time and attempting to grab the number.
To avoid that problem, see the multi-user example here:http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=395&SID=...

Yes I would want to use Before Update.

The link is all on one line.  It is the example entitle "Autonumber Problem"
here:http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=1

The method of incrementing the number there uses the Default Value of a text
box rather than code, but accomplishes the same thing.  The error handling
code (if there are two users trying to grab the same number) is the same in
either case.

This example increments the number based on the date only in tblLog.  If you
wish to start the numbering from 1 for each Incident you will need to add
additional criteria to DMax (assuming there is a link between Log records
and Incident records).  See Help for more information about DMax.  Post back
if you need asistance with that.

The only link is for the Incident Number to be inserted into the Log09
form.

Thanks

Matt

 

[stormadvisor]

I fogot to say that I need a 3 digit number like 09-001

Matt

 

[stormadvisor]

OK, I tried to do this and I broke it. In the query I am showing the
same date for each record. This includes the saved records. The
table shows the correct dates for all but I cannot add records to it.

I get an error with the code. I get a "Compile error, Expected:
Expression" after the "DefaultValue =". Here's what I entered:

Private txtIncrNum_BeforeUpdate(Cancel As Integer)

Me.txtIncrNum.DefaultValue =
Nz(DMax("[IncrNum]","[Incidents09]",_
"Year([Date]) = " & Year(Date())),0) + 1

End Sub


I put this "=Format([Date],"yy") & "-" & [IncrNum]" as the control
source for the text box. I removed what I had in the query that I
originally used for the "incident number" that I changed to "IncrNum"
in the "Incident09" table.

All I get in the text box is #Name?. I have a question now. Can this
be done with the use of
"09 - " & Format([IncrNum],"000") AS txtIncrNum,
In the query to format the number right and I would not need any of
the Date info.

Thanks for your help

-Matt

 

[BruceM]

The underscore characters are optional line continuation characters. If you
look carefully at my suggested code you will see an underscord after the =
sign, and a space between the comma and the underscore in the next line.

The code I showed was to calculate and store the number. Formatting affects
only the display. It makes no difference if you concatenate the number
(that is, assemble the various values into the displayed number) in the
Control Source of a text box or as a calculated field in a query. If the
form is based on a query you can add the calculated field to that query. If
the form is based on a table, you can easily change it to be based on a
query instead. You do not need a separate query for the calculated field.
As yet another option you could use VBA code to assign a value to an unbound
text box, but in this case one of the other options would be simpler.

IncrNum was my name for the field in which the incremented number is stored.
From what I can tell you have named it [Incident Number], which is fine,
although you would do best to leave spaces and special characters out of
names. Use your field name in the code in place of IncrNum.

It sounds as if you have a table just for 2009 records. If so, that is not
a good design. You should have one table for all records, and use a query
to limit the recordset just to the time period you want. My code assumed a
date field, and is designed to have 001 as the first IncidentNumber each
year. If "09" is a static prefix you do not need to use the Where argument
in DMax. You could just increment the number with this expression:

Me.txtIncrNum.DefaultValue = Nz(DMax("[IncrNum]","[Incidents09]"),0) + 1

However, YOU SHOULD NOT DO THIS! Managing your data will be much more
difficult this way, particularly if you need to include prior year records,
plus which you will need to create a new table and change the form's record
source every year, along with who knows how many reports and so forth. It
is a simple matter to store a date field and use that to filter the records.
A separate table every year will be a mess, to put it kindly.

The DMax expression I suggested is saying, in effect: Find the highest
value in the IncidentNumber field in a record that includes dates from the
current year, and add 1 to that number. If there are no numbers for this
year, start with 0 (This is the Nz part of the expression, which substitutes
0 for Null).

OK, I tried to do this and I broke it. In the query I am showing the
same date for each record. This includes the saved records. The
table shows the correct dates for all but I cannot add records to it.

I get an error with the code. I get a "Compile error, Expected:
Expression" after the "DefaultValue =". Here's what I entered:

Private txtIncrNum_BeforeUpdate(Cancel As Integer)

Me.txtIncrNum.DefaultValue =
Nz(DMax("[IncrNum]","[Incidents09]",_
"Year([Date]) = " & Year(Date())),0) + 1

End Sub


I put this "=Format([Date],"yy") & "-" & [IncrNum]" as the control
source for the text box. I removed what I had in the query that I
originally used for the "incident number" that I changed to "IncrNum"
in the "Incident09" table.

All I get in the text box is #Name?. I have a question now. Can this
be done with the use of
"09 - " & Format([IncrNum],"000") AS txtIncrNum,
In the query to format the number right and I would not need any of
the Date info.

Thanks for your help

-Matt

 

[stormadvisor]

The underscore characters are optional line continuation characters.  If you
look carefully at my suggested code you will see an underscord after the =
sign, and a space between the comma and the underscore in the next line.

Understand. I have switched them for spaces.

The code I showed was to calculate and store the number.  Formatting affects
only the display.  It makes no difference if you concatenate the number
(that is, assemble the various values into the displayed number) in the
Control Source of a text box or as a calculated field in a query.  If the
form is based on a query you can add the calculated field to that query.  If
the form is based on a table, you can easily change it to be based on a
query instead.  You do not need a separate query for the calculated field.
As yet another option you could use VBA code to assign a value to an unbound
text box, but in this case one of the other options would be simpler.

I have left the "09 - " & Format([IncrNum],"000") AS txtIncrNum in
the incidentsqry query to format the number.

IncrNum was my name for the field in which the incremented number is stored.
From what I can tell you have named it [Incident Number], which is fine,
although you would do best to leave spaces and special characters out of
names.  Use your field name in the code in place of IncrNum.

I see your point and I have made changes to all areas that reflect
your suggestions and code.

It sounds as if you have a table just for 2009 records.  If so, that isnot
a good design.  You should have one table for all records, and use a query
to limit the recordset just to the time period you want.

OK I have made that change to tblincidents

 My code assumed a
date field, and is designed to have 001 as the first IncidentNumber each
year.  If "09" is a static prefix you do not need to use the Where argument
in DMax.  You could just increment the number with this expression:

Me.txtIncrNum.DefaultValue = Nz(DMax("[IncrNum]","[Incidents09]"),0) + 1

However, YOU SHOULD NOT DO THIS!  

OK I won't

Managing your data will be much more
difficult this way, particularly if you need to include prior year records,
plus which you will need to create a new table and change the form's record
source every year, along with who knows how many reports and so forth.  It
is a simple matter to store a date field and use that to filter the records.
A separate table every year will be a mess, to put it kindly.

The DMax expression I suggested is saying, in effect:  Find the highest
value in the IncidentNumber field in a record that includes dates from the
current year, and add 1 to that number.  If there are no numbers for this
year, start with 0 (This is the Nz part of the expression, which substitutes
0 for Null).

Here is what code I enterd to do the year and I assume number:

Me.txtIncrNum.DefaultValue = Nz(DMax("[IncrNum]", "[tblIncidents]", "
Year([Datefield]) = " & Year(Date)), 0) + " 1 "

I had to play with it a little till I found the right thing to stop
the compile errors I received.
I put it on 1 line and I added a quotation marks around the number 1.
That stopped the
errors.

I said I assume the numbers because the error I get is when I try to
enter data on the form.
I get an error #1364. Any Ideas?

I want to thank you for your patience. I have learned access by
doing. I am trying to get work to
send me for some formal classes on this.

Matt

 

[BruceM]

There are some missing parentheses. Date is a function that needs empty
parentheses after it when it is used in an expression such as this. In VBA
code the parentheses are dropped in a case such as this:

Me.DateField = Date

However, they are needed in the DMax expression:

Me.txtIncrNum.DefaultValue = Nz(DMax("[IncrNum]", "[tblIncidents]", "
Year([Datefield]) = " & Year(Date())), 0) + 1

I don't know if you are familiar with using the immediate window in the VBA
editor to test expressions. To do so, open the database and press Ctrl + G.
In the immediate window type:

?Date()

and press Enter. It will show you today's date. Now wrap the Date function
in the Year function:

?Year(Date())

Note that the parentheses from Date are still there, plus the parentheses
from Year.

Going back to the DMax, you have the equivalent of this:
DMax("[IncrNum]", "[tblIncidents]", _
"Year([Datefield]) = " & 2009)

What you need to do is find the expression that returns the current year,
and insert that entire expression into the DMax expression in place of
hard-coding the year. That is, replace 2009 with Year(Date()). This is
what I showed in the first example.

If you are creating the first record of the year there will be no value for
the largest number in IncrNum for the current year, so DMax will return
Null. Nz converts the null to a 0. Add 1 to 0, and your first record of
the year is numbered 1.

If you use the expression you showed:

"09 - " & Format([IncrNum],"000")

You will get the desired format this year, but next year it will still show
the 09 prefix. Instead, format your DateField for the first part:

txtIncrNum: Format([DateField],"yy\ \-\ ") & Format([IncrNum],"000")

The back slashes mean that what follows is a literal character. It is the
same as:

txtIncrNum: Format([DateField],"yy") & " - " & Format([IncrNum],"000")

If you are still getting an error message, please provide the text of the
error if available, and describe exactly what causes it. I could not find
information about error 1364, and it does not ring any bells, so I can't
suggest anything offhand.

The underscore characters are optional line continuation characters. If
you
look carefully at my suggested code you will see an underscord after the =
sign, and a space between the comma and the underscore in the next line.

Understand. I have switched them for spaces.

The code I showed was to calculate and store the number. Formatting
affects
only the display. It makes no difference if you concatenate the number
(that is, assemble the various values into the displayed number) in the
Control Source of a text box or as a calculated field in a query. If the
form is based on a query you can add the calculated field to that query.
If
the form is based on a table, you can easily change it to be based on a
query instead. You do not need a separate query for the calculated field.
As yet another option you could use VBA code to assign a value to an
unbound
text box, but in this case one of the other options would be simpler.

I have left the "09 - " & Format([IncrNum],"000") AS txtIncrNum in
the incidentsqry query to format the number.

IncrNum was my name for the field in which the incremented number is
stored.
From what I can tell you have named it [Incident Number], which is fine,
although you would do best to leave spaces and special characters out of
names. Use your field name in the code in place of IncrNum.

I see your point and I have made changes to all areas that reflect
your suggestions and code.

It sounds as if you have a table just for 2009 records. If so, that is not
a good design. You should have one table for all records, and use a query
to limit the recordset just to the time period you want.

OK I have made that change to tblincidents

My code assumed a
date field, and is designed to have 001 as the first IncidentNumber each
year. If "09" is a static prefix you do not need to use the Where argument
in DMax. You could just increment the number with this expression:

Me.txtIncrNum.DefaultValue = Nz(DMax("[IncrNum]","[Incidents09]"),0) + 1

However, YOU SHOULD NOT DO THIS!

OK I won't

Managing your data will be much more
difficult this way, particularly if you need to include prior year
records,
plus which you will need to create a new table and change the form's
record
source every year, along with who knows how many reports and so forth. It
is a simple matter to store a date field and use that to filter the
records.
A separate table every year will be a mess, to put it kindly.

The DMax expression I suggested is saying, in effect: Find the highest
value in the IncidentNumber field in a record that includes dates from the
current year, and add 1 to that number. If there are no numbers for this
year, start with 0 (This is the Nz part of the expression, which
substitutes
0 for Null).

Here is what code I enterd to do the year and I assume number:

Me.txtIncrNum.DefaultValue = Nz(DMax("[IncrNum]", "[tblIncidents]", "
Year([Datefield]) = " & Year(Date)), 0) + " 1 "

I had to play with it a little till I found the right thing to stop
the compile errors I received.
I put it on 1 line and I added a quotation marks around the number 1.
That stopped the
errors.

I said I assume the numbers because the error I get is when I try to
enter data on the form.
I get an error #1364. Any Ideas?

I want to thank you for your patience. I have learned access by
doing. I am trying to get work to
send me for some formal classes on this.

Matt

 

[stormadvisor]

You should have received an email with a link to screenshots.

Matt

 

[BruceM]

I rarely check that e-mail. I took a look just now. For one thing, the
expression you posted shows line breaks in the wrong places. The newsreader
adds a line break when it runs out of space on a line. You need to keep
that in mind when viewing code, links, and so forth. In VBA a line break
needs to be indicated by a space followed by an underscore:

Me.txtIncrNum.DefaultValue = Nz(DMax("[IncrNum]", "[tblIncidents]", _
"Year([Datefield]) = " & Year(Date())), 0) + 1

Also, there is no quote after the 1, which I see in your screen shot.

For the rest the main problem seems to be that the number is not showing up
on the form. Where are you running the code to set the DefaultValue of
txtIncrNum?

The query seems to be running OK, and the value for IncrNum seems to be
stored in the appropriate table.

You should have received an email with a link to screenshots.

Matt

There are some missing parentheses. Date is a function that needs empty
parentheses after it when it is used in an expression such as this. In VBA
code the parentheses are dropped in a case such as this:

Me.DateField = Date

However, they are needed in the DMax expression:

Me.txtIncrNum.DefaultValue = Nz(DMax("[IncrNum]", "[tblIncidents]", "
Year([Datefield]) = " & Year(Date())), 0) + 1

I don't know if you are familiar with using the immediate window in the
VBA
editor to test expressions. To do so, open the database and press Ctrl +
G.
In the immediate window type:

?Date()

and press Enter. It will show you today's date. Now wrap the Date function
in the Year function:

?Year(Date())

Note that the parentheses from Date are still there, plus the parentheses
from Year.

Going back to the DMax, you have the equivalent of this:
DMax("[IncrNum]", "[tblIncidents]", _
"Year([Datefield]) = " & 2009)

What you need to do is find the expression that returns the current year,
and insert that entire expression into the DMax expression in place of
hard-coding the year. That is, replace 2009 with Year(Date()). This is
what I showed in the first example.

If you are creating the first record of the year there will be no value
for
the largest number in IncrNum for the current year, so DMax will return
Null. Nz converts the null to a 0. Add 1 to 0, and your first record of
the year is numbered 1.

If you use the expression you showed:

"09 - " & Format([IncrNum],"000")

You will get the desired format this year, but next year it will still
show
the 09 prefix. Instead, format your DateField for the first part:

txtIncrNum: Format([DateField],"yy\ \-\ ") & Format([IncrNum],"000")

The back slashes mean that what follows is a literal character. It is the
same as:

txtIncrNum: Format([DateField],"yy") & " - " & Format([IncrNum],"000")

If you are still getting an error message, please provide the text of the
error if available, and describe exactly what causes it. I could not find
information about error 1364, and it does not ring any bells, so I can't
suggest anything offhand.

 

[stormadvisor]

I rarely check that e-mail.  I took a look just now.  For one thing, the
expression you posted shows line breaks in the wrong places.  The newsreader
adds a line break when it runs out of space on a line.  You need to keep
that in mind when viewing code, links, and so forth.  In VBA a line break
needs to be indicated by a space followed by an underscore:

Me.txtIncrNum.DefaultValue = Nz(DMax("[IncrNum]", "[tblIncidents]", _
    "Year([Datefield]) = " & Year(Date())), 0) + 1

Also, there is no quote after the 1, which I see in your screen shot.

The quotation mark was automatically being filled in. Adding the
underscore properly stopped that.
Now I cannot get the parens to stay for the Date(). When I save it
changes Year(Date())), 0) + 1 to
Year(Date)), 0) + 1 without the date parens. It will not take. I get
a code helper box (don't know what else
to call it) that shows Year(date). Screenshot at the link I gave you
titled year_date.jpg.

For the rest the main problem seems to be that the number is not showing up
on the form.  Where are you running the code to set the DefaultValue of
txtIncrNum?

It is under the txtIncrNum event tab for Before Update.

I feel like I am going in circles. I think that we just have it but
little thing is stopping us.
A small setting or something. I have gone round and round with this
and I can't figuire it out.

The bad thing is that this is 1 of at least 3 things that I need to
get done on this. The other
2 are buttons that I want to have do special procedures. 1 at a time
though.

Matt

 

[BruceM]

Access removes the parentheses in VBA code, but they are needed in text box
and query expressions. By the same token, Access will put square brackets
around properties in text box expressions, but they are not used in those
same expressions in VBA. It is not a coding problem, but I do wish
sometimes for more consistency.

A text box Before Update event runs only if the text box has had something
typed into it. Since you are assigning the value via code, the Before
Update event will not run *for that text box*. The form's Before Update
event, however, runs whenever you try to save a record that has been
modified by any means. Access tries to save a record when you explictly
save it by a command button click event or some such, when you attempt to
navigate to another record, when you try to close the database, when you
move to a subform, and probably a few other reasons that do not occur to me
at the moment.

The code looks OK, provided:
1) IncrNum is the name of the field
2) txtIncrNum is the name of the text box bound to IncrNum
3) tblIncidents is the name of the table
4) datefield is the name of a date field in the record

It would help if you choose a different name for the calculated query field
that produces the 09-001 etc. format than for the text box (or anything
else, for that matter). For the query field, maybe FullNumber rather than
txtIncrNum. Save txtIncrNum as the name of the text box. In general, don't
use the same name for two different things. It can lead to confusion both
for you and for Access.

The problem with the code is that it never runs, because the text box Before
Update event does not occur. You can place the code in the form's Current
event, so that the number is created as soon as you move to a new record
(DefaultValue applies only to new records). Or you can use the form's
Before Insert event, which runs when the user starts typing in a new record.
Or you could use the form's Before Update event. This would have an
advantage in a multi-user environment in that it would be rather unlikely
that two people would save a new record at the exact same time, so the
chance of a duplicate number is greatly reduced. The disadvantage is that
the number is not visible when the record is created. There are other
options for the code to create a new number, so if you have a specific need,
describe it and we'll see what's possible.

Another very important thing: The text box txtIncrNum containing the can
contain only the IncidentNumber (or IncrNum). You can make the text box 0"
x 0", but its Visible property must be True. It's only purpose is to
provide a place for the incremented number to be created. To display the
number in the format you wish, create a query field as you have done to
produce the 09-001 number. If that field is named FullNumber (rather than
txtIncrNum as it is now), bind a text box to FullNumber.

http://picasaweb.google.com/matt.kc8bew/AccessHelp?authkey=Gv1sRgCKa3paucztvsmQE&feat=directlink

I rarely check that e-mail. I took a look just now. For one thing, the
expression you posted shows line breaks in the wrong places. The
newsreader
adds a line break when it runs out of space on a line. You need to keep
that in mind when viewing code, links, and so forth. In VBA a line break
needs to be indicated by a space followed by an underscore:

Me.txtIncrNum.DefaultValue = Nz(DMax("[IncrNum]", "[tblIncidents]", _
"Year([Datefield]) = " & Year(Date())), 0) + 1

Also, there is no quote after the 1, which I see in your screen shot.

The quotation mark was automatically being filled in. Adding the
underscore properly stopped that.
Now I cannot get the parens to stay for the Date(). When I save it
changes Year(Date())), 0) + 1 to
Year(Date)), 0) + 1 without the date parens. It will not take. I get
a code helper box (don't know what else
to call it) that shows Year(date). Screenshot at the link I gave you
titled year_date.jpg.

For the rest the main problem seems to be that the number is not showing
up
on the form. Where are you running the code to set the DefaultValue of
txtIncrNum?

It is under the txtIncrNum event tab for Before Update.

I feel like I am going in circles. I think that we just have it but
little thing is stopping us.
A small setting or something. I have gone round and round with this
and I can't figuire it out.

The bad thing is that this is 1 of at least 3 things that I need to
get done on this. The other
2 are buttons that I want to have do special procedures. 1 at a time
though.

Matt

 

[stormadvisor]

Access removes the parentheses in VBA code, but they are needed in text box
and query expressions.  By the same token, Access will put square brackets
around properties in text box expressions, but they are not used in those
same expressions in VBA.  It is not a coding problem, but I do wish
sometimes for more consistency.

A text box Before Update event runs only if the text box has had something
typed into it.  Since you are assigning the value via code, the Before
Update event will not run *for that text box*.  The form's Before Update
event, however, runs whenever you try to save a record that has been
modified by any means.  Access tries to save a record when you explictly
save it by a command button click event or some such, when you attempt to
navigate to another record, when you try to close the database, when you
move to a subform, and probably a few other reasons that do not occur to me
at the moment.

The code looks OK, provided:
1) IncrNum is the name of the field
2) txtIncrNum is the name of the text box bound to IncrNum
3) tblIncidents is the name of the table
4) datefield is the name of a date field in the record

It would help if you choose a different name for the calculated query field
that produces the 09-001 etc. format than for the text box (or anything
else, for that matter).  For the query field, maybe FullNumber rather than
txtIncrNum.  Save txtIncrNum as the name of the text box.  In general, don't
use the same name for two different things.  It can lead to confusion both
for you and for Access.

The problem with the code is that it never runs, because the text box Before
Update event does not occur.  You can place the code in the form's Current
event, so that the number is created as soon as you move to a new record
(DefaultValue applies only to new records).  Or you can use the form's
Before Insert event, which runs when the user starts typing in a new record.
Or you could use the form's Before Update event.  This would have an
advantage in a multi-user environment in that it would be rather unlikely
that two people would save a new record at the exact same time, so the
chance of a duplicate number is greatly reduced.  The disadvantage is that
the number is not visible when the record is created.  There are other
options for the code to create a new number, so if you have a specific need,
describe it and we'll see what's possible.

Another very important thing:  The text box txtIncrNum containing the can
contain only the IncidentNumber (or IncrNum).  You can make the text box 0"
x 0", but its Visible property must be True.  It's only purpose is to
provide a place for the incremented number to be created.  To display the
number in the format you wish, create a query field as you have done to
produce the 09-001 number.  If that field is named FullNumber (rather than
txtIncrNum as it is now), bind a text box to FullNumber.

http://picasaweb.google.com/matt.kc8bew/AccessHelp?authkey=Gv1sRgCKa3....

I rarely check that e-mail. I took a look just now. For one thing, the
expression you posted shows line breaks in the wrong places. The
newsreader
adds a line break when it runs out of space on a line. You need to keep
that in mind when viewing code, links, and so forth. In VBA a line break
needs to be indicated by a space followed by an underscore:

Me.txtIncrNum.DefaultValue = Nz(DMax("[IncrNum]", "[tblIncidents]", _
"Year([Datefield]) = " & Year(Date())), 0) + 1

Also, there is no quote after the 1, which I see in your screen shot.

The quotation mark was automatically being filled in.  Adding the
underscore properly stopped that.
Now I cannot get the parens to stay for the Date().  When I save it
changes Year(Date())), 0) + 1 to
Year(Date)), 0) + 1 without the date parens.  It will not take.  I get
a code helper box (don't know what else
to call it) that shows Year(date).  Screenshot at the link I gave you
titled year_date.jpg.

For the rest the main problem seems to be that the number is not showing
up
on the form. Where are you running the code to set the DefaultValue of
txtIncrNum?

It is under the txtIncrNum event tab for Before Update.

I feel like I am going in circles.  I think that we just have it but
little thing is stopping us.
A small setting or something.  I have gone round and round with this
and I can't figuire it out.

The bad thing is that this is 1 of at least 3 things that I need to
get done on this.  The other
2 are buttons that I want to have do special procedures.  1 at a time
though.

Matt

Got it! I also met up with someone that helped me to understand it
too.
Thanks for all your patience!! It was GREATLY appreciated!

Matt

 

[BruceM]

Glad to help. Good luck with the project.

Access removes the parentheses in VBA code, but they are needed in text
box
and query expressions. By the same token, Access will put square brackets
around properties in text box expressions, but they are not used in those
same expressions in VBA. It is not a coding problem, but I do wish
sometimes for more consistency.

A text box Before Update event runs only if the text box has had something
typed into it. Since you are assigning the value via code, the Before
Update event will not run *for that text box*. The form's Before Update
event, however, runs whenever you try to save a record that has been
modified by any means. Access tries to save a record when you explictly
save it by a command button click event or some such, when you attempt to
navigate to another record, when you try to close the database, when you
move to a subform, and probably a few other reasons that do not occur to
me
at the moment.

The code looks OK, provided:
1) IncrNum is the name of the field
2) txtIncrNum is the name of the text box bound to IncrNum
3) tblIncidents is the name of the table
4) datefield is the name of a date field in the record

It would help if you choose a different name for the calculated query
field
that produces the 09-001 etc. format than for the text box (or anything
else, for that matter). For the query field, maybe FullNumber rather than
txtIncrNum. Save txtIncrNum as the name of the text box. In general, don't
use the same name for two different things. It can lead to confusion both
for you and for Access.

The problem with the code is that it never runs, because the text box
Before
Update event does not occur. You can place the code in the form's Current
event, so that the number is created as soon as you move to a new record
(DefaultValue applies only to new records). Or you can use the form's
Before Insert event, which runs when the user starts typing in a new
record.
Or you could use the form's Before Update event. This would have an
advantage in a multi-user environment in that it would be rather unlikely
that two people would save a new record at the exact same time, so the
chance of a duplicate number is greatly reduced. The disadvantage is that
the number is not visible when the record is created. There are other
options for the code to create a new number, so if you have a specific
need,
describe it and we'll see what's possible.

Another very important thing: The text box txtIncrNum containing the can
contain only the IncidentNumber (or IncrNum). You can make the text box 0"
x 0", but its Visible property must be True. It's only purpose is to
provide a place for the incremented number to be created. To display the
number in the format you wish, create a query field as you have done to
produce the 09-001 number. If that field is named FullNumber (rather than
txtIncrNum as it is now), bind a text box to FullNumber.

http://picasaweb.google.com/matt.kc8bew/AccessHelp?authkey=Gv1sRgCKa3...

I rarely check that e-mail. I took a look just now. For one thing, the
expression you posted shows line breaks in the wrong places. The
newsreader
adds a line break when it runs out of space on a line. You need to keep
that in mind when viewing code, links, and so forth. In VBA a line break
needs to be indicated by a space followed by an underscore:

Me.txtIncrNum.DefaultValue = Nz(DMax("[IncrNum]", "[tblIncidents]", _
"Year([Datefield]) = " & Year(Date())), 0) + 1

Also, there is no quote after the 1, which I see in your screen shot.

The quotation mark was automatically being filled in. Adding the
underscore properly stopped that.
Now I cannot get the parens to stay for the Date(). When I save it
changes Year(Date())), 0) + 1 to
Year(Date)), 0) + 1 without the date parens. It will not take. I get
a code helper box (don't know what else
to call it) that shows Year(date). Screenshot at the link I gave you
titled year_date.jpg.

For the rest the main problem seems to be that the number is not showing
up
on the form. Where are you running the code to set the DefaultValue of
txtIncrNum?

It is under the txtIncrNum event tab for Before Update.

I feel like I am going in circles. I think that we just have it but
little thing is stopping us.
A small setting or something. I have gone round and round with this
and I can't figuire it out.

The bad thing is that this is 1 of at least 3 things that I need to
get done on this. The other
2 are buttons that I want to have do special procedures. 1 at a time
though.

Matt

Got it! I also met up with someone that helped me to understand it
too.
Thanks for all your patience!! It was GREATLY appreciated!

Matt