select statement not working

[Yang Lee]

Hi all,
I am using access and in that I link
the tables from oracle. I create
a form and in that i have a text field e.g. emp_no
this emp_no field should take the max value from
table emp while adding the record.
i use in one of the event functions

DoCmd.RunSql "select max(emp_no) from emp"

but its not working.
what i want is to take the result of this querry
in a variable and display it in this text field.

how to achieve this.
in this program i am not opening any database
the tables are linked from oracle.
please help.
thanks
lee.
--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG

[Sonotronic]

Yang,

you can not do that. You may either use a recordset or a function. The
function is easier but slower, it will be:

varMaxEmpNo = Dmax("emp_no";"emp")

Regards,

Valentín
**********************************************
"Yang Lee" <ya*******@ausi.com> escribió en el mensaje
news:33************************************@mygate .mailgate.org...

Hi all,
I am using access and in that I link
the tables from oracle. I create
a form and in that i have a text field e.g. emp_no
this emp_no field should take the max value from
table emp while adding the record.
i use in one of the event functions

DoCmd.RunSql "select max(emp_no) from emp"

but its not working.
what i want is to take the result of this querry
in a variable and display it in this text field.

how to achieve this.
in this program i am not opening any database
the tables are linked from oracle.
please help.
thanks
lee.
--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG

[Salad]

Yang Lee wrote:

DoCmd.RunSql "select max(emp_no) from emp"
RunSQL is great for append/update/delete/maketable queries, not so hot
for Select queries.

Dim rst As Recordset
set rst = Currentdb.openrecordset("select max(emp_no) As MaxNum from
emp",dbopensnapshot)
msgbox "Max Number is " & rst!MaxNum
rst.close
set rst = Nothing



but its not working.
what i want is to take the result of this querry
in a variable and display it in this text field.

how to achieve this.
in this program i am not opening any database
the tables are linked from oracle.
please help.
thanks
lee.

--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG