Results 1 to 6 of 6

Modifying the AutoNumber in Data Type in forms

  1. #1
    XiaoXiao is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2017
    Posts
    3

    Modifying the AutoNumber in Data Type in forms

    Good day!

    I would like to hear and ask your genius minds...

    I did some simple database for our Vouchers track recording...


    One thing that makes me stuck at this moment, modifying the format the auto number data setting

    Example

    our tracking ID number is "PMS-mm-dd-####"
    mm - numerical digit for this current month
    dd - current date
    #### - the auto numbering;

    i want to modify the format for data setting of autonumber that will display the current date and month same as the format above and also auto numbering


    please help me guys.. thanks

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    5,138
    dont modify the autonumber, leave as is,
    but in a query, you can create the "PMS-mm-dd-####".

    Tracking: 'PMS-' & format(MONTH([EntryDate]),"00") & "-" & format(day([EntryDate]),"00") & "-" & [autnum fld]

  3. #3
    ArviLaanemets is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Apr 2017
    Posts
    54
    Do you want to create an unique ID in this format for a new record? When yes, then I have very similar solution for our IT Devices database, where the DeviceID is in format 'CCYYYYMMDDnnn'.
    'CC' - device group code;
    'YYYYMMDD' - the date of purchase/registering;
    'nnn' - '001' is first device of this device group registered on this date, '999' id 999's device of same group registered on same date.

    In forms OnCurrent event, I'm checking for new record. When New Record, all controls on form are disabled, and a text box for registering date (by default with current date) together with 'Register Device' button, usually invisible, are made visible. When 'Register Device' button is clicked, the new DeviceID is calculated and entered into DeviceID field, all other controls are enabled, and registering date textbox and 'Register Device' button are made invisible again.

    To calculate the new DeviceID, I have 2 hidden unbound text boxes on form (I have a unbound control on Main form to link the device form with certain device group).
    txtCalcDevice1.ControlSource = '=[cbbParentDevGroup] & Format([txtRegDate];"yyyy") & Format([txtRegDate];"mm") & Format([txtRegDate];"dd")'
    txtCalcDevice2.ControlSource = '=CInt(Nz(Right(DMax("DeviceID";"tblDevices";"LEFT (DeviceID,10)='" & [txtCalcDeviceID1] & "'");3);0))'

    The new DeviceID is calculated as
    Me.txtDeviceID = Me.txtCalcDeviceID1 & Format((Me.txtCalcDeviceID2 + 1), "000")

    I think it is easy to modify this for your needs!

  4. #4
    XiaoXiao is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2017
    Posts
    3
    Where do i put the code? at the expression builder?

  5. #5
    XiaoXiao is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2017
    Posts
    3
    thanks... ill try your suggestion

  6. #6
    ArviLaanemets is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Apr 2017
    Posts
    54
    Form's OnCurrent event controls, is the button visible and other field controls disabled/locked (when New Record) or opposite (when not New Record);
    Button's OnClick event writes new DeviceID into textbox txtDeviceID, and after that hides the button and enables/unlocks other field controls.

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

Similar Threads

  1. Replies: 8
    Last Post: 12-14-2015, 06:02 AM
  2. Replies: 2
    Last Post: 12-04-2013, 07:09 PM
  3. Update access Forms and data type
    By Amen in forum Access
    Replies: 4
    Last Post: 09-25-2013, 04:49 PM
  4. Replies: 2
    Last Post: 03-19-2013, 08:36 PM
  5. MOdifying forms in split database
    By Ignace in forum Access
    Replies: 1
    Last Post: 05-11-2012, 05:36 AM

Tags for this Thread

Posting Permissions

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