Return Max of field in recordset

[ohmbru2]

I need help with this code:

Set db = CurrentDb()
query = "SELECT max(tblLicensingNotes.notesid) FROM tblLicensingNotes"
Set rs = db.OpenRecordset(query)
intNotesID = rs![NotesID].Value + 1

It errors on teh last line: Object variable or With block variable not set (Error 91)

 

[AlexCuse]

what exactly is intNotesID?

Also, did you mean to post this in the VB or Access forum?
Where are you connecting to SQL server?
Have you declared rs as a new recordset?

You don't really give enough there for anyone to see your problem.

Good lUck,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[bborissov]

Why I think this is a question to some other forum
The forum for your FrontEnd, but try:

Set db = CurrentDb()
query = "SELECT max(tblLicensingNotes.notesid) AS NotesID FROM tblLicensingNotes"
Set rs = db.OpenRecordset(query)
intNotesID = rs![NotesID].Value + 1
--- or intNotesID = rs[NotesID].Value + 1

I am not sure that this will be works, because I don't know the syntax for your frontend.



Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

Sorry I didn't explain the code. I suppose you have the error because you didn't give a name to the MAX(..) field. RecorSet didn't receive the name and you can't access it through name.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ohmbru2]

This is VBA in an Access frontend connected to SQL.

I will search the Access forums as well.

Thx