Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    NOTLguy is offline Advanced Beginner
    Windows XP Access 2002
    Join Date
    Sep 2010
    Posts
    60

    Auto fill field upon New Record selected


    I have a field in a table ( Clients ) called Clients_Client#, which is a text field. Each year we start a new group of clients numbered as follows:

    2011-01
    2011-02
    and so on.

    I would like to fill in this field automatically when a new record is selected to avoid typos and duplications. Any ideas would be appreciated.

    Regards,
    Bill

  2. #2
    Swarland is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2010
    Location
    Northumberland, UK
    Posts
    16
    Hi Bill, Having just played around with this problem, if you make the Clients_Client# field n autonumber instead of a text field, then in the format box put 2010-00
    it will then change it to \20\10"-0"0, but when you go to table view it will show 2010-01, 2010-02 etc only updating the last 2 numbers automatically. However, you will need to change the table format box to 2011, 2012, etc as each year comes around

    Regards

    Roger

  3. #3
    NOTLguy is offline Advanced Beginner
    Windows XP Access 2002
    Join Date
    Sep 2010
    Posts
    60
    Quote Originally Posted by Swarland View Post
    Hi Bill, Having just played around with this problem, if you make the Clients_Client# field n autonumber instead of a text field, then in the format box put 2010-00
    it will then change it to \20\10"-0"0, but when you go to table view it will show 2010-01, 2010-02 etc only updating the last 2 numbers automatically. However, you will need to change the table format box to 2011, 2012, etc as each year comes around

    Regards

    Roger
    Thanks for the quick reply Roger. That was what I was worried about, changing the 2010 each year. It is a maintenance function that requires me do do it rather than the end user. I was hoping to somehow use the date functions for that part and to auto update the last two digits. Hmmmmm

    Regards,
    Bill

  4. #4
    Swarland is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2010
    Location
    Northumberland, UK
    Posts
    16
    Bill, the only other way I can think of would be to have the Clients_Client# field as text and to only fill the table from a form. You would still need an autonumber ID field.
    The ID and Clients_Client# fields would not be tab stops - let me assume that other fields to be completed are Client_Name and Cient_address amongst others - attache to the Client_Name field On_Lost_Focus event the following code:
    [Clients_Client#] = Year(Now()) & "-" & [id]
    and when you tab to the Clients_address field the Clients_Client# field will be updated with 2010-16 or whatever the ID autonumber field is up to.

    You should set the Clients_Client# indexed definition to yes(no duplicates)

    This will allow the year to change automatically without any further input by you.

    regards

    Roger

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Just so everyone understands that an AutoNumber field may not be an unbroken continuous sequence of numbers. With code you could guarantee the sequence. Plus the AutoNumber will not start over each year if that is a requirement.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I like Paul's solution he posted here: http://www.baldyweb.com/CustomAutonumber.htm

  7. #7
    NOTLguy is offline Advanced Beginner
    Windows XP Access 2002
    Join Date
    Sep 2010
    Posts
    60
    Quote Originally Posted by RuralGuy View Post
    Just so everyone understands that an AutoNumber field may not be an unbroken continuous sequence of numbers. With code you could guarantee the sequence. Plus the AutoNumber will not start over each year if that is a requirement.
    Well that is precisely the problem, as I need the auto number portion of the Client_Client# to start over each year so there will be a 2011-01 and a 2012-01 and so on. Hmmmmm. Any more thoughts on this one? I am trying to stop data entry error which has occured already.

    Regards,
    Bill

  8. #8
    NOTLguy is offline Advanced Beginner
    Windows XP Access 2002
    Join Date
    Sep 2010
    Posts
    60
    Quote Originally Posted by RuralGuy View Post
    I like Paul's solution he posted here: http://www.baldyweb.com/CustomAutonumber.htm
    I posted my response before going to have a look at this. Thanks for the pointer. I will give this some thought and let you know how it turns out.

    Bill

  9. #9
    NOTLguy is offline Advanced Beginner
    Windows XP Access 2002
    Join Date
    Sep 2010
    Posts
    60
    Quote Originally Posted by RuralGuy View Post
    I like Paul's solution he posted here: http://www.baldyweb.com/CustomAutonumber.htm
    As his solution is a two part number, can you still search on the field? We often search on Client_Client# to locate a particular record.

    Bill

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    YES. Simply include the concatenation in the search.

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    If you will be searching often it might be worth creating another field with the concatenated value and index it to speed up and simplify the search.

  12. #12
    NOTLguy is offline Advanced Beginner
    Windows XP Access 2002
    Join Date
    Sep 2010
    Posts
    60
    Quote Originally Posted by RuralGuy View Post
    If you will be searching often it might be worth creating another field with the concatenated value and index it to speed up and simplify the search.
    In Paul's solution, I am confused as to what Datefield represents? If my field that has 2020-12 in it is called NumberField, and the Table is Clients, what is Datefield?

    Thanks,
    Bill

  13. #13
    NOTLguy is offline Advanced Beginner
    Windows XP Access 2002
    Join Date
    Sep 2010
    Posts
    60
    Perhaps I had better explain what I am trying to do and see is Paul's solution:

    http://www.baldyweb.com/CustomAutonumber.htm

    Nz(DMax("NumberField", "TableName", "DateField = " & Year(Date())), 0) + 1

    is the right answer and ask how to use it in my circumstances.

    I have a field Called: Client_Client# in the Clients Table which is a text field and contains numbers that start over each calendar year as follows:

    2011-01
    2011-02
    etc

    I am trying to prevent errors in entry and would rather create each new number automatically when a user clicks on the Create a New Record Button. I would also like to automatically start the new year without user or programmer intervention.

    So far, I have had no luck with Paul's solution and perhaps I am using it incorrectly. I was assuming that Datefield is a field of date/time type where the default value is Date().

    Any help would be appreciated in geting this working.

    Regards,
    Bill

  14. #14
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I've got to run right now but I'll look at this later. Sorry.

  15. #15
    NOTLguy is offline Advanced Beginner
    Windows XP Access 2002
    Join Date
    Sep 2010
    Posts
    60
    Quote Originally Posted by RuralGuy View Post
    I've got to run right now but I'll look at this later. Sorry.
    Thank you, I appreciate that,
    Bill

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Auto fill a field from multiple lookup
    By shahemaan in forum Forms
    Replies: 4
    Last Post: 06-13-2014, 04:00 PM
  2. Auto-Fill
    By sophiecormier in forum Programming
    Replies: 3
    Last Post: 10-02-2010, 08:29 AM
  3. Auto-fill in datasheet
    By Terence in forum Database Design
    Replies: 2
    Last Post: 03-18-2010, 03:42 PM
  4. Auto Fill-In Capability?
    By bbarrene in forum Access
    Replies: 3
    Last Post: 01-15-2010, 08:35 AM
  5. Auto-fill field not saving to table
    By aaid in forum Forms
    Replies: 1
    Last Post: 12-18-2009, 05:34 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