none
Need to set textbox values when form is first dirtied RRS feed

  • Question

  • I'm trying to set the value of 2 textboxes to the current date when the user begins entering data in the first textbox on my form. I coded the On Dirty event handler for the form as follows, but the statements within the If statement are never executed (nor do I know if they would succeed, since they don't get executed).  Thanks in advance for any help you can offer.
    Private Sub Form_Dirty(Cancel As Integer)
    If OpenDate.Value = Null Then
        OpenDate.Value = Now()
        AssignDate.Value = Now()
    End If
    End Sub

    Thursday, July 6, 2017 5:57 PM

Answers

  • I'm upsizing the database to an SQL back-end.  The Now() default function had previously been in place as you are suggesting, but after the upsize, the SQL tables contained the GetDate() function as a replacement, and it did not seem to populate the textboxes on the form in the same way that the Now() function in the Access tables had; that is why I was trying to do it programmatically.  I think I found a workable solution from some further hunting on the web.  I removed my code from the On Dirty event of the form, and placed it in the After Update event of the first (tab 0) textbox on the form.  So, the user does have to complete one field, but it seems reasonable.

    Thanks for your help with this.

    Thursday, July 6, 2017 6:56 PM

All replies

  • Hi,

    The Dirty event fires whenever a record is modified. Is this what you want? Or, did you just want to mark the record when it was first created?

    Thursday, July 6, 2017 6:03 PM
  • I just wanted to mark the record when first created - definitely not every time it is modified.
    Thursday, July 6, 2017 6:21 PM
  • Okay, that makes it easier. Go to the table's design view and in the Default Value for both fields, you can enter:

    Now()

    Hope it helps...

    Thursday, July 6, 2017 6:27 PM
  • I'm upsizing the database to an SQL back-end.  The Now() default function had previously been in place as you are suggesting, but after the upsize, the SQL tables contained the GetDate() function as a replacement, and it did not seem to populate the textboxes on the form in the same way that the Now() function in the Access tables had; that is why I was trying to do it programmatically.  I think I found a workable solution from some further hunting on the web.  I removed my code from the On Dirty event of the form, and placed it in the After Update event of the first (tab 0) textbox on the form.  So, the user does have to complete one field, but it seems reasonable.

    Thanks for your help with this.

    Thursday, July 6, 2017 6:56 PM
  • Hi,

    You might test your workaround with an existing record. What if the user happens to update the same field you're using to mark the new record.

    Instead, I might recommend another approach, since I did not know you're table was linked to SQL Server. So, rather than setting the Default Value at the table level, how about doing it at the Form level?

    For example, in the design view of the form, select the two textboxes for the fields you want to set the Default Value and enter Now() in the Default Value property.

    Hope it helps...

    Thursday, July 6, 2017 7:09 PM
  • I'm trying to set the value of 2 textboxes to the current date when the user begins entering data in the first textbox on my form. I coded the On Dirty event handler for the form as follows, but the statements within the If statement are never executed (nor do I know if they would succeed, since they don't get executed).

    Hi Tim,

    In the Click event of the control you can use:

    If (IsNull(OpenDate)) Then
        OpenDate = Now()
        AssignDate = Now()
    End If
    

    You have to use the IsNull function to test for a Null value.

    In any date control (on any form) I can use the "," and "." keyboard keys (the lower case countervalue of "<" and "">") to initialize the date to Date() when the control values is Null, or to decrease/increase the date by one day if the control displays a valid date.

    Imb.

    Thursday, July 6, 2017 10:15 PM
  • Okay, that makes it easier. Go to the table's design view and in the Default Value for both fields, you can enter:

    Now()


    Actually, that's not a reliable solution.  It assigns the current date and time when the form's record pointer is moved to an empty new record, not when the user begins to insert data.  For that the return value of the Now() function should be assigned to the control in the form's BeforeInsert event procedure.

    Examples of date/time stamping or logging can be found in ChangedTRecordDemo.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file is primarily intended to illustrate how to detect when the values in a row have been changed, including when the row is first inserted, rather than merely when the row has been updated, which does not necessarily imply any changes to the values, but serves to illustrate date/time stamping or logging.


    Ken Sheridan, Stafford, England

    Friday, July 7, 2017 11:31 AM
  • Actually, for a new record “when” you  start to type, I recommend the before insert. This event ONLY fires if you start typing on a new record, and you can thus easy code any values you want to appear in any column.

    Since the record will be blank, then you can go:

    Private Sub Form_BeforeInsert(Cancel As Integer)

       Me.EmailName = "kallal@msn.com"
       Me.CreatedDate = date()
       ' etc. etc. etc.
       ' or in your case

       me.OpenDate = Now()

       me.AssignDate = now()

    End Sub

    So the above event ONLY fires if the user starts typing into a new blank record – and that includes linked tables to SQL server.

    If the user does not type or “dirty” the new record, then if you exit then a record is not created.

    This event works well for linked tables to SQL server, or even those that are local, or of course standard linked tables to a Access only  back end.

    Regards,
    Albert D. Kallal (Access MVP 2003-2017)
    Edmonton, Alberta Canada

    Saturday, July 8, 2017 12:28 AM
  • .theDBguy,

    I think your proposal would have worked fine, but I ended up adding code to set the value of the two date fields equal to Now() when the AfterUpdate event fires on the first form entry field.  I also tried it in the OnDirty event of that first form field, which also works.

    Tuesday, July 11, 2017 5:58 PM
  • Hi Tim,

    As long as you got it to work, we're all good. Cheers!

    Tuesday, July 11, 2017 6:36 PM