NEED HELP URGENT - VB6/ACCESS :( :(

[Corne]

I please need help with the following:

How can I use code to (automatically) retrieve the heighest number from a database.
(Display the retrieved number in a textbox)
(access)

I want to add one to the retrieved number and then store it back into the database.
(Creating my own autonumber field)

Can anybody help me with this!!!

Thanks.

[JorgeLedo]

You can make a query that returns a recordset ordered by the field with the number, goto the las record and retrieve the number. Then do your stuff.

If you need code reply here.

------------------
Jorge Ledo
[email protected]
Portugal

[LG]

Hi, Corne.
I am not sure, that I understood you. But to find the highest number you use MAX function:

sql = "select max(ProductId) from Products"

Data1.RecordSource = sql
Data1.Refresh
Text1 = Data2.Recordset(0)

If you don't have a data control, than you have to modify this code, but the idea is the same. To add 1 :
sql = "select max(ProductId)+ 1 from Products"

But the easyest way is just assign an AutoNumber field data type to the field that you need, and all this will be done automatically for you every time you add new record.

Larisa

dim rs as recordset
set rs = db.openrecordset("select count(*) as NumberOfRecords from table1")
'New value would be rs("NumberOfRecords")+1

------------------

Vincent van den Braken
EMail: [email protected]
ICQ: 15440110
Homepage: http://www.azzmodan.demon.nl

If you dont want to use a query, try this..

dim intnumber as integer ' default = 0

with db.recordset

.movefirst

While not .eof
if .fields(place field number) > intnumber then
intnumber = .field(place field number)
end if

.movenext

doevents
wend
end with

intnumber = intnumber + 1

now you have created a new id number
but watch out. if the database contains like
lots of records. the loop performens will be low.

[Clunietp]

LG's answer is the best answer thus far. Use that one

[Corne]

Thanks for help so far!!

My Code look like this so far.

Private Sub optSprings_Click()

'declare our variables
Dim dbProgram As New ADODB.Connection
Dim rsMain As New ADODB.Recordset

'open the database
dbProgram.Open "dsn=Program"

Set rsMain = dbProgram.Execute("Select
Max(Job_No) + 1 from Main")

txtLastJob.Text = rsMain("Job_No")

dbProgram.Close

End Sub

But if I run this I get the following run time error:

ADO could not find the object in the collection corresponding to the name or ordinal reference requested by the application.

PLEASE HELP!!!! ;(

[Clunietp]

when you use the MAX SQL function without specifying a AS NAME, it gets some funky name assigned by the database.

REPLACE

txtLastJob.Text = rsMain("Job_No")

WITH

txtLastJob.Text = rsMain(0)

Tom

[Corne]

Thanks for your help but i use this:

REPLACE

txtLastJob.Text = rsMain("Job_No")

WITH

txtLastJob.Text = rsMain(0)

Tom

i get an invalid use of null?

????

[LG]

Hi,Corne.
Try this:

txtLastJob.Text = rsMain!Job_No

Larisa