Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unhappy Unanswered: Store date and time of changes made to 2 tables in one macro?

    I have a macro (datemodified) which records the date and time "now() command" into a textbox on a form called 'frmpersonaldetails' and the corresponding field in the forms table called "tblpersonaldetailsvia when each field on the form is changed on its After Update.

    It works fine for that single form and table, but I also have a second table(tblsentencenumber) and subform (frmsentencenumber) on the form and I need a macro or piece of code to make both tables show when they were last updated.

    The two tables are linked via a relationship "personID".

    I dont really want 2 macros with DateModified in each of the two tables. Just if either is changed afterupdate and have it stored in one place only if either table is altered.

    Any Ideas?

    Tom
    Last edited by moss2076; 09-27-04 at 15:50.

  2. #2
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    Are both the forms open at the same time? What i mean is when one form is edited is the other form (and subsequently table) open as well?

  3. #3
    Join Date
    Aug 2004
    Posts
    364
    Yes both forms are open and visible at the same time.

  4. #4
    Join Date
    Aug 2004
    Posts
    364
    Any ideas anyone?

  5. #5
    Join Date
    May 2004
    Location
    NH
    Posts
    87

    Cool

    Moss,

    I think I understand what you you're trying to do...

    You have two tables storing values for two different forms and you want to show the same last update date for both the tables/forms and you want that date to be stored in 1 location as opposed to 2?

    What I would suggest doing is to just create another table which stores last update values, you can create a relationship or not; depends on how extensive it would be.

    Then just pull that date and update your two forms with that value.

    You could also bind the subform field value to the main form value and refresh the subform value, which is essentially the same thing as setting both values upon change.

    Let me know if this helps or if I'm completely wrong...

    Thanks,
    Warren

  6. #6
    Join Date
    Aug 2004
    Posts
    364
    Yes! that is exactly what I want to do! Any ideas how though? I got the single macro to run on the first forms After Update event, and it saves the date to that table.

    How do I go around saving the date to a seperate 3rd table from the other 2 (form and subform visible at the same time).

  7. #7
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    In whatever code you have running to update the main form value insert something like this:

    Code:
        Dim con As ADODB.Connection
        Dim tmp As Date
        
        Set con = Application.CurrentProject.Connection
        tmp = Now()
        
        Me.Text2.Value = tmp
        Me.form2.Controls("Text2").Value = tmp
        
        con.Execute "update mod_table set mod_table.date_mod = #" & tmp & "# where mod_table.table_name = 'yourformnamecategory'"
        
        Set con = Nothing
    What I would do is create a seperate table named something like 'form_mod_history' with columns form_name, date_mod and update those values.

    Am I making sense?

    -Warren

  8. #8
    Join Date
    Aug 2004
    Posts
    364
    The 2 forms and tables are called - Personal Details form & Table, and Sentence_history form & table.

    Say I have just one field in each form and table eg Initial, how do I do a date update into a third table from both forms?

    I get what you are saying in the code, but trying to do it myself im not too hot at it!

    Cheers

    Tom

  9. #9
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    Hmm, I'm not sure else to explain it... but I'll try.

    Whenever the main form or the main form's subform is updated you need to have a macro or module code to set the date in which it was run.

    After that code is run you want to take that same time and update it into a table of your design choice which maintains the history of this update date.

    I don't think you need to develop relationships for this update table, I believe it's just a stand alone table (which might answer your question about how to have "two tables update the third table).

    Am I making sense?

    Let me know...

    Warren

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •