Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    auto numbering special situations (Access03)

    I am in the planning mode for a new database. Three groups of people agency, staff, and managers. Staff have time card numbers and I will use that as their ID number. The question relates to the other two. On the staff form I plan to mark if the person is Agency or Managers using two separate Yes/NO boxes. There should only be one ID column obviously. I will be entering the staff timecard number manually. In the After Update even for the Yes/No boxes I want to have VBA code to create an ID number that is unique for the agency or manager staff member. Format to be A-0000 and M-0000.

    Question: can that be done? To essentially create two different autonumbers in one column and to manually enter a number if the person is a staff member. Also how would I recover if someone made a mistake. I am assuming, and I know what that means, that if I deleted a number that was computer created based on a mistake that number could be regenerated by Access.

    Thank you for your thoughts and help. Fay

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

    Re: auto numbering special situations (Access03)

    <P ID="edit" class=small>(Edited by Francois on 11-Oct-04 08:55. Use dmax instead of dlookup)</P>To increment your numbers for Agency, use :
    Dim NewID as String
    Dim OldID as String
    OldID = DMax("ID","Table1","Left(ID,1)='A'")
    NewID = Format (Val(Right(OldID,4))+1,"A-0000")

    Use the same for manager but replace 'A' with 'M'
    Francois

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

    Re: auto numbering special situations (Access03)

    If you want continuous numbering for the agency and manager use :
    Dim NewID As String
    Dim OldID As String
    OldID = DMax("Right(ID,4)", "Table1", "left(ID,1)='A' OR Left(ID,1) = 'M'")
    If CheckBoxAgency = True Then
    NewID = Format(Val(Right(OldID, 4)) + 1, "A-0000")
    Else
    NewID = Format(Val(Right(OldID, 4)) + 1, "M-0000")
    End If
    Francois

  4. #4
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: auto numbering special situations (Access03)

    Just a thought

    I would create a Status column which records whether they are Agency or Managers - not use two Yes/No fields

    Reason 1 - It will be easier to create queries/expressions referring to one field only.

    Reason 2 - At some stage it is possible (likely?) that another status will be required. With a single status field this is easy to implement (probably even better to use a look-up table with a list of statuses). If you have to add another Yes/No field at table level you are making an extremely large rod for your own back!
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

Posting Permissions

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