Generate report on data change

[dbl]

Hi I have a form called incident data which I use to record all incidents.
I need to generate a report if my Employer field is changed at any time
after we have printed our claims report, how would I go about coding this
action. The claims report is usually printed straight away.

I do all my reports the following morning so would I also need to add a date
field to record the date the change took place.

Any help would be much appreciated.

Bob

 

[Stefan Hoffmann]

hi Bob,

Hi I have a form called incident data which I use to record all incidents.
I need to generate a report if my Employer field is changed at any time
after we have printed our claims report, how would I go about coding this
action. The claims report is usually printed straight away.

Use

Private Sub Form_BeforeUpdate(Cancel As Integer)

If txtField.Value <> txtField.OldValue Then
MsgBox "data changed"
' or
' CurrentDb.Execute "INSERT INTO tblTrace" & _
' "(ID, OldValue, DateChanged) " & _
' "VALUES (" & Me![ID] & ", " & _
' txtField.OldValue & ", Now)"
End If

End Sub


mfG
--> stefan <--

 

[dbl]

Sefan I follow what you have done up to the MsgBox which works fine but the
second part of the code I do not understand, do I set up a tbl called
tblTrace with fields ID, OldValue, DateChange and Now? I can see that if I
had a tbl recording the changes it would be the way forward because I could
then generate the reports from that.

But I do not understand what's needed to make this part of the code work,
can you give me more detailed help please.

The ID field would this be my CustomerID field which is the auto number
field which identifies each record?

Thanks Bob

hi Bob,

Hi I have a form called incident data which I use to record all
incidents. I need to generate a report if my Employer field is changed at
any time after we have printed our claims report, how would I go about
coding this action. The claims report is usually printed straight away.

Use

Private Sub Form_BeforeUpdate(Cancel As Integer)

If txtField.Value <> txtField.OldValue Then
MsgBox "data changed"
' or
' CurrentDb.Execute "INSERT INTO tblTrace" & _
' "(ID, OldValue, DateChanged) " & _
' "VALUES (" & Me![ID] & ", " & _
' txtField.OldValue & ", Now)"
End If

End Sub


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Bob,

do I set up a tbl called tblTrace with fields ID, OldValue, DateChange and Now? Yes.

I can see that if I had a tbl recording the changes it would be the
way forward because I could then generate the reports from that.

That is, what i've read in yout OP.

The ID field would this be my CustomerID field which is the auto number
field which identifies each record?

The trace table field ID stores your CustomerID, so it must be a long
number.

Remove the '.

' CurrentDb.Execute "INSERT INTO tblTrace" & _
' "(ID, OldValue, DateChanged) " & _
' "VALUES (" & Me![ID] & ", " & _ And replace Me![ID] with Me![CustomerID].
' txtField.OldValue & ", Now)"

Replace txtField with the name of the control, which is bound to your
field you like to trace.



mfG
--> stefan <--

 

[dbl]

Hi Stefan I have entered the code as below, the Field143 is my CustomerID

When I change the Employer in the Combo list the MsgBox appears when you
click ok on this the Error code 3075 with the following

Msg Sytax Error (missing operator) in query expression employers name ( the
employers name is what ever you changed it from)

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Employer.Value <> Employer.OldValue Then
MsgBox "The Data In This Field Has Been Changed"
CurrentDb.Execute "INSERT INTO tblTrace" & _
"(ID, OldValue, DateChanged) " & _
"VALUES (" & Me![Field143] & ", " & _
Employer.OldValue & ", Now)"
End If


End Sub

any ideas

Bob

hi Bob,

do I set up a tbl called tblTrace with fields ID, OldValue, DateChange
and Now? Yes.

I can see that if I had a tbl recording the changes it would be the
way forward because I could then generate the reports from that.

That is, what i've read in yout OP.

The ID field would this be my CustomerID field which is the auto number
field which identifies each record?

The trace table field ID stores your CustomerID, so it must be a long
number.

Remove the '.

' CurrentDb.Execute "INSERT INTO tblTrace" & _
' "(ID, OldValue, DateChanged) " & _
' "VALUES (" & Me![ID] & ", " & _ And replace Me![ID] with Me![CustomerID].
' txtField.OldValue & ", Now)"

Replace txtField with the name of the control, which is bound to your
field you like to trace.



mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Bob,

When I change the Employer in the Combo list the MsgBox appears when you
click ok on this the Error code 3075 with the following
Msg Sytax Error (missing operator) in query expression employers name ( the
employers name is what ever you changed it from)

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Employer.Value <> Employer.OldValue Then

Is Employer the name of your field or the name of your control? If field
and control have the same name, then rename the control to txtEmployer.

CurrentDb.Execute "INSERT INTO tblTrace" & _
"(ID, OldValue, DateChanged) " & _
"VALUES (" & Me![Field143] & ", " & _
Employer.OldValue & ", Now)"

Is Employer a string? Then you have to use single quotes:

"VALUES (" & Me![Field143] & ", '" & _
Replace(Employer.OldValue, "'", "''") & "', Now)"


mfG
--> stefan <--

 

[dbl]

Stefan I have made those changes it now highlights ![Field143]
and comes up with the following error

Compile error: Method or data member not found

The control source for this field is Telephone Checklist.CustomerID

If I type Me.Fie it automatically finds the Field143

Can you see what's wrong?

Bob

hi Bob,

When I change the Employer in the Combo list the MsgBox appears when you
click ok on this the Error code 3075 with the following
Msg Sytax Error (missing operator) in query expression employers name (
the employers name is what ever you changed it from)

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Employer.Value <> Employer.OldValue Then

Is Employer the name of your field or the name of your control? If field
and control have the same name, then rename the control to txtEmployer.

CurrentDb.Execute "INSERT INTO tblTrace" & _
"(ID, OldValue, DateChanged) " & _
"VALUES (" & Me![Field143] & ", " & _
Employer.OldValue & ", Now)"

Is Employer a string? Then you have to use single quotes:

"VALUES (" & Me![Field143] & ", '" & _
Replace(Employer.OldValue, "'", "''") & "', Now)"


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Bob,

Stefan I have made those changes it now highlights ![Field143]
and comes up with the following error

Try Me![Field142].Value.


mfG
--> stefan <--

 

[dbl]

Stefan it has now moved further down the code to (Employer.OldValue) which
it has highlighted the OldValue and the error msg Method or data not found.

Regards Bob

hi Bob,

Stefan I have made those changes it now highlights ![Field143]
and comes up with the following error

Try Me![Field142].Value.


mfG
--> stefan <--