Results 1 to 7 of 7

Sequential Numbering in a Form

  1. #1
    Falafa is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Posts
    6

    Sequential Numbering in a Form

    In Access2010, how do I set instructions (macro) in the data entry form to incrementally fill in the next number of my Member ID number field? The number used contains three letters followed by six numbers.



    The data entry form collects typical information, i.e. last name, first name, address, phone, email, etc. It's control source is my tblMain which holds all the information.

    I know nothing in terms of SQL statements, VB, writing macros. I don't know what to do with the macro builder. Can someone work with me to "build" the correct macro?

    Let me know if there is more I need to explain that will help.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,222
    Review https://www.accessforums.net/forms/a...ing-23329.html

    Is the alpha prefix always the same characters? You could just use an autonumber field and then format the value to display as the desired structure. However, if you cannot allow gaps in sequence, autonumber might not be appropriate.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Falafa is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Posts
    6
    I follow that, but after reading it I realize I didn't give full information. The ID number begins with three letters which will never change, followed by six numbers which will hopefully automatically increase as each new record is entered in the data entry form. Example: FWA000001, FWA000002, etc. Can I write a formula somewhere in the property sheet of the "Member ID" field on the data entry form which will incrementally enter that next number? If so, where do I put it and what would I write? Make sense?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,222
    I provided an example of VBA function from my project. I call the function when user clicks 'add record' button. The record is created and form opened to the new record. Alternatively, the ID number can be constructed when user clicks 'save record' button. If you have no or limited VBA knowledge, this might be difficult for you.

    The easiest approach is an autonumber field.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    Code:
    dim smaxID as string
    dim sNextMemberID as string
    
    smaxID = dmax("[memberid]", "membertable")
    snextmemberID = "FWA" & right("000000" & clng(right([smaxid],6)) + 1, 6)
    you'd have to then populate your member ID field before you saved it or moved to a different record.

  6. #6
    Falafa is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Posts
    6
    rpeare - I see your code and it sorta makes sense to me. Now, where do I put that code? Somewhere in the property sheet of the form design? The name of the main table that holds all the information is called tblMain, so is that what I'd use where you have written "membertable"? So what I'd put in would look like:
    dim smaxID as string
    dim sNextMemberID as string

    smaxID = dmax ("(MemberID)", "tblMain")
    sNextMemberID = "FWA" & right ("000000" & cling(right((smaxID), 6)) +1, 6)

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    Where ever you are doing your record validation. If you are using an unbound form you'd use it on your 'save record' button, if you're using a bound form you'd have to fill in the field that has your member ID before you navigate away from the record. I don't know anything about your database I'm just giving you a method to calculate the 'next' member ID would be.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. how to get auto numbering on continuous form records
    By shubhamgandhi in forum Programming
    Replies: 1
    Last Post: 08-04-2011, 02:26 PM
  2. Numbering Entries In a Form
    By blueraincoat in forum Forms
    Replies: 7
    Last Post: 03-16-2011, 06:35 AM
  3. Sequential Order ID on Form
    By charya in forum Forms
    Replies: 1
    Last Post: 01-15-2011, 10:51 AM
  4. Help with Sequential Numbering
    By orion in forum Programming
    Replies: 3
    Last Post: 07-06-2009, 01:41 PM
  5. Passing data to sequential form fields
    By jeepfamilyva in forum Forms
    Replies: 0
    Last Post: 06-28-2009, 11:04 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums