Accumulate Data

Posted on 2006-04-18
Last Modified: 2012-08-13
A Combo box allows changes in the status recorded for each record in a table (A).

I would like to accumulate a historical record of what's been changed, and when. So I want to set up a new table(B), containing 3 fields: an index(to link back to the main table A), the new status, and the date/time when it happened.

What would be the best trigger for an event associated with the Combo box? And what code will enable me to create a new record each time for the table B?


Question by:VaughanM
    LVL 54

    Expert Comment

    Best to use the BeforeUpdate event of the form.
    There you can use:

    currentdb.execute ("insert into tblLog (DateTimeUpdate, oldvalue, newvalue) values (Now(),'" & me.status.oldvalue & "','" & me.status & "'")

    Getting the idea ?


    Author Comment

    Hi Nic,

    Best to use the BeforeUpdate event of the form. - OK, got that.

    The  currentdb.execute is foreign territory to me - where can I find a good introduction to it? I'm OK with VBA, but I don't know the currentdb word.


    LVL 54

    Accepted Solution

    The "CurrentDb" is referring to the current database and saves the "trouble" in defining a Workspace and a Database before executing the acyion query.
    Just select the word and press F1 to get the context sensitive Help about the use of the CurrentDb.


    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now