[RESOLVED] Having a 'max' issue

I have this code, which works: tmpBOL is incremented

VB Code:

'find the next available number
'=====================================
Set rsBOLHeader = New ADODB.Recordset
'=====================================
sql = "SELECT [BOL] FROM BOLHEADER ORDER BY [BOL]"
rsBOLHeader.Open sql, CnxnTechSQL, adOpenKeyset, adLockReadOnly, adCmdText
If Not rsBOLHeader.BOF And Not rsBOLHeader.EOF Then
    rsBOLHeader.MoveLast
    tmpBOL = rsBOLHeader![bol] + 1
End If
rsBOLHeader.Close
Set rsBOLHeader = Nothing
'=========================

However, when I do this, tmpBOL is always 1.

VB Code:

'find the next available number
Dim lastbol As Long
'=====================================
Set rsBOLHeader = New ADODB.Recordset
'=====================================
sql = "SELECT max([BOL]) as lastBOL FROM BOLHEADER "
rsBOLHeader.Open sql, CnxnTechSQL, adOpenKeyset, adLockReadOnly, adCmdText
tmpBOL = lastbol + 1 
rsBOLHeader.Close
Set rsBOLHeader = Nothing
'=========================

Obviously a syntax error, but my documentation doesn't clear it up for me.

I'm trying to 'optimize' some code by doing away with unecessary loops.

the lastbol variable you have isn't affected by the SQL at all...

VB Code:

tmpBOL = rsBOLHeader.Fields("lastBOL").Value + 1

That worked. I have to "digest" it a bit. I really didn't notice any difference in the speed of the data fetching. Is it basically doing the same thing (behind the scenes) in either case?

The ONLY difference is that you used the value of a local variable named lastbol, while Bush used the value of the field in recordset rsBOLHeader named lastBOL. There's no difference in how long it takes to fill the recordset, because the code is the same (rsBOLHeader.Open sql, CnxnTechSQL, adOpenKeyset, adLockReadOnly, adCmdText) in both cases.