Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237

    vba code helping with overriding or updating fields as a form opens to current details

    I have a back order form that when it opens and the txtQtyReceived field has focus I would like auto override or update the date and user fields that was recorded?

    the code I have working at the moment to populate the fields when in Focus is -

    Private Sub txtQtyReceived_GotFocus()
    If IsNull(Me!DateReceived) Then Me.txtDateReceived = Date


    If IsNull(Me.ReceivedBy) Then Me.txtReceivedBy = [Forms]![LoginForm]![cboUser].Column(1)
    End Sub

    im trying to keep track last person that open and modified information... if I could keep track of multiple records would be good but don't think that is possible as the backorder details are in same table of order details so 1 order can only ever have 1 back order record...

    I could change this and add a back order table but back orders in our company are very few and generally come as a single delivery, but for the one chance it doesn't I would like to the update details.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    using the control gotfocus event is probably not the right one - what if a user clicks or tabs to the field and doesn't make any changes?

    suggest use the control after update event instead

  3. #3
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by Ajax View Post
    using the control gotfocus event is probably not the right one - what if a user clicks or tabs to the field and doesn't make any changes?

    suggest use the control after update event instead
    I have mess'd around with most of the Events and figured this would be best.

    I understand where you're coming from and there isn't a lot of info to put in on this form literally 3 fields but will give update event a shot.


    is what im asking above doable?

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    of course - pretty much anything is doable.

    But since you are only updating a field if it is null, it won't be tracking who makes subsequent changes and when.

    You need to consider all the possible options and design accordingly. You could use the form before update event instead. If you only want the original date then in the table design you could set the default for the date field to Date, or Now if you want to know the time of day as well.

  5. #5
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by Ajax View Post
    of course - pretty much anything is doable.

    But since you are only updating a field if it is null, it won't be tracking who makes subsequent changes and when.

    You need to consider all the possible options and design accordingly. You could use the form before update event instead. If you only want the original date then in the table design you could set the default for the date field to Date, or Now if you want to know the time of day as well.
    I thought the same with setting the table to =date but that means any one who opens the form will change the date... im only wanting an update if a change is made to the form

    ill do some more googling on different possibilities to track changes, I only know the very basics and then only a small amount of that.

    and by the way Ajax the after update works a treat so thankyou.

  6. #6
    SodaPop is offline Novice
    Windows 8 Access 2007
    Join Date
    Mar 2014
    Posts
    16
    Quote Originally Posted by ShaunG View Post
    I thought the same with setting the table to =date but that means any one who opens the form will change the date... im only wanting an update if a change is made to the form

    ill do some more googling on different possibilities to track changes, I only know the very basics and then only a small amount of that.

    and by the way Ajax the after update works a treat so thankyou.
    It is longer to code and more manual but I've found the easiest way is unbind the form and query the results in the table. Make a search from, finds the order to work with, pass the order to the backorder form and use auto number to create a backorder number if none exists, use an nz dlook here. Then the end user populates the from a click update which simply trigger an append query. In your backorder table you could have many separate instances now for each update. If you want a single record you'll need two back order forms, create and update. The create you display only bound fields you don't want to ever change and unbound fields with a dlook for the record. The user makes updates then trigger and update query to run. Either way works great. There are way to control table bound forms in just feel this is cleaner and easier.

    Sent from my SM-T813 using Tapatalk

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    different possibilities to track changes
    There are four basic possibilities

    1. a single record, updated for latest change (which is what you seem to be wanting). This isn't really tracking, just latest update
    2. history - you have two tables, the current table and a history table. When a record is added/updated you have code to also copy the added/updated record to the history table
    3. a variation of the above is to just have the history table, once a record is created, all update actions are changed to append actions so any change creates a new record. Requires more work to determine the current record (based on the latest date) but easy to list the history. Also can be more difficult if you are relying on an autonumber primary key for linking to other tables - this will change on each update.
    4. an audit table - this stores what has be changed (and when and who) on a field by field basis - this is the most detailed but requires some work to 'reconstitute' a record to look at the history.

    All these require code, usually in the form beforeupdate event. Be aware that each field has an 'oldvalue' property so you can compare with the current value to see if it has changed (and from what to what)

    Which of these is right for you depends on what you want to do with the information once you have it and potentially what is practical if multiple tables are involved.

  8. #8
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by Ajax View Post
    There are four basic possibilities

    1. a single record, updated for latest change (which is what you seem to be wanting). This isn't really tracking, just latest update
    2. history - you have two tables, the current table and a history table. When a record is added/updated you have code to also copy the added/updated record to the history table
    3. a variation of the above is to just have the history table, once a record is created, all update actions are changed to append actions so any change creates a new record. Requires more work to determine the current record (based on the latest date) but easy to list the history. Also can be more difficult if you are relying on an autonumber primary key for linking to other tables - this will change on each update.
    4. an audit table - this stores what has be changed (and when and who) on a field by field basis - this is the most detailed but requires some work to 'reconstitute' a record to look at the history.

    All these require code, usually in the form beforeupdate event. Be aware that each field has an 'oldvalue' property so you can compare with the current value to see if it has changed (and from what to what)

    Which of these is right for you depends on what you want to do with the information once you have it and potentially what is practical if multiple tables are involved.
    Thanks for those options Ajax I reckon your option 1 would suit the database fine... just an update

    What start of code would you use for this?

  9. #9
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by SodaPop View Post
    It is longer to code and more manual but I've found the easiest way is unbind the form and query the results in the table. Make a search from, finds the order to work with, pass the order to the backorder form and use auto number to create a backorder number if none exists, use an nz dlook here. Then the end user populates the from a click update which simply trigger an append query. In your backorder table you could have many separate instances now for each update. If you want a single record you'll need two back order forms, create and update. The create you display only bound fields you don't want to ever change and unbound fields with a dlook for the record. The user makes updates then trigger and update query to run. Either way works great. There are way to control table bound forms in just feel this is cleaner and easier.

    Sent from my SM-T813 using Tapatalk
    thanks for that Sodapop reckon im gonna try the single update Ajax is suggesting to begin with if the boss wants to track it more so sounds like what youve got there is the go.

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I would think in the form before update event put


    Me.txtDateReceived = Date
    Me.txtReceivedBy = [Forms]![LoginForm]![cboUser].Column(1)

  11. #11
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by Ajax View Post
    I would think in the form before update event put


    Me.txtDateReceived = Date
    Me.txtReceivedBy = [Forms]![LoginForm]![cboUser].Column(1)
    yeah I have gotten that to work but do get an error every now and then when it comes up again ill post.

  12. #12
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by Ajax View Post
    I would think in the form before update event put


    Me.txtDateReceived = Date
    Me.txtReceivedBy = [Forms]![LoginForm]![cboUser].Column(1)
    now I have an Original Back Order Qty field which calculates on the form the ordered goods from the received goods and shows the difference.

    I also have a Back Orders Received Todate field to know how much of item has come in.

    I have created an unbound text box for receiving back orders and have used code so when the user adds to the Qty of this field it adds it to the Back Orders Received Todate Qty.

    the code works but and shows the value in the query but in the form the control is blank.... is this extra coding I need to do to get it to work or have I done something wrong?


    Private Sub txtQtyOfBackOrderReceived_AfterUpdate()
    Me.BackOrderReceived = [txtBackOrderReceived] + [txtQtyOfBackOrderReceived]
    Me.txtDateAmended = Date
    Me.txtAmendedBy = [Forms]![LoginForm]![cboUser].Column(1)
    End Sub

  13. #13
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    what happens if you go to another record (or close the form) then return to this record?

  14. #14
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by Ajax View Post
    what happens if you go to another record (or close the form) then return to this record?
    I have to retract my statement Ajax I doubled checked the form, table and query and it is not working..

    I started off using an If Not IsNull and If IsNull code that worked for the most part, ill try to recreate and post.

    the issue was only for the first value I insert in to txtQtyOfBackOrderReceived, it would put the value in txtBackOrderReceived but then it would add the value again - if I inserted 1 it would end up 2.

    every value after the first worked fine though

  15. #15
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by Ajax View Post
    what happens if you go to another record (or close the form) then return to this record?
    I managed to get the below code to work nicely Ajax.

    it updates the user details and date everytime a user inputs a record of a received back order

    Private Sub txtQtyOfBackOrderReceived_AfterUpdate()
    If IsNull(Me.BackOrderReceived) Then
    [txtBackOrderReceived] = [txtQtyOfBackOrderReceived]
    Else:
    Me.txtBackOrderReceived = [txtQtyOfBackOrderReceived] + [txtBackOrderReceived]
    End If
    Me.txtDateAmended = Date
    Me.txtAmendedBy = [Forms]![LoginForm]![cboUser].Column(1)
    End Sub

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Stop executing code when Form opens
    By Joakim N in forum Programming
    Replies: 3
    Last Post: 01-04-2017, 06:09 AM
  2. Problems updating a customer details
    By shaunacol in forum Forms
    Replies: 9
    Last Post: 07-13-2015, 11:26 AM
  3. Graph on Form - Overriding data
    By Gabriel2012 in forum Forms
    Replies: 6
    Last Post: 02-19-2013, 10:18 AM
  4. Replies: 9
    Last Post: 04-11-2011, 10:55 PM
  5. Computer1 Form Current Record opens on Computer2
    By joefonseca79 in forum Forms
    Replies: 2
    Last Post: 02-20-2011, 04:11 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