Conditional Changes?

[SubMatrix]

I have a database that is used by half a dozen users and reviewed by one. One of the fields in most of the tables is a Yes/No control field called "New/Edited". This field is not visible in the forms that the users have access to and defaults to "Yes". This way if a new record is created, the admin sees it, reviews it, and then unchecks it.

What I am trying to do is make it so that if a previously reviewed record is altered in a form, it will automatically change the value of that field back to "Yes", so that the admin knows there is a change that may need to be reviewed.

I have fumbled around witht he expressions, macro, and code builder and did little more than confuse myself. Any help anyone could offer would be greatly appreciated.

 

[RuralGuy]

The BeforeUpdate event of a form fires just before a record is changed. It is that event in the form where you want to set the field back to yes.

 

[missinglinq]

In the user forms (but not in the forms used the reviewer!)

Private Sub Form_BeforeUpdate(Cancel As Integer)
  'Validation code, if any
  Me.YourNewEditedCheckBox = -1
End Sub

Assuming that your checkbox is on the user form with its Visibility Property set to No.Replacing YourNewEditedCheckBox with the actual name of your checkbox. Hopefully you don't actually have a slash mark in the name. If you do, in VBA, Access will convert the slash mark to an underline.

With this code, you don't even have to have the Default Value set to Yes , as it will cover new records as well.

As indicated, the line

Me.YourNewEditedCheckBox = -1

must come after any Validation code you have, so that if the user changes his mind and cancels the update, the Yes/No field won't be changed.

Also note, as indicated, that this code cannot be used in the reviewer's form! The very act of changing the checkbox from Yes to No would once again cause the Form_BeforeUpdate event to fire, and the checkbox would then be set back to Yes!

 

[SubMatrix]

The field was never added to the form as the field was added after the form was created. The form is based off of a query which is used as a source for other forms and reports. When I went to add the field to the form it said that it would have to create a new query and no longer use the old one. I also have a bunch of validation expressions that reference the original query so changing it to a new one would be possible but a huge headache.

Is there code that will allow it to reference the table directly or do I have to have the field listed in the form for it to work?

Also, I did use a slash mark in the field name (didn't knowit was a no-no ). Will it still work with that or should that be changed as well.

If it helps with examples any, the Table and Form are both called "Actions" and the field is called "New/Edited"

 

[missinglinq]

Why can't you simply open the query in Design View and add the field, then add it to the forms?

You can use the name New/Edited as the name of the checkbox, with the slash mark, just be aware that when referring to it in VBA code, you have to use New_Edited, with the underscore replacing the slash. VBA does the same thing if you use a control name with a space in it, thus, in the code module, My Control has be referred to as My_Control.

 

[RuralGuy]

I for one would just start training yourself to *not* use those special characters or reserved words in Access.
Problem names and reserved words in Access
Error message when you use special characters in Access databases

 

[missinglinq]

I totally agree with, Allan! If you just have to stick something between components of a name, use the Underscore; that's what Access is going to turn it into anyway. But I personally think using something like NameOfYourControl is the easiest to type and read.