combine events to create a condition

[Guest]

I am trying to change the properties in a text box in my form. If changes (On
Change) are made to the text box and you move (On LostFocus) to a different
field, then I want to run a macro. I can’t figure out how to code this or
combine these two events as a condition.

 

[ruralguy via AccessMonster.com]

That event has already been created for you by Microsoft. It is called the
AfterUpdate event.

 

[Guest]

AfterUpdate is seemingly the obvious choice. I want to do something more
complicated and I should have given more details in my first note. Here is
some more information. I have a form where I am adding new customers into a
database. There are some text boxes and combo boxes. I also have two combo
boxes that allow searching existing records. I want to be able to add new
records and edit and delete existing ones. No big deal.

I don’t want Access to automatically save any edits that I make. I know that
can be a nice feature but I want to work around this built-in behavior. The
setup I want is to help avoid accidental changes in existing records. If I’m
editing an existing record, I want to be prompted (run a macro that I have
created) as I go to another field (Do you want to save this? Yes or No). If
I’m adding a new record, I don’t want to be prompted as I go to another
field. I would just be prompted at the end when I try to save or close the
form. AfterUpdate won’t allow for that flexibility. I get prompted if I’m
adding or editing a record. I think there needs to be some kind of
combination of two or more conditional events (On Changes and On LostFocus?)
or something like that. Help me make this a relatively goof-proof database.
Thanks.

 

[Douglas J. Steele]

Access doesn't actually save the changes until you move to another record,
not when you move to another field in the same record.

If you're concerned, put code into the form's BeforeUpdate event: that fires
just before Access tries to write to the table (and you can set Cancel =
True to stop the update from taking place)

 

[Guest]

Thanks for the input. However, my form is still unable to distinguish between
adding a record and editing a current record. I want to get prompted ONLY
when I’m editing an existing record. BeforeUpdate (like AfterUpdate) doesn’t
allow for that. I figure I need some additional code to allow for that
judgment to be made.

 

[Douglas J. Steele]

Look at the form's NewRecord property.

 

[Guest]

I am in the Form Properties (in the upper left of Design View of a Form) and
I don't see a NewRecord property. Are you talking about the command?

 

[Douglas J. Steele]

You won't find it in the Properties table, as it's not something you can set
during Design.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
MsgBox "You're trying to add a new record"
Else
MsgBox "You're trying to update an existing record"
End If
End Sub

As I said, if you want to prevent the update, you simply set Cancel = True.
If you want to undo the changes that were made, you use Me.Undo:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
MsgBox "You're trying to add a new record"
Else
MsgBox "I'm not going to let you update the existing record"
Me.Undo
Cancel = True
End If
End Sub

If you want to do checking, and let those records that pass your check get
through, put your logic in the appropriate branch of the If construct.