1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access 2003 automatic field entry

Discussion in 'Business Applications' started by RANCHLAW56, Dec 30, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. RANCHLAW56

    RANCHLAW56 Thread Starter

    Joined:
    Dec 25, 2010
    Messages:
    23
    I have a field in my Access 2003 database that currently requires the user to enter the 2 digit month & 2 digit year of the incident as the prefix (prefix) to the case number. I would like to have this automatically created based on the date entered in the date of incident field (date).

    Any ideas would be appreciated.
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Do you need it to input the Case Number as well?
     
  3. RANCHLAW56

    RANCHLAW56 Thread Starter

    Joined:
    Dec 25, 2010
    Messages:
    23
    No the case number is the primary key value
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    As in an Autonumber field?
     
  5. RANCHLAW56

    RANCHLAW56 Thread Starter

    Joined:
    Dec 25, 2010
    Messages:
    23
    That is correct
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    In the AfterUpdate event procedure of the date of incident field you need some VBA code. What exact format do you want the case number?
     
  7. RANCHLAW56

    RANCHLAW56 Thread Starter

    Joined:
    Dec 25, 2010
    Messages:
    23
    If the entry in the date field were 3/23/12 then the prefix would be 0312.
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    0312001, or 0312-001
     
  9. RANCHLAW56

    RANCHLAW56 Thread Starter

    Joined:
    Dec 25, 2010
    Messages:
    23
    The full case number with the prefix would be 0312-0001. The prefix is stored in a seperate field in the table and used as reference for relation to the previous filing system and to prevent confusion with outside agencies. They do not have to be combined in the database.
     
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    What is the name of the field Prefix?
     
  11. RANCHLAW56

    RANCHLAW56 Thread Starter

    Joined:
    Dec 25, 2010
    Messages:
    23
    That is correct. The field name is Prefix.
     
  12. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    me.Prefix = month(me.datefieldname) & Year(me.datefieldname)
    the datefieldname should not really be date as that is the name for the System date and might cause some confusion for Access and VBA.
     
  13. RANCHLAW56

    RANCHLAW56 Thread Starter

    Joined:
    Dec 25, 2010
    Messages:
    23
    Almost there.... it is posting into the Prefix as 032010 (2 digit month and 4 digit year)
     
  14. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Ok change Year(me.datefieldname) to format(me.datefieldname,"yy")
     
  15. RANCHLAW56

    RANCHLAW56 Thread Starter

    Joined:
    Dec 25, 2010
    Messages:
    23
    Private Sub Date_Received_Exit(Cancel As Integer)
    Me.Prefix = Month(Me.Date_Received) & Format(Me.Date_Received, "yy")
    End Sub


    Returns error "Can't find project or library"
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/971580

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice