none
MS Access Web App - Text + Random Number RRS feed

  • Question

  • I am working on an offender management database, and am stuck on creating a random Offender ID (not the primary key). What I would like is for new records to automatically generate a random offender ID (format: CCMS-00000), where they always begin with CCMS and then are followed by a dash and 5 random digits. I am a novice at this, but through research, I have built a validation rule that I thought would do the trick, but no....

    I am using:

    [Offender ID]="CCMS-"+Cast(Format(Rand()*Power(5,5),"00000"),Text)

    Can anyone help me figure out where I am failing?

    Thanks!


    • Edited by CCMS Chris Wednesday, July 22, 2015 6:10 PM
    Wednesday, July 22, 2015 6:10 PM

Answers

  • Hi CCMS,

    How do you create new records? If you create new records in datasheetview directly, I think you could use On Insert events in the table design, edit the filed you want to set as rand, something like below:

    But, in this way, if you create the records in Form list, the value of this filed will not show up until you save the records. If you want to show the random value when you click the new record button. Due to you could not custom the new button event, I suggest you add new button, in the new button click event, add NewRecord event, RunDataMacro to get the Random value, set the filed with the random value. Some like below:

    In the datamacro, something like below:

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, July 23, 2015 7:21 AM

All replies

  • Also, I have tried using "Number" and "Short Text" as the field type. Neither seemed to work.
    Wednesday, July 22, 2015 6:13 PM
  • Hi CCMS,

    How do you create new records? If you create new records in datasheetview directly, I think you could use On Insert events in the table design, edit the filed you want to set as rand, something like below:

    But, in this way, if you create the records in Form list, the value of this filed will not show up until you save the records. If you want to show the random value when you click the new record button. Due to you could not custom the new button event, I suggest you add new button, in the new button click event, add NewRecord event, RunDataMacro to get the Random value, set the filed with the random value. Some like below:

    In the datamacro, something like below:

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, July 23, 2015 7:21 AM