Error message on Search

[Kila]

I have a form that has a wizard-generated search box to find a particular record. This particular form also has a field that automatically logs "Now()" when you change records. The problem is that sometimes when you use the search box to locate a particular record, if you make a very minimal change, such as only checking a checkbox, or enter a field & make no changes (sometimes), you get an error when you try to quickly leave the record using the search box...but not every time. You can go to "next record" with no problems at all, but sometimes searching for another record with the search field generates the following error:

Runtime error '3020'
Update or CancelUpdate without AddNew or Edit

If you Debug, it goes to this code, but I am not sure what is wrong with it...

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Me.txtRecordUpdated = Now()
End Sub

Just as an FYI, ALL the code for the form is attached below. What I am TRYING to do is have the form notate the date & time the record was updated any time the form or either of its subforms is changed. (BTW...it does NOT seem to make this notation for the subform updates) I suspect I am not doing this quite right, but I do not know how to fix it. Can anyone help?

Here is the form code:

Option Compare Database
Option Explicit

Private Sub cboNameSearch_AfterUpdate()
    ' Find the record that matches the control.
    Me.RecordsetClone.FindFirst "[SSN] = '" & Me![cboNameSearch] & "'"
    Me.Bookmark = Me.RecordsetClone.Bookmark
    Me.cboNameSearch = Null
End Sub

Private Sub cboSSNSearch_AfterUpdate()
    ' Find the record that matches the control.
    Me.RecordsetClone.FindFirst "[SSN] = '" & Me![cboSSNSearch] & "'"
    Me.Bookmark = Me.RecordsetClone.Bookmark
    Me.cboSSNSearch = Null
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Me.txtRecordUpdated = Now()
End Sub

Private Sub subfrmActions_Exit(Cancel As Integer)
  Me.txtRecordUpdated = Now()
End Sub

Private Sub subfrmRoster_Exit(Cancel As Integer)
  Me.txtRecordUpdated = Now()
End Sub

Thanks for any suggestions!

 

[adam_fleck]

Maybe someone else will explain this properly

I've run across this problem too.

I don't know why, but a form's BeforeUpdate event doesn't seem to let you do anything that changes the value of any of the controls on the form.

Maybe this is because in order to run, BeforeUpdate needs to have the final values of all the controls on the form. If you try to do something that interferes with this business of collecting the final values, BU breaks. Fair enough.

There are two things you can do.

1) Delete the control that contains the timestamp (txtRecordUpdated). There is now no timestamp field on your form. However, if the name of your time stamp field in the underlying table is the same as the name of the control you've just deleted, then you're OK.

This is because, oddly enough, an Access form can write to a field in an underlying record, even when the field has not been dropped onto the form. (If the field in the underlying table is not called txtRecordUpdated, change your code so that it refers to the field as it is named in the table, e.g.,

    Me.Name_of_TS_Field_in_Table = Now[CODE]

or

2)

Put your  "=Now" code on the BeforeUpdate events of all controls on your form that your user could edit.

 

[Kila]

Thanks!

Thanks! I will try that! But what do I do if I want to see the field, so that when I enter, I know when it was last updated? If I keep the control, but update the record instead, will that work?

 

[adam_fleck]

Yep, that'll work

So long as the control is called say txt_TimeStamp and the field is called
fld_TimeStamp and you use

Me.fld_TimeStamp = Now

You should be OK

 

[Kila]

It worked!

That worked! Thank you! Now, along a related theme, how can I get the date stamp to update whenever ONLY a subform is edited? I have the command there, but it does not seem to work.

Currently, the code is:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Me.RecordUpdated = Now()
End Sub

Private Sub subfrmActions_Exit(Cancel As Integer)
 tblNameList.RecordUpdated = Now()
End Sub

Private Sub subfrmRoster_Exit(Cancel As Integer)
 tblNameList.RecordUpdated = Now()
End Sub

Thanks for your help.

 

[adam_fleck]

Not sure

But you could try putting code like this on the Before Update Event for each of the subforms.

    Me.Parent.RecordUpdated  = Now

If that doesn't work, try making a public function on the main form:

Public Function updateTimeStamp(dteUpdate as Date)

    Me.RecordUpdated = dteUpdate
 
End Function

Then put code to call this on the BeforeUpdate event of each subform

    Form_Main_Form_Name.updateTimeStamp Now

(The name of a form in code is the name of the form, prefixed by Form_, as shown in the Project Explorer)

 

[Kila]

That worked!

The 1st one did it. Thanks!