getting ident_current into a vb variable

If you're adding the record via the ADO recordset Update method, then after you call Update, the record just added becomes the current record.  So you can get its primary key by simply setting the appropriately-typed variable equal to it, i.e.,
rs.AddNew
rs("field1") = "SomeValue"
rs("field2") = "SomeOtherValue"
...
rs.Update
SomeVariable = rs("PrimaryKeyField")

Thus, there is no need to try to call directly to SQL to get the ID of the just-added record.

Of course, if you're working in VB .Net, you will need all the .Value properties since .Net doesn't support default collections/properties.

here is the call that I am using for the database.
Dim cn As ADODB.Connection
   Dim cmd As ADODB.Command
   Set cn = New ADODB.Connection
   cn.CONNECTIONSTRING = CONNECTIONSTRING
   cn.Open
   Set cmd = New ADODB.Command
   cmd.ActiveConnection = cn
   cmd.CommandText = strSqlInsert
   cmd.CommandType = adCmdText
   Set rs = New ADODB.Recordset
   rs.Open cmd, , adOpenKeyset, adLockOptimistic

if I then call  variable = rs("Order_ID").value  I get an error

Oh, I see what you're asking now.  So your strSqlInsert is something like
"INSERT INTO myTable .... "
and so you want to run it and find out the value of the last ID generated by that INSERT command.

I thought you were updating the recordset directly as described above.

I have two ideas:
1.  You could immediate open another recordset by EXECUTEing a COMMAND against your connection with command text = "SELECT IDENT_CURRENT('TableName')" then read the value of NewRS(0).  
2.  You could do something like DMax("IDFieldName","TableName") which will retrieve the maximum value in the field.

Either of these methods might NOT return what you expect in a high-volume environment:  what happens if someone else's changes append the instant between yours and you call to get the last ID?  You'll get someone else's last inserted record id.

Without changing to the method I referred to earlier, where you retrieve your new records then insert them into the new recordset one at a time, I'm not sure you to get the lastest primary key generated by your specific process with absolute certainty.

My suggestion 1 above assumes you're working against a MS SQL server:  IDENT_CURRENT is a SQL-specific funciton.

I think I have come up with a solution
strSqlInsert = "DECLARE @id as int  insert into orders (numb_Pages,print_Type, page_Layout, bleed_Option, paper_type, proof, ins, amount, doc_ID, customer_ID) Values('" & intNumbPages & "','" & strPrintType & "', '" & strPageLayout & "', '" & blnBleedOption & "', '" & strPaperType & "' , '" & strProof & "', '" & strInsert & "', '" & intAmount & "', '" & intDocID & "', '" & strCustID & "'" & ") select  * from orders where Order_ID = @id"

let me know if you think that will work.
after getting your feedback i will accept it as the answer

thanks
shayne

I mean I will accept your comments as the answer and award the points to you

shayne

Very nice. Great suggestion. I have increased the point value for the added help
shayne

Thanks!  Feel free to add comments here if you need anything more with this issue.