Using the Primary Key as part of another field

[injanib]

This might be a tricky one, or not.



I have a table with an autonumber Primary Key. Then I have a form that simply shows the records in this table. One of the field in this table is a text field which I like to have automatically generated. The field value will start with an "S" followed by "0000" and then the value of the autonumber primary key field.
So if the primary key for the record is 1, then the text field would be "S00001".

I could have done this if I used the form in Data Entry mode. But I don't. I enter data into this table using an unbound form, and using the following:

Set rst = CurrentDb.OpenRecordset("TableName")

With rst
.AddNew

!FieldName = Me![ControlName]
'and so on

.Update
.Close
End With
Set rst = Nothing

Using this method, update query, or any other method other than using the bound form with DataEnty mode; how can I have the value of this text field generated as explained above?

I know this is an easy one for you geniuses out there.

Thanks.

[clean32]

in your auto number field set the format? to 0000000. this will display the the numbers as 0000001 000002 etc etc etc

then in the form it will be setvalue "S"&[form.formname.id] just make sure that the field you are putting this in is a text or not a number field

[injanib]

Thanks for the suggestion. I forgot I could set the format of the the autonumber to that. However, when I reference it on the form to set up the value of another field from it, Access drops the leading zeros. So I have to use the format statement where ever I want to use its value with the zeros. Your suggestion gave me a different idea, with which I ended up restructuring my tables a bit. It was a bit work, but I like it better this way.

Thanks.

[clean32]

well then doint change the format in the tables, just change it in the forms or where ever the user will see it or print it