By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,591 Members | 629 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,591 IT Pros & Developers. It's quick & easy.

How About the MAX() function

P: 27
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 ::::

Expand|Select|Wrap|Line Numbers
  1. IdRs.Open "SELECT MAX(IDN) as NewId from expenses", DB, adOpenDynamic, adLockOptimistic
  2. NewId = NewId + 1
  3. 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.
Apr 27 '07 #1
Share this Question
Share on Google+
4 Replies


100+
P: 149
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 ::::

Expand|Select|Wrap|Line Numbers
  1. IdRs.Open "SELECT MAX(IDN) as NewId from expenses", DB, adOpenDynamic, adLockOptimistic
  2. NewId = NewId + 1
  3. 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
Apr 27 '07 #2

100+
P: 375
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
Expand|Select|Wrap|Line Numbers
  1. IdRs.Open "SELECT MAX(IDN)  from expenses", DB, adOpenDynamic, adLockOptimistic
  2. CIDN.Text = IdRs.Fields(0)+1
This worked for me.
Apr 27 '07 #3

debasisdas
Expert 5K+
P: 8,127
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.
Apr 27 '07 #4

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

thnx a lot!
Apr 27 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.