Results 1 to 12 of 12
  1. #1
    steve7800 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    41

    What event will fire when a new record is created?

    frm10 has a text field wherein the user enters DateReceived. VBA code sets the global variable glbDateReceived to that value entered by the user when closing frm10 and opening frm20.

    frm20 is a form bound to a table. frm20 is configured so that the user can add new records to the table.



    I need a way to programatically set frm20's DateReceived field to the glbDateReceived value each time a new record is created. Occasionally a new record will have a different DateReceived than most of the records being entered and user will need to change it.

    What event will fire when a new record is created? My thought is to use that event to do Me.DateReceived = glbDateReceived.

    What event am I looking for? Is there a better way to accomplish my objective? Thanks.

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    As for every record - OnCurrent event. And there will be NewRecord proprety getting value TRUE.

  3. #3
    steve7800 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    41
    Thank you, that works. I'm a VBA novice, appreciate your patience.

  4. #4
    apr pillai's Avatar
    apr pillai is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2010
    Location
    Alappuzha, India
    Posts
    209
    You may use the AfterInsert Event procedure to insert the Date as given below:
    Code:
    Private Sub Form_AfterInsert()   
    Me![DateReceived] = glbDateReceived
    End Sub
    When you touch a key on the keyboard to insert a character on some field on a new record the Form_AfterInsert() Event is fired.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    probably everything you'll need regarding events for forms and controls. I refer to it often

    https://support.office.com/en-us/art...7-ce86553682f9
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    steve7800 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    41
    Hello apr pillai,

    I tried AfterInsert on a couple of forms bound to an underlying table. AfterInsert fires after the new record is populated with data and moved away from (to another record). What am I missing?

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think the AfterInsert event is being confused with OnChange.
    You didn't like or follow the link I provided? AfterInsert is explained right there and might have saved you from trying to use it to get what you wanted.

  8. #8
    apr pillai's Avatar
    apr pillai is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2010
    Location
    Alappuzha, India
    Posts
    209
    Check the Tab Index Order of each field. This may not be in proper order when you delete and insert fields during design time. Select each field on the form and check it's Tab Index number and order them properly from 0,1,2,3... and so on, so that it will advance from one field to the other in proper order. If the first field have the highest Tab Index number then after advancing from that field will jump to the next record.

    How many fields you have on the Form?

    Form_BeforeInsert() Event also works for you equally well.
    Last edited by apr pillai; 06-02-2018 at 08:20 AM. Reason: addition of text

  9. #9
    steve7800 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    41
    Micron, thank you for the link. I did follow it and do like it and have it bookmarked. I do not recall having seen that page before and I agree with you that it has a lot of good info. Takes a bit of time and hard knocks to get the hang of it

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I'm a bit late to the party but if you
    need a way to programatically set frm20's DateReceived field to the glbDateReceived value each time a new record is created.
    then why not just set the default value of the DateReceived control to that value? Users can still edit it where necessary
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    steve7800 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    41
    Hello ridders52,

    I tried setting the default value but could not get it to work. I placed these lines of code in the Form_Load event:

    Debug.Print " frmItemAdd20 Form_Load " & "glbReceivedDate = " & glbReceivedDate
    txtiDateReceived.DefaultValue = glbReceivedDate
    Debug.Print " frmItemAdd20 Form_Load " & "txtiDateReceived.DefaultValue = " & txtiDateReceived.DefaultValue

    The two debug.print statements yield correct results. But the screen displays 12/30/1899. The txtiDateReceived control is formatted in the Property Sheet as Short Date. I could not figure out how to make the DefaultValue code work and so began looking at other ways. I probably should have posted here seeking help rather than abandoned the DefaultValue idea. Any advice as to how to make it work?

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    As it's a global variable, create a function in a standard module

    Code:
    Function GetReceivedDate()
    
    GetReceivedDate = glbReceivedDate
    
    End Function
    Then set your control default value equal to GetReceivedDate() in its property sheet

    BTW 30/12/1899 is day zero in Access. All dates are stored as double datatype numbers compared to that date
    Today is day 43253 and today at 18:00 is 43253.75
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. How to cause a form event to fire from a general module
    By GraeagleBill in forum Programming
    Replies: 10
    Last Post: 09-04-2016, 08:08 AM
  2. Combo Box On Click Event does not fire
    By GraeagleBill in forum Forms
    Replies: 7
    Last Post: 10-30-2015, 01:20 PM
  3. Fire off event on another form
    By Ruegen in forum Access
    Replies: 4
    Last Post: 04-12-2015, 11:50 PM
  4. Replies: 11
    Last Post: 11-13-2014, 08:52 PM
  5. Trying to fire event on record change
    By danielhowden in forum Forms
    Replies: 3
    Last Post: 05-13-2011, 06:30 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