History of changes, when a patient changes nursing homes

[Guest]

I have a form with name, nursing home, funding status etc. I need to do an
audit trail when they leave a nursing home or when they are council funded
then they become self funding. I need a history in a datasheet subform -
how do I do this, I tried in a subform but it doesn't pick up when they have
left a nursing home.

Thanks

Elaine

 

[Larry Linson]

I have a form with name, nursing home,
funding status etc. I need to do an
audit trail when they leave a nursing home
or when they are council funded then they
become self funding. I need a history in
a datasheet subform - how do I do this, I
tried in a subform but it doesn't pick up
when they have left a nursing home.

If you can clarify, it's likely that someone will have some useful
suggestions. It's a little hard to offer advice since you've told us
"requirements" but nothing about the data you maintain, the Table layout,
etc., which is vital to what you want to do. There can be many reasons for
missing a Record (like the record of leaving that you mention) but without
details of what you have and what you are doing, it's an issue that we can't
address. It is almost certain that the difference between a Form embedded in
a Subform Control, and Datasheet View in a Subform Control is NOT the cause
of missing the record (assuming the record does exist).

Larry Linson
Microsoft Access MVP

 

[Guest]

I have a form, I want to put a subform of the history on it,

Name,
Nursing Home
Funding status

When a client leaves the nursing home and goes to another nursing home then
the previous nursing home needs to stay on the subform when the form is
updated...

I have created a form and a subform but I cannot get the subform to keep the
previous nursing home, is there a way of saving the previous data on the
subform without changing it when the whole form is saved.

Elaine

 

[SmartbizAustralia]

This sounds just like a fantastic bit of code that I picked up on the
internet.

Would love to give the author a plug.

If a nutshell all you require is to set the tag properties of any field
in any form that you want to keep an audit trail for. Then you can
display a filter view in your subform.

This the before update property to check what's changed and save it.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rs1 As Recordset, ctrl As Control
If Me.Dirty = True Then
Set rs1 = CurrentDb.OpenRecordset("tblAudit", dbOpenDynaset)
For Each ctrl In Me
If ctrl.Tag = "Audit" Then
If ctrl.OldValue <> ctrl.Value Then
rs1.AddNew
rs1![Form_Name] = Me.Name
rs1![Record_ID] = Me![EmployeeID]
rs1![Field_Altered] = ctrl.Name
rs1![DateTime_Altered] = Now
rs1![Altered_By] = CurrentUser()
rs1![From] = ctrl.OldValue
rs1![To] = ctrl.Value
rs1.Update
End If
End If
Next ctrl
rs1.Close
End If

End Sub

Hope this helps.

Tom Bizannes
Microsoft Certified Professional
Sydney Australia
Http://www.smartbiz.com.au

 

[7676]

I have a form, I want to put a subform of the history on it,

Name,
Nursing Home
Funding status

When a client leaves the nursing home and goes to another nursing home then
the previous nursing home needs to stay on the subform when the form is
updated...

I have created a form and a subform but I cannot get the subform to keep the
previous nursing home, is there a way of saving the previous data on the
subform without changing it when the whole form is saved.

Elaine

:

 

[7676]

I have a form, I want to put a subform of the history on it,

Name,
Nursing Home
Funding status

When a client leaves the nursing home and goes to another nursing home then
the previous nursing home needs to stay on the subform when the form is
updated...

I have created a form and a subform but I cannot get the subform to keep the
previous nursing home, is there a way of saving the previous data on the
subform without changing it when the whole form is saved.

Elaine

:

gracias

 

[Larry Linson]

Forms and subforms display data. But Tables are used to store data. If you
are, in fact, storing the information, then I imagine someone may be able
help you to be able to display it. But, as I wrote earlier, describing the
forms, subforms, and the idea of what you want to do is not enough
information for us to be of assistance.

Larry Linson
Microsoft Access MVP

 

[Larry Linson]

Fields are part of Tables and Queries and do not have a Tag Property. Forms
have a Tag property, Controls on Forms have a Tag property. That's what your
code is using, the Tag property of Controls on Forms.

The implication of the original poster, however, was that a record of the
events is already being saved, and rather than needing a separate audit
trail Table, needed to know how to display all the appropriate records.

Larry Linson
Microsoft Access MVP

This sounds just like a fantastic bit of code that I picked up on the
internet.

Would love to give the author a plug.

If a nutshell all you require is to set the tag properties of any field
in any form that you want to keep an audit trail for. Then you can
display a filter view in your subform.

This the before update property to check what's changed and save it.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rs1 As Recordset, ctrl As Control
If Me.Dirty = True Then
Set rs1 = CurrentDb.OpenRecordset("tblAudit", dbOpenDynaset)
For Each ctrl In Me
If ctrl.Tag = "Audit" Then
If ctrl.OldValue <> ctrl.Value Then
rs1.AddNew
rs1![Form_Name] = Me.Name
rs1![Record_ID] = Me![EmployeeID]
rs1![Field_Altered] = ctrl.Name
rs1![DateTime_Altered] = Now
rs1![Altered_By] = CurrentUser()
rs1![From] = ctrl.OldValue
rs1![To] = ctrl.Value
rs1.Update
End If
End If
Next ctrl
rs1.Close
End If

End Sub

Hope this helps.

Tom Bizannes
Microsoft Certified Professional
Sydney Australia
Http://www.smartbiz.com.au

I have a form with name, nursing home, funding status etc. I need to do
an
audit trail when they leave a nursing home or when they are council
funded
then they become self funding. I need a history in a datasheet
subform -
how do I do this, I tried in a subform but it doesn't pick up when they
have
left a nursing home.

Thanks

Elaine