[RESOLVED] syntax error "select max(fields) from table"

[ksuwanto8ksd]

I have a table "ORDER" database access 2k its structure is:

orderid = autonumber
orderdate= date
supplierid=text
suppliername=text

in my form have a textbox name "txtnota" and a command button click event code:

Private Sub command1_Click()
    call RsTmp
end sub
 
 
Sub RsTmp()
Dim RsTmp As ADODB.Recordset
 
msql = "select * from order " & _
       "where orderid in (select max(orderid) from order)"
[b]Set RsTmp = con_cashReg.Execute(msql) 'error here[/b]
If Not (RsTmp Is Nothing) Then
   If (Not RsTmp.BOF) And (Not RsTmp.EOF) Then
      txtnota.Text = RsTmp.Fields("orderid").Value + 1
   Else
   txtnota.Text = 1
   End If
   RsTmp.Close
End If
End Sub

the run time error is:
"syntax error in FROM clause"
Im regard it can get max number from "OrderId" field and add 1 to it
i dont know what wrong with this, If any one can help me is very much appreciated. thanks in advance

best regard