Results 1 to 13 of 13
  1. #1
    clifton11221 is offline Novice
    Windows 10 Access 2003
    Join Date
    Nov 2020
    Posts
    5

    Auto Add A Unique Value To A Field On A Form

    I'm attempting to auto add a unique value to a text field [TktNo] via a Form. I'm using VBA code to accomplish this. Below is the code...


    In the code, I'm taking the first character from the current date [DateCreated], the first character from the user that created the record [CreatedBy] and I'm adding the Unique ID for the record [RefNo] (all fields in the same table).

    The [DateCreated] field is automatically generated via "DateNow()". Likewise, the [CreatedBy] field is automatically added via "CurrentUser".

    The problem that I have is that the code is placed in the "Before Update" of the Form which results in the TktNo being updated/changed everytime a user updates the record - and I do NOT want the TktNo to be updated (ever).

    I tried adding the code to "Before Insert" but then the Ref No and Date Created info isn't available.



    Please help... I should also note that I do NOT know how to write VBA code. I'm open to alternative methods to adding a unique value to the TktNo field that would not require me to make major changes to the DB or table structure.

    Code:
    TktNo.Value = VBA.Left(DateCreated.Value, 1) & VBA.Left(CreatedBy.Value, 1) & RefNo.Value

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    on a form, use Now() will give a unique value to the second.
    if you are saving it to a table use AUTONUM and it will make the value when you save.

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    See if you can adapt this. You only want to update the field when a new record is created. The below action "NewRecord" is not shown in the action dropdown but it works if you type it in.

    Click image for larger version. 

Name:	newrec.png 
Views:	26 
Size:	10.9 KB 
ID:	43378
    Last edited by davegri; 11-04-2020 at 10:08 AM. Reason: clarif

  4. #4
    clifton11221 is offline Novice
    Windows 10 Access 2003
    Join Date
    Nov 2020
    Posts
    5
    Correct, I only want to update the field when a new record is created. The only problem with your suggestion is the DB is in Access 2003. Do you know how I should add the IF Statement to the Macro for the 03 version?

    Quote Originally Posted by davegri View Post
    See if you can adapt this. You only want to update the field when a new record is created. The below action "NewRecord" is not shown in the action dropdown but it works if you type it in.

    Click image for larger version. 

Name:	newrec.png 
Views:	26 
Size:	10.9 KB 
ID:	43378

  5. #5
    clifton11221 is offline Novice
    Windows 10 Access 2003
    Join Date
    Nov 2020
    Posts
    5
    I'm not sure I fully understand. Should I use Now() for the TktNo field? I don't want the TktNo to display as a Date. It could use the numbers of the date, that's fine but not a date format. I should also note that the DB (and field) already posses thousands of records of data.

    Yes, the data is saving to a table.

    Quote Originally Posted by ranman256 View Post
    on a form, use Now() will give a unique value to the second.
    if you are saving it to a table use AUTONUM and it will make the value when you save.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Perhaps Ranman forgot to tell you to format the table field as data type of single.
    EDIT - forgot to say that you'd need several decimal points to see the difference. Rather than monkey around with Time, Timer, Time$ or Now I'd recommend using an autonumber if you don't care about it being exactly sequential and it doesn't contain text.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Whats the purpose of this field? Whats the purpose of TktNo?
    What do you want the data to look like?
    There are many ways to create a unique field but it would help to know what its for.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Do you know how I should add the IF Statement to the Macro for the 03 version?
    Sorry, I do not.

  9. #9
    clifton11221 is offline Novice
    Windows 10 Access 2003
    Join Date
    Nov 2020
    Posts
    5
    Quote Originally Posted by moke123 View Post
    Whats the purpose of this field? Whats the purpose of TktNo?
    What do you want the data to look like?
    There are many ways to create a unique field but it would help to know what its for.
    The DB is used to manage copy jobs for a reprographics center. The TktNo (Ticket Number) field is the Job Ticket Number. The ID Number used to be a sequential ID on a physical form that had to be ordered. The staff formerly typed the Ticket Number off of the form into the database (in addition to the other information about the copy job).

    Now instead of using the physical form, we want the DB to create the TktNo automatically.

    We can't go with the option of turning the TktNo text field to a Number field because we're talking about thousands and thousands of records already in the system. And it's my understanding that you can't turn a text field into a number field (unless I'm mistaken).

    If the Ticket Numbers can look something like TKT30032 that would be fantastic!!!

    As previously stated, the number have to be unique (that's why I was including the RefNo (AutoNumber/Primary Key) as part of the TktNo).

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You can alter a field data type if the value is acceptable to the new type. So yes for 123 but not for A123.
    Perhaps some examples of what a succession of these numbers has to look like would help.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    If the Ticket Numbers can look something like TKT30032 that would be fantastic!!!
    Bear in mind you can make the ticket number look like anything you want. It does not necessarily have to be stored as TKT30032. You can just store
    30032 and wherever you need to display it you just concatenate the "TKT" to the number.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  12. #12
    clifton11221 is offline Novice
    Windows 10 Access 2003
    Join Date
    Nov 2020
    Posts
    5
    I solved my issue by using the below code "Before Update" in the Form. I'm using the If Statement to update the field if the field is null, if not, leave it as is.

    If the field is null, I'm combining the first character of the Date (using "Now()"), the first 2 characters of the Staff Name that created the record (using "CurrentUser()"), the first character of the Job Title, and adding the (auto number) Ref Field. An example would be: 1KNH107296

    Code:
        If IsNull(TktNo.Value) Then
        TktNo.Value = VBA.Left(DateCreated.Value, 1) & VBA.Left(CreatedBy.Value, 2) & VBA.Left(JobTitle.Value, 1) & RefNo.Value
    End If

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    FYI - you don't need .Value because it is the default property. Nor do you need VBA so this should be the same...
    Code:
    If IsNull(TktNo) Then TktNo = Left(DateCreated, 1) & Left(CreatedBy, 2) & Left(JobTitle, 1) & RefNo
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 20
    Last Post: 09-09-2020, 10:31 PM
  2. Unique Value and Auto Populate
    By jlgray0127 in forum Programming
    Replies: 1
    Last Post: 04-11-2018, 02:36 PM
  3. Auto-Increment a New Unique ID
    By Mellen105 in forum Forms
    Replies: 5
    Last Post: 04-07-2014, 04:15 PM
  4. Replies: 1
    Last Post: 07-22-2013, 10:17 PM
  5. Replies: 2
    Last Post: 06-01-2013, 07:00 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums