Find highest value in specific field in recordset obtained with SQL error

Hi I have succesfully set a textbox to the value of a field in a recordset obtained using SQL. so far so good.
But I want to find the highest value of all that fldOrderID and add one to it, so I can use that as a New orderID

this worked

frmNewOrder:
Call GetOrderMaster
txtOrderID = MR_OS.grstCurrentRS![fldOrderID]

standard form MR_OS:
Public Sub GetOrderMaster()

'use the glngCustomerID assinged in SetCurrentRecord to select the tblOrderMaster in a select
Set grstCurrentRS = gdbCurrent.OpenRecordset("Select * from tblOrderMaster where fldCustomerID like " & "'*" & glngCustomerID & "*'")
End Sub

the textbox shows the value of fldOrderID of the current record of the recordset.
Question: is the current record the last record in the recordset by default?

Anyway so that is working so I tried to add the abilty to find the highest value in records of the field fldOrderId and add one to it and use that as a New OrderID. so I added a global variable glngOrderID in the standard form MR_OS changed the Sub GetOrderMaster() to this

Public Sub GetOrderMaster()
glngOrderID = 0
'use the glngCustomerID assinged in SetCurrentRecord to select the tblOrderMaster in a select
Set grstCurrentRS = gdbCurrent.OpenRecordset("Select * from tblOrderMaster where fldCustomerID like " & "'*" & glngCustomerID & "*'")
'find the highest value in the fldOrderID feild and add one to that to be used as the new OrderID
'loop through the current recordset checking the fldOrderID values and set a variable to the highest value
Do Until grstCurrentRS.EOF = True
If grstCurrentRS.Fields("fldOrderID").Value > glngOrderID Then
glngOrderID = grstCurrentRS.Fields("fldOrderID").Value
End If
grstCurrentRS.MoveNext
Loop


End Sub

now when I run it I get "No Current Record" and it points to

txtOrderID = MR_OS.grstCurrentRS![fldOrderID]
in the frmNewOrderID

I want to change that eventually to
txtOrderID = glngOrderID
after finding the highest order number in the fldOrderID field in the current recordset and adding one to it, but why do I not have a current record now?

Use the SQL statement:

Select Max(fldOrderID) from tblOrderMaster where fldCustomerID like " & "'*" & glngCustomerID & "*'"

This will give you only the record with the highest value in the "fldOrderID" field for the customer(s) that the variable glngCustomerID contains their number.

Good Luck!!!

Great I'll try that thx :)

hmm Ok now im confused

Now I get an error saying Item not found in record
with this

frmNewOrder:

Call GetMaxOrderID
txtOrderID = MR_OS.grstCurrentRS![fldOrderID]

Standard form MR_OS:

Public Sub GetMaxOrderID()
'Get the max OrderID value with a Select statement
Set grstCurrentRS = gdbCurrent.OpenRecordset("Select MAX(fldOrderID) from tblOrderMaster where fldCustomerID like " & "'*" & glngCustomerID & "*'")

The error points to
txtOrderID = MR_OS.grstCurrentRS![fldOrderID]
in frmNewOrder

But this works showing the fldOrderID value from the current record in the recordset in the text box.

frmNewOrder:

Call GetMaxOrderID
txtOrderID = MR_OS.grstCurrentRS![fldOrderID]

Standard form MR_OS:

Public Sub GetMaxOrderID()
'Get the max OrderID value with a Select statement
Set grstCurrentRS = gdbCurrent.OpenRecordset("Select * from tblOrderMaster where fldCustomerID like " & "'*" & glngCustomerID & "*'")
End Sub


The only difference is
MAX(fldOrderID) vs * in the select.
obviously fldOrderID is in the tblOrderMaster or neither one would work.. so I dont see why the first one wont find MAX(fldOrderID) from tblOrderMaster

the fldOrderID is of data type long

[Edited by VB_Sponge on 04-22-2000 at 06:47 AM]