Access Question RRS feed

  • Question

  • How to use a column field values to automatically populate the column values. Example ( column one-room # 4089D to automatically create a value in this format...49504039D901576.) And what ever number in the primary column the next number is created. The next room # is 4040..The next value created in the next column should be 495004090901577. The data was imported from Excel Can something like this be done.

    Room Number Equipment #

    4040 495004040901855


    Wednesday, August 24, 2016 2:51 PM

All replies

  • Hi. What is the significance of these numbers? Can you give us the "rules" to generate them? In other words, how does 4089D equates to 49504039D901576 and 4040 equates to 495004090901577? Thanks.
    Wednesday, August 24, 2016 3:03 PM
  • If you need to create a number series that have no correlation you can make an array that maps the different columns to the desired values:

    Column Value  |  Corresponding value
    # 4089D         | 49504039D901576
    # 4040           | 495004090901577

    Then you use VBA to map the values.

    Best regards, George

    Wednesday, August 24, 2016 3:10 PM
  • I'm assuming there are typos in your post as the room numbers in your first paragraph do not appear to have any correlation with the equipment numbers.  I'm assuming that the first row in your sample data is correct in that 4040 is an element within the  equipment number.

    Redundancy should not be introduced into a table by duplication of values as this is not only unnecessary, but more importantly leaves the database open to the risk of update anomalies.  So the RoomNumber column's value should not be an element in the EquipmentNumber column's value.

    You do not say what the first part of the EquipmentNumber, 49500 in your example, signifies, but if this is derived from other data than the same applies, and this should not be stored in the EquipmentNumber.

    This leaves us with the final element, 901855 in your example.  It appears that you merely want to increment this by 1 when a new row is inserted into the table.   This is done by looking up the current MAX value of the column, and adding 1.  However, this can give rise to conflicts in a multiuser environment as, until one user saves the new row, all users currently inserting a new row will get the same number.  By making the column the primary key of the table or otherwise uniquely indexing the column an error will be raised when a second user attempts to save their row if one of the other users have already done so.  The error can therefore be handled and the number adjusted accordingly.

    You'll find an example in CustomNumber.zip in my public databases folder at:


    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the example for 'Sequential Numbering' is the appropriate model to follow.  This computes a ProductID value when a row is inserted into the table via a form.  For batch insertions or updates see the code in the Click event procedure of the 'Insert Rows' button in the 'Insert Multiple Rows' form.

    When you need to return the full structured number it is a simple matter of concatenating its constituent elements from the RoomNumber value, whatever data the first part of the structured number is derived from and the sequential values generated.  This can be done in a computed column in a query or a computed control in a form or report.

    BTW, do not use spaces or special characters like the # symbol in table or column names.  Use CamelCase or represent a space by an underscore character like_this.

    Ken Sheridan, Stafford, England

    • Edited by Ken Sheridan Wednesday, August 24, 2016 6:28 PM Typo corrected.
    Wednesday, August 24, 2016 6:26 PM
  • Hi Lemarie,

    First of all I want to say that your description of the thread is little confusing and community members are not able to understand your issue clearly.

    you had mentioned the Room number and Equipment no.

    How they are related with each other?

    in the description you had mentioned that Room no is 4089D in  49504039D901576.

    in the example you had mentioned like below.

    Room No is 4040 in 495004040901855.

    you can see that in the description there is Alpha numeric characters and in the example it is only numeric.

    How to distinguish them and find the correct room number?



    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, August 25, 2016 1:58 AM