BeforeUpdate event procedure interferes with record navigation 1

[mtnsurfer22]

I have written some code in the BeforeUpdate event of a form that will search all of the control boxes on the form and record any changes to those controls to another table (tblChanges) along with other relevant info. My problem is that when I use the standard record navigation buttons (which calls the BeforeUpdate event like I need it to) my changes are tracked to the table fine but the form never updates and continues to the the next record. The navigation buttons seem to simply just call my BeforeUpdate event and then "forget" about their other task of record navigation.

My guess is that it has something to do with my setting the focus to each control as I search for changed records. Perhaps this new focus is interfering with the exit and LostFocus events that need to occur next. However, I can't seem to access the properties of the current control unless it has the focus. I will paste my code below, any suggestions would be greatly appreciated.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctlC As Control
Dim rs As DAO.Recordset
Dim Index As Integer

Set rs = CurrentDb.OpenRecordset("tblChanges&quot

For Each ctlC In Me.Controls
If ctlC.Tag = "record" Then
ctlC.SetFocus
If ctlC.Text <> ctlC.OldValue Then
rs.AddNew
rs!UsersID = Me.UsersID
rs!Date = FormatDateTime(Date, vbShortDate)
rs!PreviousValue = ctlC.OldValue
rs!NewValue = ctlC.Text
rs!Field = ctlC.Name & &quot; -> &quot; & Me.Name
rs.Update
End If
End If
Next ctlC
End Sub


thanks
Eric

 

[Brewman76]

You shouldn't have to set focus in order to call the properties of a control. What problem do you have when you don't set focus? There is something else going on here that is causing this not to work.

I also noticed your code is referencing &quot;ctlC.OldValue&quot; which isn't a property of any standard control I can think of. That will also cause problems.

Adam

 

[mtnsurfer22]

Thanks for the reply. I left this problem for a bit to work on some other stuff and had acctually just fixed it when you replied. I was previously getting an error that stated something like...(&quot;Cannot access the properties of a control that does not have the focus&quot However, it must have been linked to something else because when I came back to it and removed the set focus command it works fine.

OldValue is working fine for me. As I understand it...Access keeps track of the 'OldValue' of a control untill after the AfterUpdate command of the form is called.

thanks again
eric