Field on Form = next record

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?

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]

Why not set the ID field to Autonumber? If you want to begin at 60010 you can append a table with that number as the ID to a table with the Autonumbered ID.

Originally Posted by 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?

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.

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]

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

Red wrote:

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.

--
Marsh
MVP [MS Access]

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.

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

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