Create a Random Number to be Automatically Generated as a Default Value for a Record's ID

[travisinblue]

Hello and thank you for your time and assistance,

I've been building a CRM in Access that allows for creating profiles for our accounts. When we add a new account, I'd like an Account ID to automatically generate and look like this: CRMXXXXX where the X's represent a random number. I originally wanted to set the field as autonumber to just count up, but unfortunately I need to append this value to another sales database we have; you can't store the text "CRM" with an autonumber in a table.

Right now, all I've done is place an equation in the default value of a textbox I have on my 'Add Account' form. I then made the control source of the text box the Account ID field. My equation is as follows:
="CRM" & CStr(Int(Rnd(Now())*10000))

This appears to work, as it generates a value in the correct format. However, I've realized that each time I open the CRM, the random numbers start over again and I run into issues of trying to create a new record with the same ID as a previously created record. I assume I need to incorporate some sort of timestamp to it in order to change it.

There could be an entirely different and better way of doing this so please let me know your thoughts. I appreciate any and all ideas you may have.

Much thanks!

[ranman256]

Just use AUTONUMBER, and IMPLY that the CRM is there.

select "CRM" & [tblID] as AcctID, * from table

[travisinblue]

Thanks for the reply, and my apologies for being a novice. So I believe you're saying to combine the letters 'CRM' with the autonumber field in a the new, AcctID field. Where would I insert the select query?

[ranman256]

in its own field in the grid. "CRM" & [tblID]
The above code should work with your table /field names.

[ItsMe]

One approach may be to use the Format property of the Autonumber field. You can try it at the table level or everywhere the Autonumber is displayed, e.g. Forms and Reports.

here is the syntax
>"CRM"

[travisinblue]

Ended up just typing the following in the default value property on the form that creates a new account:

="CRM" & Format(Now(),"mssh")

It generates what appears as a random number that's based on the date and time and changes every second.

Just thought I'd post it in case anyone else wanted something like that. Thanks for the replies!