I'm having a brain fart when it comes to this new project that I'm working on and I'm hoping someone can enlighten me. Surprisingly, this is the first time I've had to do this with UltraDev.

My problem is that I need to auto create data in a field called Co_ID in my Access 97 table. This data is based on two other fields. The first part is the first three characters of a field called "BusinessType". The second field is from the ID field in an Access 97 table. Seems simple enough but I had to temporarily come up with a workaround for now until I could get it working the way it should.

Here's the work around. User inputs a new record and saves it. When the user pulls up the newly saved record on a different form (edit form), the form is tied into a Access Query. On this Access Query is where I'm combining the two fields into a display field called Company ID. In the query, the field is doing: Company ID: UCase(Left([BusinessType],3)) & Format([ID],"000000". When the user saves the information, the information from Company ID is stored into my table in the field called Co_ID.

My problem is that this is a two step process. Save the new record. Bring up the new record (with the query) and save it again so the CompanyID is updated. There's got to be a simpler and better way but my mind is blank.

Any help is greatly appreciated. Have a great 4th of July weekend!!

Ken

All you really need is the unique ID for the record that is generated in Access. I think there is a couple of methods and there is definetaly a tutorial somewhere on 4guysfromrolla or somewhere but one method i used was this....

create a field called Identifier (or whatever) in the Company table.

Now just before the record is created (in the code above the insert command) set a variable to a 12 digit totally unique randomized string of letters and numbers using a wee function. Insert this into the identifier field. Then just after the insert command open a recordset filtered by this identifier variable. it will retreive your record. Now concatenate your ID and Business type to make your CO_ID and insert it back to the database. This all happens as the record is created.

Hope this helps a little....

Cheers
Leed


:: Son, im Thirty.... ::

Similar to leed's post:

create a field which will store your composite number. create a recordset in ultradev with the following SQL:

SELECT CONCAT(BusinessType, ID) as composite_number.

simply create a form element (text box) in your UD form and set its default value to the contents of your concatenated recordset.

Although personally, i prefer to store the values in spearate fields, and simple concatenate them when you are pulling them out in forms. There is no need for the numbers to be physically stored together, you can always manipulate the data as you choose when you are pulling it out of the recordsets.

hope it helps etc.

------------------------
Poolio

MSN:

www.eliziumdesign.com

Thank you both. I re-evaluated this problem this morning after my vacation. I saw a simpler fix. There was no need to save this information into the table in the first place. By having the Access query combine both fields into the data I needed, all I really needed to do was display this temporary field from the query on the page. There was no reason I needed it to save into the table. Whenever I need to display this, I'll just link to to the query.

It's amazing how a little vacation can clear up the brain. <img src=../images/dmxzone/forum/icon_smile_big.gif border=0 align=middle>

Thanks again!