Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting (Access 2000)

    Not sure how to approach this question. A student is trying to set up a
    correspondence tracking database for her department and she would like do a couple of things:
    1. In the "Correspondence" table, when her boss checks "follow up" and
    selects "Assigned to" for the department, based on the department selected she
    would like her database to automatically assign a number in the "Tickler #" field in the format described below and for numbers to be incremental but restart at 01 at the beginning of a new calendar year, e.g., (hopefully that willl eliminate playing with the database format to much).

    Admin & Finance = Admin-yy-####(2digit year)(System Assigned Number)
    Example: Admin-03-01, A-03-02
    Econ =Econ-yy-#####
    EQB = EQB-yy-####
    ECB =ECB-yy-####


    Any guidance greatly appreciated...example is attached along with a jpg pic of properties window
    Attached Files Attached Files
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

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

    Re: Formatting (Access 2000)

    In the first place, I hope you meant a form. Bosses should *never* be allowed to edit records directly in a table or query (*). <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    What happens if the boss decides to clear the "Follow up" check box? Should Tickler# be cleared too?
    And if the boss selects another "Assigned to" department while "Follow up" is checked, should the Tickler# be changed too?

    BTW, the jpg is so small that it's unreadable, but that doesn't matter.

    (*) Nor should any other end user.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting (Access 2000)

    Hans;
    I will send her your questions. I totally agree with your statements on the form deal. <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15> Other then the Administrator "no one" should have access to anything except the forms intended for that person. Thank you for answering, will let you know what she has to say.
    Nannette
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting (Access 2000)

    Okay Hans this is her response: (She never answered if she's using a form, I am assuming she is!)

    If my boss selects "follow-up" then I want the system to force him to select a department and a tickler number assigned as described (epiphany: I guess I need to change that "follow-up" caption to "Assign Tickler#"??). To keep the database accurate I think once a tickler # is assigned it should be left alone, if it is inadvertent we can just close it out as an erroneous assignment in the comments field. If action from more than one department is required we could handle that in a comments field.

    I will need to allow him to assign correspondence to a department without assigning a tickler number if it is something that my office does not have to follow up on. Any guidance you can provide would be greatly appreciated.

    Thanks
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

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

    Re: Formatting (Access 2000)

    I added a TicklerSeq field (number, long integer) in the Correspondence table to hold just the sequence number.

    I added an Abbreviation field to the Department table to hold the department "code" to be used in the Tickler#; I added this field as a (hidden) third column in the Department combo box.

    This is the code:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strYear As String
    Dim strDept As String
    Dim lngSeq As Long
    If Me.Followup = True Then
    ' Only act if Followup is ticked
    If IsNull(Me.DepartmentID) Then
    ' Department must be filled
    MsgBox "If Followup is ticked, you must select a department.", vbExclamation
    Me.DepartmentID.SetFocus
    Cancel = True
    ElseIf IsNull(Me.DateReceived) Then
    ' DateReceived must be filled
    MsgBox "If Followup is ticked, you must enter a received date.", vbExclamation
    Me.DateReceived.SetFocus
    Cancel = True
    ElseIf IsNull(Me.TicklerNumber) Then
    'If Tickler# already filled, leave alone
    strDept = Me.DepartmentID.Column(2)
    strYear = Format(Me.DateReceived, "yy")
    lngSeq = Nz(DMax("TicklerSeq", "Correspondence", _
    "DepartmentID = " & Me.DepartmentID & _
    " And Year(DateReceived) = " & Year(Me.DateReceived)), 0) + 1
    Me.TicklerSeq = lngSeq
    Me.TicklerNumber = strDept & "-" & strYear & "-" & Format(lngSeq, "00")
    End If
    End If
    End Sub
    Attached Files Attached Files

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting (Access 2000)

    HansV,

    Your absolutely Emazing...<img src=/S/smile.gif border=0 alt=smile width=15 height=15> I have sent her your reply, and also the link back here to the page so if she has any other questions, maybe she can ask you directly. Thanks ever so much...been such a busy week, and my heads been to jammed to think to clearly.

    Always grateful...I salute you! <img src=/S/salute.gif border=0 alt=salute width=15 height=20> Nannette
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting (Access 2000)

    Hans,

    Here is the great news!!!

    I forwarded your response as:
    Janice,
    I received help on this one because envisioning what you needed well, I was kind of lost. I went to Woody's Lounge and talked to HansV, and the edited database as he worked on your question is attached to this email...he added some code to the database, and explains what he didThis is the link as well:
    http://www.wopr.com/cgi-bin/w3t/showflat.p...sb=5&o=0&fpart= )

    From HansV at Woody's Lounge:

    And she responded in turn as:

    That is exactly what I wanted. Thanks so much. By the way, once complete all users will enter data using forms via a switchboard. I'm still in the process of working out the forms and reports that I need to build. I'm sure I will be getting in touch with your soon. Again, thankyou both for your expertise. Jan

    Thank you Hans, as always!!!!

    Nannette
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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