Results 1 to 10 of 10

Thread: Access 200

  1. #1
    Join Date
    Dec 2002
    Posts
    7

    Unanswered: Access 2000

    I use a form to select a record from a table and update (change) any of the fields displayed. When the form is closed any changes are saved to the table. What I would like to do is store a copy of the changes to certain fields in another table so I can create a running history report. Using this update form, how do I direct the changes of just certain fields to the new table (as well as the old) and only when these certain fields actually change?
    Last edited by David Paul; 12-12-02 at 20:19.

  2. #2
    Join Date
    Sep 2002
    Location
    Land of OZ
    Posts
    173

    Re: Access 2000

    What you need is some code that loops through all the fields on the form when the form opens and capture these values and store it into an array.

    On closing of the form, check if the form is dirty, if it is, loop again to pick up the new values. Do a compasion of the two arrays and enter fields using an insert statement.

    OR you can write code for each fieldm check the value on enter and on exit ... this I would not recommend.

    If you need help with the code just let me know ...

    Originally posted by David Paul
    I use a form to select a record from a table and update (change) any of the fields displayed. When the form is closed any changes are saved to the table. What I would like to do is store a copy of the changes to certain fields in another table so I can create a running history report. Using this update form, how do I direct the changes of just certain fields to the new table (as well as the old) and only when these certain fields actually change?
    Mona
    ________________________________
    Life is too short to be sane or sensible. Weird people rule and normal people suck

  3. #3
    Join Date
    Dec 2002
    Posts
    7

    Re: Access 2000

    Originally posted by mona
    What you need is some code that loops through all the fields on the form when the form opens and capture these values and store it into an array.

    On closing of the form, check if the form is dirty, if it is, loop again to pick up the new values. Do a compasion of the two arrays and enter fields using an insert statement.

    OR you can write code for each fieldm check the value on enter and on exit ... this I would not recommend.

    If you need help with the code just let me know ...
    Thanks Mona for the input.
    Yes, I do need some help with the code. If you could give me an example using the format below, I would greatly appreciate it. I'm looking to keep a history on, say Status and Status Date.

    Table: Projects
    Fields: Number, Title, Status, Status Date

  4. #4
    Join Date
    Sep 2002
    Location
    Land of OZ
    Posts
    173

    Re: Access 2000

    Hi David,

    I mocked up a small database. I didn't really have time to comment much in the code, but I will when I get some spare time this week.

    The Status and Status Date fields on the form have a tag "Check" and the code relies on this to save the details. It will check any field that has the tag Check to see if has changed and save those details into the History table...



    Originally posted by David Paul
    Thanks Mona for the input.
    Yes, I do need some help with the code. If you could give me an example using the format below, I would greatly appreciate it. I'm looking to keep a history on, say Status and Status Date.

    Table: Projects
    Fields: Number, Title, Status, Status Date
    Attached Files Attached Files
    Mona
    ________________________________
    Life is too short to be sane or sensible. Weird people rule and normal people suck

  5. #5
    Join Date
    Dec 2002
    Posts
    7
    Hi Mona,
    Tried your code. It does capture the information I'm looking for, but not quite in the way I'd like. My ultimate goal is to write the Project Number (which never changes) to the history table along with the Status and Status Date fields, all in the same record. (Your code gives me a new record for each field.) As for the the Status and Status Date fields, both could change or either could change. I would need a record to reflect the changed field(s) as well as the unchanged one(s). In other words, if either field changed, I'd need to write all the fields to the history table. --- I'm going to use the project number field to link to another table to produce reports.

    David

  6. #6
    Join Date
    Sep 2002
    Location
    Land of OZ
    Posts
    173
    Can you give me an idea of what your history table would look like... I mean the fields ... and how they would be entered into the table ...

    Originally posted by David Paul
    Hi Mona,
    Tried your code. It does capture the information I'm looking for, but not quite in the way I'd like. My ultimate goal is to write the Project Number (which never changes) to the history table along with the Status and Status Date fields, all in the same record. (Your code gives me a new record for each field.) As for the the Status and Status Date fields, both could change or either could change. I would need a record to reflect the changed field(s) as well as the unchanged one(s). In other words, if either field changed, I'd need to write all the fields to the history table. --- I'm going to use the project number field to link to another table to produce reports.

    David
    Mona
    ________________________________
    Life is too short to be sane or sensible. Weird people rule and normal people suck

  7. #7
    Join Date
    Dec 2002
    Posts
    7
    Table Projects:
    Project Number (unique text field)(Primary key)
    Project Title (text field)
    Project Description (text field)
    Current Status (text field - Last status entered)
    Status Date (date field - Date of status update)

    Table History: (add new record each time status changes)
    Project Number (Text field - duplicates OK)(Primary key with Status Date field)
    Status Date (Date field)(Primary key with Project number field)
    Old Status ( could be previous status written after update or current status written each time it's updated.)

    When a project begins, a new record is created in the Projects table.
    (Using a form: ADD NEW PROJECT)
    Project Number is assigned, a Title and Description of the project is entered along with the current status (for example: Planning Phase)
    and the date the status was entered.
    To update the status of a project a form is used: UPDATE PROJECT
    The History table could have a record written when the project is first
    entered with the status and status date. Then, each time the status changes in the Projects table a new record would need to be written to
    the History table. -- This method would involve code for both forms.
    Then I could get all the status information for a report from the History table.
    OR -- The history table could have records added only when the status
    changes (using the UPDATE PROJECT form). This means code for only
    one form. I could pull the current status from the Projects table and all past status(es) from the History table into a report.

    Example of History records:

    1004A 11/01/2002 Planning Phase
    1004A 12/02/2002 Designing of Building
    1004A 12/16/2002 Construction Begins

    Does this help??

    David

  8. #8
    Join Date
    Sep 2002
    Location
    Land of OZ
    Posts
    173
    OK no problems .... this is alot easier that I first envisaged ... scrap the original stuff I sent ....

    Please see new attached DB ... it is A2K ... let me know if you need it in 97 ...





    Originally posted by David Paul
    Table Projects:
    Project Number (unique text field)(Primary key)
    Project Title (text field)
    Project Description (text field)
    Current Status (text field - Last status entered)
    Status Date (date field - Date of status update)

    Table History: (add new record each time status changes)
    Project Number (Text field - duplicates OK)(Primary key with Status Date field)
    Status Date (Date field)(Primary key with Project number field)
    Old Status ( could be previous status written after update or current status written each time it's updated.)

    When a project begins, a new record is created in the Projects table.
    (Using a form: ADD NEW PROJECT)
    Project Number is assigned, a Title and Description of the project is entered along with the current status (for example: Planning Phase)
    and the date the status was entered.
    To update the status of a project a form is used: UPDATE PROJECT
    The History table could have a record written when the project is first
    entered with the status and status date. Then, each time the status changes in the Projects table a new record would need to be written to
    the History table. -- This method would involve code for both forms.
    Then I could get all the status information for a report from the History table.
    OR -- The history table could have records added only when the status
    changes (using the UPDATE PROJECT form). This means code for only
    one form. I could pull the current status from the Projects table and all past status(es) from the History table into a report.

    Example of History records:

    1004A 11/01/2002 Planning Phase
    1004A 12/02/2002 Designing of Building
    1004A 12/16/2002 Construction Begins

    Does this help??

    David
    Attached Files Attached Files
    Mona
    ________________________________
    Life is too short to be sane or sensible. Weird people rule and normal people suck

  9. #9
    Join Date
    Dec 2002
    Posts
    7
    Thanks again Mona,
    Access 2000 is fine. I'll take a look at it.

    David

  10. #10
    Join Date
    Dec 2002
    Posts
    7
    Mona,
    Made a few changes:
    1. Created a from to add new records to the Projects table. It populates all fields in the Projects table and duplicates some of them in the History table. (This is what I want. All instances of status will be in the History table for easy reporting.)
    2. Created a form to Update records in the Projects table. It changes fields in the Projects table but doesn't add new records to the History table. ??? Can't figure out what I'm not doing.
    Just figured it out -- had History table field ProjectNumber set to no duplicates instead of duplicates ok. Needs to be changed in this zip file.
    I noticed that all fields get written to the history table instead of only the updated ones. (If only the Funded field changes, Status and StatusDate fields get written also.) Would like the unchanged fields to be blank in the History table.
    See attached loops01

    David
    Attached Files Attached Files
    Last edited by David Paul; 12-19-02 at 19:01.

Posting Permissions

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