Track changes??

[Cooptoons]

We use infopath to enter information for parts that we repair and store it in
MS Access. We would like track changes that are made to the records. That way
we could have a history of repairs made to that part and if it comes back and
such. Right now everytime you make a change it overwrites the old record.
Is there any way to do this?

I have searched everywhere on the Internet and can't find anything.
Please help.

 

[foolycoolycurry]

I don't have an answer to your question cooptoons, but I am in much the same
boat so I'd also like and answer.

I use an InfoPath form that links with an Access DB. I would like to know
when a record was updated via InfoPath. I have included a "Date Last
Modified" field in the DB and form. I would like this date last modified
field to automatically change in Access when a record is modified in the
Infopath form, The next best option would be to not allow the person to
successfully Submit until they manually update the date modified field on the
form.

The one difference between cooptoons' request and mine is I don't need a
history, just the last date something was modified.

Perhaps an answer to your question would help me out. Anyone out there have
any ideas?

 

[S.Y.M. Wong-A-Ton]

I don't see this being implemented easily, but here is an idea: Create a
history table with the same structure as the parts table. Add an extra field
to the parts table and call it "dirty". Use rules to set this dirty field to
a value (e.g. TRUE) whenever the part pertaining to the dirty field is
change. You can then write code in the OnSubmitRequest to submit the form and
to loop through all the parts, detecting which ones are dirty, and for each
dirty one create a record in the history table. To create the records for the
latter you may have to use a web service or write ADO code. Should any other
"easier" solution come to me, I'll let you know...

 

[S.Y.M. Wong-A-Ton]

You can submit your form using rules (Tools > Submitting Forms). Once you've
set that then you can set up two rules that will fire when your form is being
submitted. The first rule must set the value of the LastModifiedDate to
InfoPath's now() or today() function, and the second rule must use the Main
data connection of your form to submit to a data connection, i.e., your
database.

 

[Cooptoons]

Both the form and the database are stored and run from over server but our IT
guy won't turn on web services. What is ADO code? Do you have any examples?
Or is there anyway to get a record to write a new record instead of
overwriting the origianl? Sorry for so many questions at once.

Thanks for your help thus far.

 

[foolycoolycurry]

Thanks for the suggestion. I have one last question.

When I query my DB it is common for me to pull up multiple records (query
all items in a certial room or building fo example). If a list of 10 items
is brought up and only two are edited, I only want those two to have their
"DateLastModified" field updated. Will your suggestion accomplish this or
would all 10 "DatLastModified" fields be updated upon Submit?

 

[S.Y.M. Wong-A-Ton]

Great question! Because it made me realize that my suggestion would only work
for one record.

A possible solution to updating more than one record would be to implement a
"dirty" field like I suggested to Cooptoons (see post), and then in the
OnSubmitRequest loop through all the modified records, set the DateModified
field and then submit. There is also a codeless solution, which is pretty
complicated to explain, and less elegant than when using code. What it boils
down to is using a helperField to detect to which records changes have been
made and then immediately update (on screen or hidden) the corresponding
DateModified field after every change to the record. When submitting the
form, the changed values of the DateModified field will be written to the
database.

 

[S.Y.M. Wong-A-Ton]

What is ADO code? Do you have any examples?

ADO is an "old-school" library containing objects to be able to access
databases. You can get some samples here:
http://msdn.microsoft.com/library/en-us/ado270/htm/mdmscadocodeexamplesinvbscript.asp?frame=true

Or is there anyway to get a record to write a new record instead of
overwriting the origianl?

Not that I know of.

Sorry for so many questions at once.

No worries.

Thanks for your help thus far.

You're welcome.