Results 1 to 3 of 3
  1. #1
    zdjbel is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2012
    Posts
    25

    How to add/update date from unbound text box on a form to a table

    Hello,



    I have a database which I update several times a day with new data and want to show the last date and time it was updated on my menu form. On the menu form I have an unbound textbox txtLastUpdated which gets the system date whenever I click the update button on the form. My issue is that every time I leave the form, the date last updated disappears and I want it to be displayed until the next time I run the update command.

    I understand that to do this I must have a table where the date will be stored so I created a table tLastUpdated with one field LastUpdated to store the date and time in. How do I update the date from the form to the table with VBA so it can be displayed until the next update?

    Thanks very much in advance!

    Zee

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    In case you delete all records from table(s) and insert new info:
    1a) When you update a single table only, you need a table like tLastUpdated: UpdateDate and add a row with some date into it. Into procedure which runs update query, you must add row
    Code:
    UPDATE tLastUpdate SET UpdateDate = Date
    1b When you update sevaral tables (egal with same procedure, or with different ones), you need a table like tLastUpdated: TableName, UpdateDate. Into procedure which runs a update query e.g. for tTable1, you must add rows
    Code:
    DELETE FROM tLastUpdate WHERE TableName = "tTable1"
    INSERT INTO tLastpdate (TableName, UpdateDate) VALUES ("tTable1", Date)
    Here is used INSERT INTO instead of UPDATE, as having multiple tables in update list automatically assumes, that you may want new tables to be updated in future, and it is easier to delete existing and create new records instead of checking every time is the table in update list or not, and then decide between INSERT and UPDATE.

    In case you update only part of records in table(s), you need a field UpdateDate in (every) table updated. The field is updated as part of update query, like
    Code:
    UPDATE tTable1 (Field1, Field2, ..., UpdateDate)
    SET Field1 = SomeValue1, Field2 = SomeValue2, ..., Date)
    When you want to know last update made in form for every row in table(s), you also need a field UpdateDate, which is linked to hidden or disabled text box (txtUpdateDate) in form. In BeforeUpdate event of form you add a row
    Code:
    txtUpdateDate = Date

  3. #3
    zdjbel is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2012
    Posts
    25
    Hello Avril,

    Thank you very much, your proposed solution 1a solved my problem since I was looking to update a single table.

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

Similar Threads

  1. Replies: 5
    Last Post: 01-07-2019, 01:51 AM
  2. Replies: 9
    Last Post: 01-09-2014, 02:58 PM
  3. Replies: 3
    Last Post: 05-02-2013, 01:59 PM
  4. Replies: 5
    Last Post: 06-23-2012, 10:30 PM
  5. IIF formula in an unbound text box using date()
    By probablyjoel in forum Forms
    Replies: 3
    Last Post: 04-20-2012, 12:03 PM

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