Calculating a value for a new record

[Julian]

I have a simple database which records a list of different reports my team
produces. Each report has a unique hopefully sequential number "Report No"
which is different to the "Record ID" (Autonumber).

I have a form which I am using to add new reports to the underlying table.
As I add a new report record I want to look up the highest existing Report No
value in the table and auto populate the New Report Form Report No Control
with that number +1. i.e. if the last report added was numbered 255 the next
one shoiuld be 256. I have tried using Max ([Current Report List]![Report
No])+1 in the Control Source property to create the number on a test form and
this creates the correct value but when I try using the same expression in
the Add Report Form it results in the value 1. The only difference between
the two forms is that the form I want to use Data Entry is set to Yes and
only shows 1 record when opened but the test form Data Entry is set to No and
shows 200+ records. If changed to Yes then the Max expression does not
produce the correct value. Any ideas please

 

[Tom Wickerath]

Check out this sample from Roger's Access Library:

AutonumberProblem.mdb
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=395


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Julian]

Eventually sorted thank you. I tried the DMAX solution but initially got
ERROR but after I put [ ] around the field name it worked perfectly.

Check out this sample from Roger's Access Library:

AutonumberProblem.mdb
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=395


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

I have a simple database which records a list of different reports my team
produces. Each report has a unique hopefully sequential number "Report No"
which is different to the "Record ID" (Autonumber).

I have a form which I am using to add new reports to the underlying table.
As I add a new report record I want to look up the highest existing Report No
value in the table and auto populate the New Report Form Report No Control
with that number +1. i.e. if the last report added was numbered 255 the next
one shoiuld be 256. I have tried using Max ([Current Report List]![Report
No])+1 in the Control Source property to create the number on a test form and
this creates the correct value but when I try using the same expression in
the Add Report Form it results in the value 1. The only difference between
the two forms is that the form I want to use Data Entry is set to Yes and
only shows 1 record when opened but the test form Data Entry is set to No and
shows 200+ records. If changed to Yes then the Max expression does not
produce the correct value. Any ideas please