Results 1 to 7 of 7
  1. #1
    Join Date
    May 2009
    Posts
    12

    Unanswered: revision date each time information modified

    Hi All,
    as the title say's, i need to have a date change each time i either add or modify information in my data base
    It is a Roster type database, and as such each time it is modified it will need to be printed, so i want the newest version to have the latest date on it in a particular place(revision_date), but i might need to print it without changes so the (revision_date) would need to stay the same as it was.
    I am using this date as a type of revision number.
    I hope someone can give me a bit of help on this one.
    cheers
    Geoff

    Please if i am not making myself clear, could you tell me so i can try to fix, i have another question from a few days ago but as yet hasn't had a response, and i am not sure if it is because i have worded my question wrong or there is no apparent fix.

    thanks

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try updating the date in the after update event of the form. That event should not fire unless a change is made.
    Paul

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If all you need to do is keep track of the latest version date, then just have a table (call it: tblVersionDate) which has 1 field (ie. VersionDate - Date data type) and 1 record (the latest version date.) You could then write a simple piece of code to update it when you push a button: ie.(ADO coding)

    Function UpdateVersionDate()
    Dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from tblVersionDate"
    if rs.eof and rs.bof 'no records exit
    rs.addnew
    end if
    rs!VersionDate = Date() 'or Now() if you want the time
    rs.update
    rs.close
    set rs = nothing
    End Function

    Then just call that function (usually by pushing a button) whenever you want to update the version.

    If you want to track history of versions and add notes, it may then be something like this:

    Function UpdateVersionDate()
    Dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from tblVersionDate"
    rs.addnew
    rs!VersionDate = Date() 'or Now() if you want the time
    Dim txtNotes as variant
    txtNotes = inputbox("Enter notes for this version date:")
    if isnull(txtNotes) then
    msgbox "No notes will be saved for this version date."
    else
    rs!VersionNote = txtNotes
    end if
    rs.update
    rs.close
    set rs = nothing
    End Function
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    As Paul also pointed out...

    What I'll do is have a DateModified (date type) and ModifiedBy (text type) fields in the table (along with a DateEntered and EnteredBy fields). Then on the form, in the form's beforeupdate event, I'll have code like this:
    me!DateModified = Date() 'or Now()
    me!ModifiedBy = getuser() ' this is from the getuser routine in the code bank which grabs the current user logged in. After the record has been entered, if anyone goes back into that record at a later date and makes a change, the DateModified will change to that day and the Modifiedby to who modified it (from the getuser routine). But you won't know what field was changed and the old value so this type of tracking is really only good for identifying that "someone or the getuser name" changed "some field value" (and could've also changed it back to the original value) for that record on the DateModified. With this type of routine you can't track specifics about what data was changed and the values so it is limited on what you're going to get out of it.

    If you want to keep track of multiple changes or what fields were specifically changed, that gets a little more into coding and further structure setup (and often unnecessary except for extremely important tracking of this information is needed for high crisis type items - it also makes things a bit complex.) SQL Server also does a very nice job of tracking changes to what fields (automatically), when they took place, old value versus new value, and who made them. MSAccess has to be setup/programmed to do this kind of tracking (which can get a tad tricky.)
    Last edited by pkstormy; 05-22-09 at 01:38.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Otherwise, if you're just printing it to a report, simply put in an unbound text field on the report and set the sourceobject property = Date(). Then when it prints, the current date will always print on that report and you have a hard copy. (you could also then write code to write that date into a field in the table where all the roster records are printing from.) - a simple update query could take care of this and you can have the query run before the report opens.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I know I gave you a lot of different answers here. I guess I didn't fully understand your problem and hence gave you some of the common approaches to what I think was your issue. My apologies if I didn't answer your original question.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    May 2009
    Posts
    12
    hi pkstormy,
    thanks heaps for the reply mate,
    i am not at work at the moment so when i get back i will try some of your sugestions.
    cheers
    geoff

Posting Permissions

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