I have an insert record form in my site .. works sweet no probs.. I need the page that it goes to to pull the info from the recordset of the record just inserted, I can get it to do this by setting record set to sort descending by the primary key field ... but what if ( the remote chance that it happens) that the last record inserted is not the record that the current user inserted .... is there a way to pass the data so that it is the new record inserted seeing that the record hasn't been created yet to pass the variable ... ???

Hope this makes sense.

Cheers, Ryan

If you are using SQL Server 2000 you can predetermine an autonumber field, take a look here:
www.sqlteam.com/item.asp?ItemID=319

If you are using access the best solution is to generate your own unique record identifier for situations like creating orders where this is necessary, here's some code I wrote to generate a unique reference number:

<%
If Request("Submit" <> "" Then

set rsRefNumber = Server.CreateObject ("ADODB.Recordset"
rsRefNumber.ActiveConnection = MM_YourConnection_STRING
rsRefNumber.Source = "SELECT RefNumber FROM tblRefNumber"
rsRefNumber.CursorType = 0 ' adOpenForwardOnly
rsRefNumber.CursorLocation = 2 ' adUseServer
rsRefNumber.LockType = 2 ' adLockPessimistic
rsRefNumber.Open
varRefNumber = rsRefNumber.Fields("RefNumber".value
rsRefNumber.Fields("RefNumber".value = varRefNumber + 1
rsRefNumber.Update
rsRefNumber.Close
rsRefNumber = Nothing

varRefNumber = "REF" & varRefNumber

End If
%>

In the database I have set up a table (tblRefNumber) containing 1 field (RefNumber) with a data type of numeric, to start of I entered a value of 0 in the field. This code then creates a recordset pulls the value held in the RefNumber field and stores it in a variable (varRefNumber). Then it adds 1 to the value, updates the field and closes the recordset.

Note the LockType = 2, this means that only a single user can use and update the table at any one time, so even if two people try and place an order at the exact same time they are processed one after the other and the uniqueness of varRefNumber is maintained.

Now I can use the unique varRefNumber to track an order or whatever.

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

Cheers .... thanks for the help .... will try this tonight ....

Ryan

Cheers, Ryan

Owen...

Your post looks useful because I am attempting the same thing: I want to insert a record with a form, and, upon submitting, go to a "details" page that will show the data as entered. Then the user can confirm or go back and edit.

But I'm a real newbie to the ASP stuff, so I have a couple of questions...

Where do you place the code in relation to the Insert Record behavior?

And can I just replace the ID which is an AUTONUM field in my table with your RefNum field? Are you saying that your RefNum would autoincrement from the largest number already in the table?

Thanks...

Place the code before the Insert Record behaviour, modify the insert record code to pick up varRefnumber as well as the fields values.

The table tblRefNumber only contains one field and one record, the code above reads the current record value assigns it to the variable varRefNumber to provide a unique record number for the table into which you are going to insert the form fields. It then adds 1 to to the variable and updates tblRefNumber with this number ready for the next time, so yes, this completely replaces the Autonumber field in the second table.

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo