Button to complete a field and save record

[Davince]

Hi all, I've not used access in about 15 years so am REALLY rusty (and a lot seems to have changed!)

What I would like to do is this:

A form is attached to a table. In the table is an ID field (autonumber) a Surname field (text, no special characters allowed) and a Client Registration field

When completing the form, I want the only way to save the record to be by pressing a button. Before it saves the record though, I would like on-click to automatically take the first three letters of the surname, add the autonumber after that, and input that into the field 'Client Registration'. Whats the most efficient way?



Also, if possible, I'd have preferred autonumber to start at 1000 rather than 1, so that the Client Registration field is abc0000 format. Any tips on that?

Many thanks!

[June7]

Bing: Access autonumber seed

http://stackoverflow.com/questions/2...utonumber-seed

However, don't really need to start at 1000 to show 4 digits. Can use Format() function. Example:

Format(1, "0000")

That will show result: 0001

Why bother saving the calculated ID into table? It can be calculated when needed.

So, if you ever exceed 9999, can adjust calculation to 5 digits.

[Davince]

Thanks June7. The calculated field is used to reference other data both inside the database, in another system (which requires first three letters plus 4 numbers) and in paper records, so it will be printed on everything and filed (physically) by that reference. I don't *have* to have it autonumbered really (just want to because one user will forget it otherwise...) but I do need it saved. Likelyhood of it ever going over 9999 is... less than 0001

[June7]

My comments still hold. Can use concatenated value as easily as saved value for what you describe but save if you want.

Generating custom unique identifier is a common topic in forum.

Supposedly autonumber can generate negative but I have never seen this.

[chloe]

I use the following code in the even procedure section of my button to save and clear the form for the next entry:

DoCmd.GoToRecord acDataForm, "DatabaseName", acNewRec