Field on Form = next record

[Kim Webb]

I have a field on a form for project number. I basically want it to be
the next available number (ie 06010 then 06011 etc). In the form I
create a text box and under control source I put:

=[Projects]![ProjectID]=[NextNumber]

This does not work

I tried:

=[Projects]![ProjectID]+1

I put in

=Last([ProjectID])+1

and it will show me the correct number but it never wrtes the number to
the table and gives me a primary key can't be null error since
ProjectID is my primary key.

I tried this:

[projectID]=Last([ProjectID])+1

it does not work.

Clearly I'm missing the part where it finds the next record and then
writes the value to the table-any ideas how I make that set happen?

[Marshall Barton]

Kim Webb wrote:

I have a field on a form for project number. I basically want it to be
the next available number (ie 06010 then 06011 etc). In the form I
create a text box and under control source I put:

=[Projects]![ProjectID]=[NextNumber] [snip]Clearly I'm missing the part where it finds the next record and then
writes the value to the table-any ideas how I make that set happen?

The project ID text box on the form must be bound to the
field in the table. This means that you can not use a
control source expression to calculate the number.

Instead you should use the form's BeforeUpdate event to
calculate the number by looking up the largest number in the
table and adding one to that:

Sub Form_BeforeUpdate( . . .
Me.ProjectID = Nz(DMax("ProjectID","Projects"), 0) +1
End Sub

I am just guessing the both the text box and the field in
the table are named ProjectID and the the table is named
Projects, so change those if I guessed wrong.

--
Marsh
MVP [MS Access]

[Kim Webb]

OK so on the form I've added the [event procedure] you suggected and
the control source now proint to:

ProjectID

for that field

but it just shows up blank-it does not show me the next project number.

[Marshall Barton]

Kim Webb wrote:

OK so on the form I've added the [event procedure] you suggected and
the control source now proint to:

ProjectID

for that field

but it just shows up blank-it does not show me the next project number.

The BeforeUpdate event doesn't fire until the record is
about to be saved, so you will not see it until after the
save. Did you navigate to a different record and back to
the newly created record (or check the table) to see if the
record was saved with the correct value?

--
Marsh
MVP [MS Access]

[Red]

This one is easy :D

private sub form_current()
Label1.text = format(getnewprojectid)
End sub
Private Function GetNewProjectID() as Integer
dim rs as recordset
set rs = currentdb.openrecordset("YOUR TABLE NAME HERE")
if rs.recordcount < 1 then
GetNewProjectID = 1
Exit function
else
rs.movelast
GetNewProjectID = rs("projectid") + 1
end if
End Function

[Marshall Barton]

Red wrote:

This one is easy :D

private sub form_current()
Label1.text = format(getnewprojectid)
End sub
Private Function GetNewProjectID() as Integer
dim rs as recordset
set rs = currentdb.openrecordset("YOUR TABLE NAME HERE")
if rs.recordcount < 1 then
GetNewProjectID = 1
Exit function
else
rs.movelast
GetNewProjectID = rs("projectid") + 1
end if
End Function

Hold on there Red. The Current event will calculate a new
key for every record that form navigation displays.

Furthermore, even in the case of navigating to the new
record, there may be a time delay of hours (lunch, meetings,
etc) between calculating the new projectID value and saving
it to the table. This means that multiple users have a good
chance of getting the same ID

Another problem is that the MoveLast is not guaranteed to
move to the record with the maximum projectID value.

--
Marsh
MVP [MS Access]

[Kim Webb]

You're correct it did write the value after I closed it. The problem I
had was that the projects are two digit year codes then three digits -
ie 98001 was the first project we did in 1998 so the DMax found a 99
project as the maximum and added one to that. I change the formula to
DLast and it's very close but it writes it as 6013 instead of 06013-
any idea how I remedy that?

Me.ProjectID = Nz(DLast("ProjectID","Projects"), 0) +1

Thanks very, very much for your help with this.

[Red]

True true...
I errantly wrote that while I was waiting for a macro to finish
running....

But, a couple things about this question I'd like to clear up before I
continue my hot air...

A developer should never allow a user to directly access the tables.
(Commandment #2 http://www.mvps.org/access/tencommandments.htm). So I
made the assumption that the form would have some sort of "Save"
button, instead of saving after pressing the next record button (or
whatever)...And, Access isn't really meant to be used for multiple
users....but w/e...

Next thing I was thinking... Kim probably wants this project ID number
so she can document it on paper somewhere, so the number must NOT
change in between starting and finishing...but with your solution this
is not possible.. and with my solution, you pointed out the problems...
At this point, I'd also like to note the possible problem with a user
"grabbing" a number, and end up not using it...

So, given what I think Kim was wanting, what you were thinking, and
what I was thinking, here is the total scenario as I see it:
(Of course, I usually over think things....)
1: Need to be able to see the Project ID # when entering information
into the form
2: Need to lock the # so multiple users can find the next number, and
not use one being currently used
3. Need to identify an unused number and place it back in the "usable"
list

I don't think either of our solutions fixes all 3 of these problems...

give me a bit, and I will come up with a solution :D

[Red]

How about this?

Option Compare Database
Option Explicit

Private Sub form_load()
Me.TimerInterval = 1000
Text0 = GetNewProjectID
End Sub
Private Function GetNewProjectID() As Integer
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT Max(Projects.ProjectID) AS
NewID FROM Projects;")

If rs.RecordCount < 1 Then
GetNewProjectID = 1
Exit Function
Else
rs.MoveLast
GetNewProjectID = rs("NewID") + 1
End If
End Function

Private Sub Form_Timer()
If Text0 <> GetNewProjectID Then
MsgBox ("Another user has used your number, please update your written
records")
Text0 = GetNewProjectID
End If
End Sub

[betwalk]

What would be the reason to not just use the default value property of
the control to do this task?

[Red]

I had a nice reply, but then my webbrowser screwed me...

Anyhow, yes, you could use it as the default property, but then you'd
have to double check the value hadn't changed prior to saving it, or at
regular intervals.

[Marshall Barton]

Red wrote:

How about this?

Option Compare Database
Option Explicit

Private Sub form_load()
Me.TimerInterval = 1000
Text0 = GetNewProjectID
End Sub
Private Function GetNewProjectID() As Integer
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT Max(Projects.ProjectID) AS
NewID FROM Projects;")

If rs.RecordCount < 1 Then
GetNewProjectID = 1
Exit Function
Else
rs.MoveLast
GetNewProjectID = rs("NewID") + 1
End If
End Function

Private Sub Form_Timer()
If Text0 <> GetNewProjectID Then
MsgBox ("Another user has used your number, please update your written
records")
Text0 = GetNewProjectID
End If
End Sub

The MoveLast unnecessary since the query will retrieve
exactly one record. The max field's value will be Null if
there are no records.

I hate using the timer to do anything because it interferes
with too many other things. Better to have a separate table
with the next available number and if a record is canceled
save the canceled record's number to the table so it can be
"reused".

To be totally safe, you should lock this table until the
data is consistent for this record and other users working
on other records at exactly the same time.

Bottom line: In a full blown scenario, this is not a
"simple" problem. The BeforeUpdate event leaves an
extremely small window for duplicate IDs and most folks use
this instead of worring about the full blown solution.

--
Marsh
MVP [MS Access]

[Marshall Barton]

Comments inline
--
Marsh
MVP [MS Access]

Red wrote:

True true...
I errantly wrote that while I was waiting for a macro to finish
running....

But, a couple things about this question I'd like to clear up before I
continue my hot air...

A developer should never allow a user to directly access the tables.
(Commandment #2 http://www.mvps.org/access/tencommandments.htm). So I
made the assumption that the form would have some sort of "Save"
button, instead of saving after pressing the next record button (or
whatever)...And, Access isn't really meant to be used for multiple
users....but w/e...
Hardly anyone uses a "save" button since Access takes care
of the save automatically when the user does just about
anthing that indicates editing the record is completed.

Access is actually pretty good at multi-user databases.
I've heard of a few very carefully designed and implemented
mdbs with close to 100 users (although 5 to 20 is much more
common).

Next thing I was thinking... Kim probably wants this project ID number
so she can document it on paper somewhere, so the number must NOT
change in between starting and finishing...but with your solution this
is not possible.. and with my solution, you pointed out the problems...
I have never run into a user need for writing the number
down, but there probably exists some situation that wants to
do that. In my experience a button to print a summary of
the new record's data is what the user's would prefer to
use. The print button can save the record before printing
so the number will appear on the report.

At this point, I'd also like to note the possible problem with a user
"grabbing" a number, and end up not using it...

So, given what I think Kim was wanting, what you were thinking, and
what I was thinking, here is the total scenario as I see it:
(Of course, I usually over think things....)
1: Need to be able to see the Project ID # when entering information
into the form
2: Need to lock the # so multiple users can find the next number, and
not use one being currently used
3. Need to identify an unused number and place it back in the "usable"
list

I don't think either of our solutions fixes all 3 of these problems...

This set of requirements is getting pretty complex and way
beyond the scope of how I read Kim's original question.
Personally, I have never run into a situation with all of
those requirements.

You're welcome to attack the problem if you feel so
inclined, but you may also want to think about some of the
other issues I posted to one of your other replies.

[Red]

>Hardly anyone uses a "save" button since Access takes care

of the save automatically when the user does just about
anthing that indicates editing the record is completed.
I use them all the time, you never know when a user will screw up and
delete a record that is vital to business...

One of the most common things I hear is "I accidentally delete this
field occasionaly, can you make it so I can change it, but undo it if I
accidentally screw up?"... Now, I know there are tons of ways to get
around it, but the simplest way is to make a simple "save" button to
add the records to the table. Now, I'm sure this could be one hell of
a statement is the table has 50+ fields, but, generally, most forms I
deal with are on a much smaller scale.
Access is actually pretty good at multi-user databases.
I've heard of a few very carefully designed and implemented
mdbs with close to 100 users (although 5 to 20 is much more
common).
I inherited some mdb's that are used by dozens of users. While they
CAN be used for multiple users, it is NOT optimal to be using access
(imho) for multiple users. I could link dozens of articles on both the
pro's and con's of Access, and most of them agree that there are far
better options for multiple users than Access (especially the version
I'm currently 'stuck' in... icky 97...)...
I have never run into a user need for writing the number
down, but there probably exists some situation that wants to
do that. In my experience a button to print a summary of
the new record's data is what the user's would prefer to
use. The print button can save the record before printing
so the number will appear on the report.
It must be insanely nice to have users/supervisors that get with the
times, and don't write anything down on paper. I give an example of a
job I had 4-5 years ago... I was temp'ing midnight as a data entry
operator at a warehouse.. entering orders... I had to enter each order
individually, and then write the system assigned number on the order.
Then, print the orders from the starting (system) number to the ending
(system) number. Then, I had to match up each original order with the
order from the system I was inputting them into.

Yada yada yada, yes, people often need to see that incremental number
when doing things.

This set of requirements is getting pretty complex and way
beyond the scope of how I read Kim's original question.
Personally, I have never run into a situation with all of
those requirements.
You're welcome to attack the problem if you feel so
inclined, but you may also want to think about some of the
other issues I posted to one of your other replies.

Indeed, it is a complex situation, but, we are both making assumptions
that Kim herself did not mention (You - the multiple user environment,
me- needing the numbers while inputing).
As far as attacking the problem, I was just trying to help while I was
wasting time at work..... I would be glad to help if she had additional
questions, as I'm sure you would be to.

Your advice has been taken, and as always, is greatly appreciated!

Thanks,

~Red

[Marshall Barton]

Kim Webb wrote:

You're correct it did write the value after I closed it. The problem I
had was that the projects are two digit year codes then three digits -
ie 98001 was the first project we did in 1998 so the DMax found a 99
project as the maximum and added one to that. I change the formula to
DLast and it's very close but it writes it as 6013 instead of 06013-
any idea how I remedy that?

Me.ProjectID = Nz(DLast("ProjectID","Projects"), 0) +1

Thanks very, very much for your help with this.

Since you are combining two values to make the ProjectID, it
should be stored in two separate fields. You can always put
them together in your forms or reports so the users won't
know how the table keeps the values. Actually you may
already have a project date field that already contains the
year information.

Back to your specific question. For the leading zero to be
saved in the table, the ProjectID field must be a TEXT type
field. You should confirm that before making any more
changes.

If it is a text field, then the DMax (not DLast) could be:

strYear = Format(Date, "yy")

Me.ProjectID = strYear & Nz(DMax("Val(Right(ProjectID,
3))","Projects", "Left(ProjectID,2)='" & strYear & "'"), 0)
+1

--
Marsh
MVP [MS Access]

[Kim Webb]

Since you are combining two values to make the ProjectID, it
should be stored in two separate fields. You can always put
them together in your forms or reports so the users won't
know how the table keeps the values. Actually you may
already have a project date field that already contains the
year information.
Well I'm not combining two values the value of ProjectID is just 95234,
99765, 01987, 06111- I've always just calculated.
Back to your specific question. For the leading zero to be
saved in the table, the ProjectID field must be a TEXT type
field. You should confirm that before making any more
changes.
It is a text type.


If it is a text field, then the DMax (not DLast) could be:

strYear = Format(Date, "yy")

Me.ProjectID = strYear & Nz(DMax("Val(Right(ProjectID,
3))","Projects", "Left(ProjectID,2)='" & strYear & "'"), 0)
+1

This does not work since my last project is 06010 and my next one
assigned needs to be 06011. It gives me 6011 but I just need to add
something to the formula below to get the zero to be the lead number:

Me.ProjectID = Nz(DLast("ProjectID","Projects"), 0) +1

What's wierd is that if I use

=Last([ProjectID])+1

It does give me the leading zero

[Marshall Barton]

be*****@gmail.com wrote:

What would be the reason to not just use the default value property of
the control to do this task?

The DefaultValue (with the DMax expression) would be
calculated at the user's first keystroke on a new record.
At the same time Access provides a blank record as another
potential new record, but it will display the same default
value as the record that is currently being created. Even
if you can live that confusing issue, having the new number
calculated so early in the process leaves a gaping hole for
other users to get the same number before the record you are
working on is saved.

--
Marsh
MVP [MS Access]

[Kim Webb]

Perhaps in an effort to make this easier to understand - before this
year what happened was the form had a field to SHOW people what the
next number was in the project list- so the ProjectID field was empty
and then a box to the right showed them which one to type in to the
ProjectID box. I had a text box named NextNumber and had in the
control source

=Last([ProjectID])+1

This worked well for many years until my users either got to busy or
too careless or both and suddenly were entering the wrong number by
mistake so if the number said 06010 was the next number they might
enter 06110 by accident.

This was a huge problem last year when we did 800 projects and had
hundreds of wasted numbers from mistakes.

So all I'm trying to do is avoid have users have to type in their own
project number and rather have it assigned to them automatically.

Also we previously had many users in the DB at the same time trying to
assign the same numbers so I was hoping to fix that also.

Thanks.

[Kim Webb]

Oh and I should also mention they DO need to write the project number
down-it's given out to all the vendors on the project and how the
project is tracked for the rest of the year. Typically as soon as the
number gets assinged to them they wrote it on the folder.

Thanks do much for all the help.

[Kim Webb]

I don't know if this is why there is always a leading zero kept when I
do the:

=Last([ProjectID])+1

in the control source but also in the text box under FORMAT it is:

00000

and under INPUT MASK it says:

99999;;*

I don't know if either of those help to make the zero stay leading but
I figured I'd mention them.

Thanks again.

[Kim Webb]

Well I spoke too soon - it actually does not write the correct project
number:

now for each one I get 6016

the next number should be 06019

I just keep putting the zero's in manually at the front.
Kim Webb wrote:

You're correct it did write the value after I closed it. The problem I
had was that the projects are two digit year codes then three digits -
ie 98001 was the first project we did in 1998 so the DMax found a 99
project as the maximum and added one to that. I change the formula to
DLast and it's very close but it writes it as 6013 instead of 06013-
any idea how I remedy that?

Me.ProjectID = Nz(DLast("ProjectID","Projects"), 0) +1

Thanks very, very much for your help with this.

[Marshall Barton]

Did you use both lines? The strYear line should preserve
the leading zero.

And, Yes, having a format of 00000 will display the leading
zero whether it is stored in the table or not. Check the
table ***without using a format for the field*** to see if
the leading zero is really there or not. If it isn't there,
my DMax expression will not work. I really need to know
this before you can expect to arrive at a solution.

You are being sidetracked by the DLast experiment. There is
no guarantee that it will retrieve the largest number in the
table. Sometimes it will, but other times it can retrieve
any old value ftom the field.

If you need to see the number before the record is saved and
if you might have multiple users performing this activity,
then this whole concept goes out the window and it would
require some really advanced programming and table
manipulations.
--
Marsh
MVP [MS Access]
Kim Webb wrote:

Since you are combining two values to make the ProjectID, it
should be stored in two separate fields. You can always put
them together in your forms or reports so the users won't
know how the table keeps the values. Actually you may
already have a project date field that already contains the
year information.

Well I'm not combining two values the value of ProjectID is just 95234,
99765, 01987, 06111- I've always just calculated.

Back to your specific question. For the leading zero to be
saved in the table, the ProjectID field must be a TEXT type
field. You should confirm that before making any more
changes.

It is a text type.

If it is a text field, then the DMax (not DLast) could be:

strYear = Format(Date, "yy")

Me.ProjectID = strYear & Nz(DMax("Val(Right(ProjectID,
3))","Projects", "Left(ProjectID,2)='" & strYear & "'"), 0)
+1

This does not work since my last project is 06010 and my next one
assigned needs to be 06011. It gives me 6011 but I just need to add
something to the formula below to get the zero to be the lead number:

Me.ProjectID = Nz(DLast("ProjectID","Projects"), 0) +1

What's wierd is that if I use

=Last([ProjectID])+1

It does give me the leading zero

[Kim Webb]

Marshall Barton wrote:

Did you use both lines? The strYear line should preserve
the leading zero.
yes i used both lines but how does it know what the latest project
number is to begin with.
And, Yes, having a format of 00000 will display the leading
zero whether it is stored in the table or not. Check the
table ***without using a format for the field*** to see if
the leading zero is really there or not. If it isn't there,
my DMax expression will not work. I really need to know
this before you can expect to arrive at a solution.
if i look at field projectID in the table it shows the leading zero for
all the projects after 1999.
You are being sidetracked by the DLast experiment. There is
no guarantee that it will retrieve the largest number in the
table. Sometimes it will, but other times it can retrieve
any old value ftom the field.

but Dmax doesn't work because 99888 is a larger number than 06010 so it
will always find the biggest number. Also this is a text field not a
number field.
If you need to see the number before the record is saved and
if you might have multiple users performing this activity,
then this whole concept goes out the window and it would
require some really advanced programming and table
manipulations.

I don't need to see the number - I can also just have another field
that shows them what it'll be using

=Last([ProjectID])+1

[Marshall Barton]

The DMax call I used:
DMax("Val(Right(ProjectID,3))","Projects",
"Left(ProjectID,2)='" & strYear & "'")

retrieves the highest numeric value of the right most three
digits where the first two characters match the current year
(with possible leading zero).

In your example where you said "99888 is a larger number
than 06010", it doesn't matter because the part
"Left(ProjectID,2)='" & strYear & "'" forces the max to only
look at projects that start with 06. Then the value
returned by "Val(Right(ProjectID,3))" is just the 10 in
06010.

Ahhh, I just realized I did not specify the declaration for
strYear and if that's not correct, it might cause the
leading zero to be dropped. I also forgot to force the
zeros in front of the right three digits. The code should
have been:

Dim strYear As String
strYear = Format(Date, "yy")
Me.ProjectID = strYear & _
Format(Nz(DMax("Val(Right(ProjectID,3))", _
"Projects", "Left(ProjectID,2)='" & strYear & _
"'"), 0) +1, "000")

Sorry if my mistakes have caused you any grief.
--
Marsh
MVP [MS Access]
Kim Webb wrote:

Marshall Barton wrote:

Did you use both lines? The strYear line should preserve
the leading zero.

yes i used both lines but how does it know what the latest project
number is to begin with.

And, Yes, having a format of 00000 will display the leading
zero whether it is stored in the table or not. Check the
table ***without using a format for the field*** to see if
the leading zero is really there or not. If it isn't there,
my DMax expression will not work. I really need to know
this before you can expect to arrive at a solution.

if i look at field projectID in the table it shows the leading zero for
all the projects after 1999.

You are being sidetracked by the DLast experiment. There is
no guarantee that it will retrieve the largest number in the
table. Sometimes it will, but other times it can retrieve
any old value ftom the field.

but Dmax doesn't work because 99888 is a larger number than 06010 so it
will always find the biggest number. Also this is a text field not a
number field.

If you need to see the number before the record is saved and
if you might have multiple users performing this activity,
then this whole concept goes out the window and it would
require some really advanced programming and table
manipulations.

I don't need to see the number - I can also just have another field
that shows them what it'll be using

=Last([ProjectID])+1

[Kim Webb]

I used this:

Dim strYear As String
strYear = Format(Date, "yy")
Me.ProjectID = strYear & _
Format(Nz(DMax("Val(Right(ProjectID,3))", _
"Projects", "Left(ProjectID,2)='" & strYear & _
"'"), 0) +1, "000")
And it worked - many thanks for all the help.

I have a similar issue with the INVOICE form and I'm wondering if
BeforeUpdate assigns the number when I save the record where can I put
that command so it saves the record ONLY if the number is already
blank?

Can I add an IF/THEN to this statement somehow.

Private Sub Description_BeforeUpdate(Cancel As Integer)
Me.Invoice = Nz(DMax("Invoice", "Invoices"), 0) + 1
End Sub

Thanks.


Marshall Barton wrote: The DMax call I used:
DMax("Val(Right(ProjectID,3))","Projects",
"Left(ProjectID,2)='" & strYear & "'")

retrieves the highest numeric value of the right most three
digits where the first two characters match the current year
(with possible leading zero).

In your example where you said "99888 is a larger number
than 06010", it doesn't matter because the part
"Left(ProjectID,2)='" & strYear & "'" forces the max to only
look at projects that start with 06. Then the value
returned by "Val(Right(ProjectID,3))" is just the 10 in
06010.

Ahhh, I just realized I did not specify the declaration for
strYear and if that's not correct, it might cause the
leading zero to be dropped. I also forgot to force the
zeros in front of the right three digits. The code should
have been:

Dim strYear As String
strYear = Format(Date, "yy")
Me.ProjectID = strYear & _
Format(Nz(DMax("Val(Right(ProjectID,3))", _
"Projects", "Left(ProjectID,2)='" & strYear & _
"'"), 0) +1, "000")

Sorry if my mistakes have caused you any grief.
--
Marsh
MVP [MS Access]
Kim Webb wrote:

Marshall Barton wrote:

Did you use both lines? The strYear line should preserve
the leading zero.

yes i used both lines but how does it know what the latest project
number is to begin with.

And, Yes, having a format of 00000 will display the leading
zero whether it is stored in the table or not. Check the
table ***without using a format for the field*** to see if
the leading zero is really there or not. If it isn't there,
my DMax expression will not work. I really need to know
this before you can expect to arrive at a solution.

if i look at field projectID in the table it shows the leading zero for
all the projects after 1999.

You are being sidetracked by the DLast experiment. There is
no guarantee that it will retrieve the largest number in the
table. Sometimes it will, but other times it can retrieve
any old value ftom the field.

but Dmax doesn't work because 99888 is a larger number than 06010 so it
will always find the biggest number. Also this is a text field not a
number field.

If you need to see the number before the record is saved and
if you might have multiple users performing this activity,
then this whole concept goes out the window and it would
require some really advanced programming and table
manipulations.

I don't need to see the number - I can also just have another field
that shows them what it'll be using

=Last([ProjectID])+1

[Marshall Barton]

Kim Webb wrote:

I used this:

Dim strYear As String
strYear = Format(Date, "yy")
Me.ProjectID = strYear & _
Format(Nz(DMax("Val(Right(ProjectID,3))", _
"Projects", "Left(ProjectID,2)='" & strYear & _
"'"), 0) +1, "000")

And it worked - many thanks for all the help.

I have a similar issue with the INVOICE form and I'm wondering if
BeforeUpdate assigns the number when I save the record where can I put
that command so it saves the record ONLY if the number is already
blank?

Can I add an IF/THEN to this statement somehow.

Private Sub Description_BeforeUpdate(Cancel As Integer)
Me.Invoice = Nz(DMax("Invoice", "Invoices"), 0) + 1
End Sub

I'm not sure I understand this latest twist. If you are
assigning the record's primary key, then you want to use the
**form** BerforeUpdate event, not a control's event.

I guess I'm guilty of assuming you have all the other logic
in place and it's looking like that was not a valid
assumption, so let me back up and fill in some background.
This activity we've been discussing needs to be done at the
time that the form is saving a **new** record. To reduce
the window where another user can get the same number, we
use the **form** BeforeUpdate event. To make sure this only
happens for a new record (not when an existing record has
just been edited), the code to set the number needs to be
enclosed in an If that checks for the new record situation:

Private Sub FORM_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me.Invoice = Nz(DMax("Invoice", "Invoices"), 0) + 1
End If
End Sub

I don't think I've ever seen the entire event procedure for
the Projects form, but, if you don't already have it, the
same If statement needs to be used there too.

--
Marsh
MVP [MS Access]

[Kim Webb]

I'm not sure I understand this latest twist. If you are
assigning the record's primary key, then you want to use the
**form** BerforeUpdate event, not a control's event.
For invoices the form was setup to allow users to print out the invoice
before closing the form. So you enter the invoice info - and most of
the fields are marked as being requred - and then print the invoice-
then close it.

If the number is not assigned until after the form is closed then the
invoice can't be printed until it's closed and opened back up. So I
put the event in after the last field that is requred - which happens
to be the description field.

This works except if someone goes in later and changes the description
the invoice number changes.


I guess I'm guilty of assuming you have all the other logic
in place and it's looking like that was not a valid
assumption, so let me back up and fill in some background.
This activity we've been discussing needs to be done at the
time that the form is saving a **new** record. To reduce
the window where another user can get the same number, we
use the **form** BeforeUpdate event. To make sure this only
happens for a new record (not when an existing record has
just been edited), the code to set the number needs to be
enclosed in an If that checks for the new record situation:

Private Sub FORM_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me.Invoice = Nz(DMax("Invoice", "Invoices"), 0) + 1
End If
End Sub

I don't think I've ever seen the entire event procedure for
the Projects form, but, if you don't already have it, the
same If statement needs to be used there too.

--
Marsh
MVP [MS Access]

[Marshall Barton]

You should still use the form's BeforeUpdate procedure. The
issue is that the record needs to be saved before it is
printed.

Presumably, you have a button on the form that users click
on to print the invoice. If so, then the first thing the
button's Click event procedure should do is save the record
using this line of code:
If Me.Dirty Then Me.Dirty = False

As part of saving the record, the form's BeforeUpdate event
will fire and, for a new record, assign the invoice number.
This way, the wole situation hangs together without all the
complications you're running into now.
--
Marsh
MVP [MS Access]
Kim Webb wrote:

I'm not sure I understand this latest twist. If you are
assigning the record's primary key, then you want to use the
**form** BerforeUpdate event, not a control's event.

For invoices the form was setup to allow users to print out the invoice
before closing the form. So you enter the invoice info - and most of
the fields are marked as being requred - and then print the invoice-
then close it.

If the number is not assigned until after the form is closed then the
invoice can't be printed until it's closed and opened back up. So I
put the event in after the last field that is requred - which happens
to be the description field.

This works except if someone goes in later and changes the description
the invoice number changes.

I guess I'm guilty of assuming you have all the other logic
in place and it's looking like that was not a valid
assumption, so let me back up and fill in some background.
This activity we've been discussing needs to be done at the
time that the form is saving a **new** record. To reduce
the window where another user can get the same number, we
use the **form** BeforeUpdate event. To make sure this only
happens for a new record (not when an existing record has
just been edited), the code to set the number needs to be
enclosed in an If that checks for the new record situation:

Private Sub FORM_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me.Invoice = Nz(DMax("Invoice", "Invoices"), 0) + 1
End If
End Sub

I don't think I've ever seen the entire event procedure for
the Projects form, but, if you don't already have it, the
same If statement needs to be used there too.