How About the MAX() function

[rave3085]

i have a field by name IDN in the access table that is of field type autonumber!
so when the form loads, i want the next automatically generated id to appear in the textbox named CIDN.text(enabed=false). this was the code i tried out ::::

IdRs.Open "SELECT MAX(IDN) as NewId from expenses", DB, adOpenDynamic, adLockOptimistic
NewId = NewId + 1
CIDN.Text = NewId

The thing is that im always getting the number"1" in the textbox eventhough the recordset has more than 30records. It is not returning the highest IDN,

Can anybody suggest me what the problem is and do tell me the mistake i have done and also the best way of doing such a thing.

[ansumansahu]

i have a field by name IDN in the access table that is of field type autonumber!
so when the form loads, i want the next automatically generated id to appear in the textbox named CIDN.text(enabed=false). this was the code i tried out ::::

IdRs.Open "SELECT MAX(IDN) as NewId from expenses", DB, adOpenDynamic, adLockOptimistic
NewId = NewId + 1
CIDN.Text = NewId

The thing is that im always getting the number"1" in the textbox eventhough the recordset has more than 30records. It is not returning the highest IDN,

Can anybody suggest me what the problem is and do tell me the mistake i have done and also the best way of doing such a thing.

If you simply execute the SQL query in the query analyser does it return you the record.

-ansuman sahu

[cmrhema]

If you simply execute the SQL query in the query analyser does it return you the record.

-ansuman sahu

Do try out as ansuman sahu said Usually this works well in SQL Query but I faced the same problem when I had to do that type of coding
I prefer you try out as below

IdRs.Open "SELECT MAX(IDN)  from expenses", DB, adOpenDynamic, adLockOptimistic
CIDN.Text = IdRs.Fields(0)+1

This worked for me.

[debasisdas]

Dear rave3050


The reason it did't work for u is so simple
u made some basic mistake

in your code
NewId is a column alias in SQL
but the NewId you are using in the next line is a VB variable.
and default value is 0
and when u say +1

it returns 1 .
the code teturns u, what u r asking it to do.

use the code as suggested by CMRHEMA

It will definately work for u.

[rave3085]

hey thnx to u and anusmansahu, this works for me too!

thnx a lot!