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]

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.

 

[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]

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?

 

[Red]

This one is easy

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]

This one is easy

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.

 

[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

 

[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]

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.

 

[Marshall Barton]

Comments inline
--
Marsh
MVP [MS Access]

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]

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

 

[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

 

[Kim Webb]

Since I'm not using numbers but text the autonumber does not work since
there is no way to convince access that 06000 comes after 99006, etc.
I did not come up with the current system and it can't be changed since
out DB is somehow exporting to an accounting package -soloman I think -
and the numbering system can't be changed.

 

[Marshall Barton]

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.

 

[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.