Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2006

    I want to make a self generating ID field and other things

    I am making a database and I want to make a field to generate it's own data on a table, similar to auto number. But I want it to it to be letters and numbers using the first 3 letters from another field.

    For example

    for company “ABC” the ID will be “ABC1”, and if there is already a company called “ABC”, then the second “ABC” company will be called “ABC2” and company “AAB” will be “AAB1”

    The first 3 letters should be from the name of the company and the number is set it aside from other companies with the same first 3 letters.

    Can anyone help me with this problem? :cry:

  2. #2
    StepUP is offline Advanced Beginner
    Windows 7 64bit Access 2000
    Join Date
    Dec 2005
    Create an autonumber field, and then on the "Before Update" event of the form, add some code to concatenate the company ID with the autonumber field.


    Me.CompanyAndNumber = Me.CompanyID & Me.AutoNumField
    John F

  3. #3
    Join Date
    Sep 2006
    Manchester, England
    I don't like manual PK's myself so I avoid them but your code will need to be something like this :

    this will get you the next primary key number to insert :
    SELECT Max(mid( CompanyID ,4, len(contentid)-4)) + 1 as NewValue
    FROM tbl_Accounts
    WHERE CompanyID LIKE 'ABC%'
    new key would be
    strID = "ABC" & rs("NewValue")
    The mid function is getting the part of the company ID that is numeric. It assumes that it is always preceeded by three character.

    You may need to tweak this a bit.

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

Similar Threads

  1. Replies: 1
    Last Post: 10-28-2008, 08:38 AM
  2. noob prob, generating next report
    By flash319 in forum Reports
    Replies: 2
    Last Post: 08-04-2008, 03:10 PM
  3. How to make an autorefreshing?
    By Estyl in forum Access
    Replies: 2
    Last Post: 02-19-2008, 08:29 AM
  4. generating random numbers on form
    By anitra in forum Forms
    Replies: 1
    Last Post: 02-14-2006, 10:08 AM
  5. Replies: 1
    Last Post: 12-09-2005, 09:27 PM

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