Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    New Lounger
    Join Date
    Feb 2005
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    CREATING OWN AUTO NUMBER (2000)

    Hello all
    I have a key identifier for different people entering data for eg MMTC 001/05 for their first record for 2005
    Can i code so that when they create a new record it will automaticly populate the field with MMTC 002/05

    Thank you Ron Don

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: CREATING OWN AUTO NUMBER (2000)

    Yes, you can do that, but it involves a fair bit of VBA and the use of either a special table for each person that contains the next record to be added, or the use of the Max function, which might be easier in your case. Your bigger challenge however is to identify the person entering the data - there you have two choices. One is to use the Access User Security feature which requires each user to login, or you have to build your own login system to track users (which involves lots of work and will never be as secure as the builtin security feature).

    You might consider another alternative of putting a timestamp (using the Now() function) in each record that is created when the record is entered. That plus using the CurrentUser() function would let you determine the sequence in which records were entered. It would of course be dependent on the accuracy of the individual workstation clocks, so that might be an issue.

    If you would like further details on either approach, please post a response, and we'll do our best to help.
    Wendell

  3. #3
    New Lounger
    Join Date
    Feb 2005
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CREATING OWN AUTO NUMBER (2000)

    I am not so concern who actully entered the data more that a unquic record is created by updating the prefix they enter for eg first record MMTC001/05 next record MMTC002/05 etc can i automat this process

    thanks ron

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CREATING OWN AUTO NUMBER (2000)

    Do you want the user to enter a prefix (such as MMTC) in a text box, then press a command button to create a new record, with an automatically created identifier MMTCnnn/05? Or something else?

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: CREATING OWN AUTO NUMBER (2000)

    Exactly how are you using this identifier? If the intent is merely to create a unique identifier, then this is not the best way; use a real autonumber and have a separate field for user initials. Contrived ID numbers like you are suggesting usually cause problems in the long run.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    New Lounger
    Join Date
    Feb 2005
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CREATING OWN AUTO NUMBER (2000)

    The MMTC001/05 is use as the key for the records as I have26 different users at various location entering the data, as it is not on a LAN they data is automaticly emailed to a central database that stores all the records the prefix MMTC001/05 for one user and the prefex WSSU001/05 for example stop the records from clashing when email and being imported, and as a added bonus allows for a quick user identifier . The first reason was the original reason for haveing it and is still the main objective to prevent the records from conflicting when they are recieved and imported into the central database

    Thanks for looking at this problem and I hope I am making myself clearer MMTC is the user 001 the record number /05 is the year
    Regards Ron

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: CREATING OWN AUTO NUMBER (2000)

    What happens if a user has more than 999 records?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    New Lounger
    Join Date
    Feb 2005
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CREATING OWN AUTO NUMBER (2000)

    Mark the record changes to MMTC1000/05 but it expected that no more than 2 records will be entered daily with all the statiscal data for that day being included the next year would commence with MMTC001/06 etc

    Thank Ron

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CREATING OWN AUTO NUMBER (2000)

    I would split this field in 3 fields. One for the initials (MMTC), one for the number, and one for the year.
    In the before Insert of the form, you can use a DMax function to find the greatest number with the right initials and the right year and add 1and store the new number, the initials and the year.
    If you need the number in forms or reports, you can concatenate the 3 field with the right format to get it like you want.
    Francois

  10. #10
    New Lounger
    Join Date
    Feb 2005
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CREATING OWN AUTO NUMBER (2000)

    yes Hans I think that would be a good way to do it but it would be good if they chose the prefix from a list box then this automaticly creates the new record by adding 1 and populates the key

    thanks for the replies Ron in Australia

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CREATING OWN AUTO NUMBER (2000)

    Following Francois' suggestions, create 3 fields:
    sInitials (text, size 10)
    iSeqNo (number, integer)
    iYear (number, integer)
    Make the combination of threse fields the primary key of the table.

    I will call this table tblData; substitute the actual name in what follows.

    Create a table tblInitials with one field:
    sInitials (text, size 10)
    Make this field the primary key.

    Now to your form bound to the tblData table. Create a combo box cboInitials with Row Source Type set to Table/Query and Row Source set to tblInitials.
    Put a command button cmdNew next to it with caption "Create New Record", and put the following code in the On Click event of the button:

    Private Sub cmdNew_Click()
    Dim intYear As Integer
    Dim intSeqNo As Integer
    Dim strInitials As String
    On Error GoTo ErrHandler

    ' Check that initials have been selected
    If IsNull(Me.cboInitials) Then
    MsgBox "Please select your initials.", vbExclamation
    Me.cboInitials.SetFocus
    Exit Sub
    End If

    ' Save current record if necessary
    If Me.Dirty Then
    RunCommand acCmdSaveRecord
    End If

    ' Current year
    intYear = Year(Date)
    ' Initials
    strInitials = Me.cboInitials
    ' Get next sequence number
    intSeqNo = Nz(DMax("iSeqNo", "tblData", "sInitials = " & Chr(34) & _
    strInitials & Chr(34) & " AND iYear = " & intYear), 0) + 1
    ' Go to new record
    RunCommand acCmdRecordsGoToNew
    ' Set the values
    Me.sInitials = strInitials
    Me.iYear = intYear
    Me.iSeqNo = intSeqNo
    ' Ready!
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    End Sub

    Note: if two users on different (unconnected) PCs select the same initials, you will still get duplicate values...

  12. #12
    New Lounger
    Join Date
    Feb 2005
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CREATING OWN AUTO NUMBER (2000)

    Hans thank you for replying and takeing the time to produce the code which I understood my problem is I can see in my mind where to aplly the tables you talk about. I created as per the instruction but alsa nothing happend I have no doubth overlook something. can iget a simpler explantion
    Sorry I know it frustrating but I cant seem to understand how I connect my current field to what you suggest

    thanks in advance Ron in Australia

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CREATING OWN AUTO NUMBER (2000)

    Let's take it one step at a time.

    Your form is bound to a table. In my previous reply I called it tblData, in reality it will have another name.
    Have you added the three fields sInitials, iYear and iSeqNo to "tblData" ?

  14. #14
    New Lounger
    Join Date
    Feb 2005
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CREATING OWN AUTO NUMBER (2000)

    yes i ahve added these to my table called contargID do i give all three a key

    ron

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CREATING OWN AUTO NUMBER (2000)

    If there are existing records in the table, you will have to fill in the fields before you can set the primary key. Once that is done, open the table in design view, select the three fields by dragging through the selection buttons to the left of the field names, and click the Primary Key button on the toolbar.

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •