Hi

Looking for a bit of help. I have a request form that when a user opens a new request it populates the ref number straight away. What I'd like it to do it check the db to see what the last number was and increment it by 1 and that is the new ref number, they can then fill it out and submit it.

However there is also another part where if there was an original request that was rejected, a user can go back and re-submit that request with changes however they don't want it to overwrite the original request so what they'd like to happen is the user can search for their previous request and select it. What it needs to do then is take the original ref number, ie 1001 and add an a, b, c etc on the end to show it is a re-submitted request.

I thought about using auto increment in sql however as I've also got to user alphanumeric characters on the ref numbers I don't think this will allow it. I'm a complete newbie so any help would be greatly appreciated

Thanks

I'm sure there's lots of ways to do this. Here's one idea. Create a table with in SQL with an autoincrement column and a column to insert the users userid, [IDTable], so when the form opens you have the incremented value and its association to the user. When you save the form, save all of the data on the form to a second table [Requests]. The Requests table will have a column for the SubmissionID and a column to mark the resubmissions, Resubmissions, making both the SubmissionID and Resubmissions columns ints.

How many resubmissions are you allowed? The alphabet only has 26 letters and you could possibly run out. Whether there is a finite number of submissions or not you could build an alphabet table with an AlphaID column, int (increment). Make an entry with the ID of 0 with nothing in the Alphabet column before turning on autoincrement. That way on the first submission INSERT in the Requests table the AlphaID column will be 0. Successive submissions will find the matching row for the SubmissionID column. If you get a hit then you know this is a resubmit. Then you can add one (1) to the AlphaID column and insert the second (?) resubmission. When you display the reference number you can cast the SubmissionID as a varchar and concatenate the Alphabet column from the Alphabet table.

Hope this helps.

Richard

please refer following for identity creation
http://p2p.wrox.com/topic.asp?TOPIC_ID=67898

urt