form.activecontrol.oldvalue not working

[LokeshSundar]

Hi Team,

i am using Office 365 and i have Access working. I am trying to do audit for the form and i have the code to track changes and which is working for dropdown list but not the textbox. Below is the function which i am using to track changes and i get error on the "frm.ActiveControl.OldValue" line. but the same line working fine for dropdownlist but not for textbox. i have a dollar value for the textbox. this code was working fine before friday(27/9/2019). but post friday it is not working. This also works for string values but not for dollar values

Function Trackchanges()
Dim db As DAO.database
Dim rs As DAO.Recordset
Dim strSql As String
Dim strCtl As String
Dim strReason As String
Dim Number As Double
Dim frm As Form

Dim ctl As Control


Dim OldVal
Dim NewVal


Set frm = Forms!frm_Opportunity

'strReason = InputBox("Reason For Changes")
strCtl = frm.ActiveControl.Name
strSql = "SELECT * FROM tbl_Audit;"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSql, dbOpenDynaset)
'Debug.Print frm.ActiveControl.OldValue
'OldVal = CStr(frm.ActiveControl.OldValue)

If rs.RecordCount > 0 Then rs.MoveLast

With rs
.AddNew
rs!RecNo = frm.txtOppNbr
rs!FormName = frm.Name
rs!CreatedByUserID = frm.UserIDCreated
rs!ChangedByUserID = fOSUserName
rs!UserName = frm.LastModifiedBy
rs!ControlName = strCtl
rs!DateChanged = Date
rs!TimeChanged = Time()
rs!PriorInfo = frm.ActiveControl.OldValue <------- Error is appearing here
rs!NewInfo = frm.ActiveControl.Value
'rs!CurrentUser = fOSUserName
rs!Reason = strReason
.Update
End With
Set db = Nothing
Set rs = Nothing
End Function



These are the error message which i get when i run the database.

Thanks & regards,
Lokesh S

[Minty]

I'm guessing this is being called in the before update event ?
If it did work and now it doesn't something has been changed. That's either data or a control name.

What is the result of your debug statements, and is the control bound ? it won't work on a calculated control or unbound one from memory.

[LokeshSundar]

This is a bound control which is called in before update statement, and no control name is changed and it was working till last friday. it is a calculated bound control which was working before.

[Minty]

I assume from your answer that it is calculated in the forms underlying query, rather than on the form ? If it is the latter it won't have a an oldvalue hence your error.

What is the result of your debug statements that are commented out?

[Micron]

1) please use code tags (# on forum menu bar) and indentation to make your code more readable.
2)

it is a calculated bound control

That makes no sense. A bound control cannot have a calculation as its recordsource. As previously stated, a calculated control has no OldValue property, which is probably the reason for the error message. I'm going to have to ignore your claim that it was working before and yet nothing was changed because you cannot successfully reference a control property that does not exist one day only to have it fail another day. Something would have to be different, even if it was just the way that the code executed. Perhaps some other control (like a button) has the focus because this is a function and not a sub that's tied to a control event? No harm, but why make it a function if it's not going to return any value? One other comment if I may. If you're going to create a With block for rs, no point in repeating the object variable name for every field update.

Lastly, Number is a reserved word and you should not use it for object names.

[orange]

LokeshSundar,
Here is a link to some comments and an example using data macros for audit trail.

More info and discussion here.

[moke123]

rs!PriorInfo = frm.ActiveControl.OldValue <------- Error is appearing here

Is the control in fact Active? I'm familiar with Screen.ActiveControl but not frm.ActiveControl. Doesn't the control need focus to be active?

[Micron]

Is the control in fact Active? I'm familiar with Screen.ActiveControl but not frm.ActiveControl. Doesn't the control need focus to be active?

.ActiveControl is a property of a form yet the Micro$oft documentation uses the Screen object in conjunction with it, not the form. One of the more confusing explanations by M$
https://docs.microsoft.com/en-us/off....activecontrol

Oddly enough, Forms!formName.ActiveControl works in the immediate window but won't compile in code

[LokeshSundar]

Hi,

I have tried with the

screen.activecontrol.oldvalue

and

form.activecontrol.oldvalue

and both are not working. i have retrived the database when it was working fine and it is same with that database as well. Any Help would be helpfull.

Function TrackChanges()
Dim db As DAO.database
Dim rs As DAO.Recordset
'Dim rs As ADODB.Recordset

Dim strSql As String
Dim strCtl As String
Dim strReason As String
Dim Number As Double
Dim frm As Form_frm_Opportunity

Set frm = Forms!frm_Opportunity

'strReason = InputBox("Reason For Changes")
strCtl = frm.ActiveControl.Name
strSql = "SELECT * FROM tbl_Audit"



'Set db = CurrentDb()
Set rs = CurrentDb.OpenRecordset("tbl_Audit")


If rs.RecordCount > 0 Then rs.MoveLast

With rs
.AddNew
rs!RecNo = frm.txtOppNbr
rs!FormName = frm.Name
rs!CreatedByUserID = frm.UserIDCreated
rs!ChangedByUserID = fOSUserName
rs!UserName = frm.LastModifiedBy
rs!ControlName = strCtl
rs!DateChanged = Date
rs!TimeChanged = Time()
rs!PriorInfo = Screen.ActiveControl.OldValue
rs!NewInfo = Screen.ActiveControl.Value
rs!Reason = strReason
.Update
End With

Set db = Nothing
Set rs = Nothing
End Function

[Micron]

I think you will have to post a compacted and zipped copy of your db for analysis. Last ditch effort here - you could check for missing or broken references. I also see that one of the messages seems to be coming from another application - Opexb Pipeline? If it is something that we need in order to test your code, then posting the db won't help.

[LokeshSundar]

i was unable to update the database here so i am pasing the link for the database saved in the onedrive.
https://1drv.ms/u/s!ArDY-GvljhEQhHrv...D5H4p?e=wR1O1h

[Minty]

i have retrived the database when it was working fine and it is same with that database as well. Any Help would be helpfull.

This would tell me that either it didn't actually really work, or something in your backend data structure has changed to stop it working.

If an old unchanged version isn't working one of the two scenarios above must be the cause,or some external update has stopped it functioning.

[LokeshSundar]

I have not changed any code or properties of the form or in the module. But how can a error occur on Office update.

[Minty]

I have not changed any code or properties of the form or in the module. But how can a error occur on Office update.

Unfortunately frequently if you have a read on various sites including here.
I'm afraid I can't open your database -I'm on 2016 and it's complaining I need a newer version to open yours.

I'm not convinced about your description of a calculated bound control either. What is it's control source?

[LokeshSundar]

Control source is a query, which is connected to a table. you can hold shift and click the database it will open in the design mode