Results 1 to 5 of 5
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919

    Get autonumber after new record entered

    Click image for larger version. 

Name:	000.jpg 
Views:	24 
Size:	70.4 KB 
ID:	25541

    I created an AfterInsert procedure for the form, but the event did not fire
    after I entered some text.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you dont get the # until the record is saved, so ....

    make that box the first in the list (with tab stop = no)
    add an unbound box as the last box, and make it transparent ,without borders. Its not for entry, but a place for the cursor to land when done to trigger the save.
    save the record using the txtLastBox_OnGotFocus event. DoCmd.RunCommand acCmdSaveRecord
    NOW the record # should show

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    you dont get the # until the record is saved, so ....
    For fields with Autonumber type, the number is assigned as soon as the record is created, which happens as soon as the user enters any data (i.e. a character) into the record and before it is saved.

    As ranman suggested, just put a text box bound to the record number (i.e. the autonumber field) at the beginning of each line, and Access will fill it in for you.

    Note: if you depend on having all the record numbers present and in sequence, don't use autonumber. The generated autonumber is not reused, even if you don't save the record.

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    What do you need to do with that number? Do you need to save it to another field or you just want it displayed before the record is saved?

  5. #5
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    Turns out I don't need the newly created ID (autonumber) of new record, I just need the record saved so that it becomes the current record. Below is the event code:
    Code:
    Private Sub tbShadow_GotFocus()
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    ' The "Shadow" text box is a transparent "sliver" just to the right of SubCatName
    ' text box.  It gets the focus when the user presses enter after having entered an
    ' additional sub-category name.  The ID of the major category is inserted in the
    ' table field to satisfy the relational link, having done that the record can be
    ' saved.
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    
    On Error GoTo SubCatErr
    
    Me.SubCatOfID = ID                'Set link to Major category (ID captured at Open time)
    DoCmd.RunCommand acCmdSaveRecord
    
    Exit_Handler:
        Exit Sub
    
    SubCatErr:
        MsgBox "Error " & Err.Number & " - " & Err.Description
        Resume Exit_Handler
    End Sub
    Thanks for everyone's thoughts,
    Bill

    (PS) Does ranman256 really look like Arnold

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

Similar Threads

  1. AutoNumber Primary Key until manually entered
    By anonymust in forum Database Design
    Replies: 3
    Last Post: 05-11-2014, 08:34 AM
  2. Replies: 2
    Last Post: 11-30-2013, 02:53 AM
  3. Replies: 19
    Last Post: 12-21-2012, 06:57 AM
  4. Replies: 1
    Last Post: 02-02-2012, 08:34 PM
  5. Record appear if been entered before
    By miziri in forum Programming
    Replies: 2
    Last Post: 08-17-2011, 05:36 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
  •  
Other Forums: Microsoft Office Forums