Results 1 to 14 of 14
  1. #1
    jeffreylewis is offline Novice
    Windows 10 Access 2007
    Join Date
    Sep 2020
    Posts
    19

    Update time field from another field update

    Windows 10


    Access 2007

    I have 2 fields on a form
    1. txtIn_Stock
    2. txtIn_StockChangeDate

    I am trying to achieve that when field 1. txtIn_Stock changes then Field 2. txtIn_StockChangeDate enters todays date.
    I am using the code below but no joy so far.

    Code:
    Private Sub txtIn_Stock_BeforeUpdate(Cancel As Integer)     
     Me.txtIn_StockChangeDate = Date
    End Sub
    Grateful if anyone can help.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Forms don't have fields. They have controls which can be bound to fields in the forms Record Source.
    Are the control names the same as the fields to which they are bound?
    Have you made sure that your code does actually run by placing a break point at the start of the procedure and then stepping through the code using F8 ?
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Try a different event. AfterUpdate? LostFocus?
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  4. #4
    jeffreylewis is offline Novice
    Windows 10 Access 2007
    Join Date
    Sep 2020
    Posts
    19
    Hi Guys thanks for the help. But from what I am reading only a Macro would work. Not VBA
    But I am a bit restricted in macros for 2007

    Any ideas as to how to achieve a macro to do this?

  5. #5
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Before update is the wrong event.
    Are you changing txtIn_Stock with code or are actually entering a value in the control?
    If you are setting txtIn_Stock with code then I doubt any events are firing.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  6. #6
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    As moke123 suggested...

    Code:
    Private Sub txtIn_Stock_AfterUpdate()
     Me.txtIn_StockChangeDate = Date
    End Sub
    Should work just fine!

    Quote Originally Posted by jeffreylewis View Post

    ...But from what I am reading only a Macro would work...
    Don't know where you read that...but I wouldn't refer to that source again...nothing could be further from the truth! Macros are seldom used by experienced developers, as they tend to be very rigid, and limited in what they can do.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    jeffreylewis is offline Novice
    Windows 10 Access 2007
    Join Date
    Sep 2020
    Posts
    19
    This doesn't work because there is no way to programatically update the txtIn_StockChangeDate date field. It works ok on the key press or manually enter the change. But to programatically update it just won't work.
    If anyone can show me different you'll be my friend for life.

    Private Sub txtIn_Stock_AfterUpdate()
    Me.txtIn_StockChangeDate = Date
    End Sub

  8. #8
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    The only way that I can think of where it doesnt work is if you are programatically updating the txtIn_Stock field and expecting that to fire the afterupdate event of the txtIn_Stock field.
    If you are updating the txtIn_Stock field manually, the afterupdate event does not fire until you exit the txtIn_Stock control.

    if you are programatically updating the txtIn_Stock field, why not update the txtIn_StockChangeDate within the same code?

    Rather than us show you that it works perhaps you could post an example showing us how it doesnt work. At least that way we can see what it is your doing.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  9. #9
    jeffreylewis is offline Novice
    Windows 10 Access 2007
    Join Date
    Sep 2020
    Posts
    19
    Thanks for staying with me.


    On the current event of the form the following code differentiates between something being IN STOCK or OUT OF STOCK on a wholesaler's web page via a WebBrowser0 control.


    If Nz(doc.getElementsByName("Quantity")(0).Type, 0) = "hidden" Then
    Me.txtIn_Stock = 0
    Else
    Me.txtIn_Stock = Nz(doc.getElementsByName("Quantity")(0).Value, 0)
    End If


    What I am tring to do is if there HAS BEEN a change is to programatically record the date of the change something like this.
    Me.txtIn_StockChangeDate = Date

  10. #10
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Thats what I thought was happening.
    So you are setting the value of Me.txtIn_Stock with code. When you do that the controls events do not fire.

    Cant you test for the change in the current event also and then set the value of both fields at the same time?

    dim x as integer
    If Nz(doc.getElementsByName("Quantity")(0).Type, 0) = "hidden" Then
    x = 0
    Else
    x = Nz(doc.getElementsByName("Quantity")(0).Value, 0)
    End If

    me.txtIn_Stock = x

    ' do a test for the change and if there is a change then

    Me.StockChangeDate = date

    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  11. #11
    jeffreylewis is offline Novice
    Windows 10 Access 2007
    Join Date
    Sep 2020
    Posts
    19
    ' do a test for the change and if there is a change then
    Not sure how to do that with Code.

    Although as I scroll through the records it certainly IS working

  12. #12
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I cant see what you have but it should be something like this

    Code:
    Dim x as Integer, y as Integer
    
    
    y = me.txtIn_Stock  ' get existing value
    
    
    If Nz(doc.getElementsByName("Quantity")(0).Type, 0) = "hidden" Then
        x = 0
    Else
        x = Nz(doc.getElementsByName("Quantity")(0).Value, 0)
    End If
    
    me.txtIn_Stock = x
    
    
    if x <> y then  ' test if they dont match
    
    
        Me.txtIn_StockChangeDate = Date
    
    
    end if
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  13. #13
    jeffreylewis is offline Novice
    Windows 10 Access 2007
    Join Date
    Sep 2020
    Posts
    19
    Thank you sooooo much Moke123
    That go me on the right track and it works as I wanted it to now

    I have been looking at the same few lines of code for so long I just could not see what was wrong.

    Best regards
    Jeffrey Lewis

  14. #14
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    your welcome.
    good luck with your project.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

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

Similar Threads

  1. Replies: 4
    Last Post: 03-27-2018, 09:19 PM
  2. Update field based on time stamp
    By Nortonsapple in forum Queries
    Replies: 11
    Last Post: 02-20-2017, 07:54 AM
  3. Replies: 9
    Last Post: 07-21-2014, 11:57 AM
  4. Replies: 3
    Last Post: 06-06-2012, 12:45 PM
  5. NEW sql update HELP :( (time field problem)
    By ducthang88 in forum Programming
    Replies: 1
    Last Post: 12-05-2010, 12:03 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