How do I activate the code builder in the After Update event of a field in a TABLE in Access 2013?

[Melodyh]

Hi! I searched for the answer here, but just couldn't find it. Could you please help?



I have a database that lots of other people use. They prefer to work in the datasheet view of a query to add and update records (not in a form).

When they click off a radio button (Yes/No field) that a student has arrived to write a test, I want to fire an After Update event in the table that sends an email to someone IF certain conditions are met.

In order to program this, I know I open the table in datasheet view, click on the field name column header, go to the TABLE contextual tab, and click on the After Update button in the After Events group. All good. However, this opens up the MACRO builder. I don't want the macro builder...I want the CODE builder. I have written my code and it all works fine when based on a button in a form.

Is there any way this can be done? Or any other way? I'm totally open to ideas!!!!!

Thanks so much in advance!

Melody

[NTC]

a query's record set does not offer an event in which to do such a trigger

you will need to set up a datasheet style form

[aytee111]

Get the macro to run the code!

[HiTechCoach]

Melody,


Unfortunately in Access 2007 and later with a .accdb the default is to create a macro not VBA code.

I have a database that lots of other people use. They prefer to work in the datasheet view of a query to add and update records (not in a form).

I NEVER let users edit data except on a form.

I do get that it can be a lot faster to work in an excel like grid. I agree. Since I don't let my user, I don't let myself do it either.

I use a form in continuous view to show the data. This gives me the full power of form events, command buttons, and other controls. I sometimes will use a split form. My users, myself included, have found that this is actually preferred over editing in a query.

[ssanfu]

A little more info:

I know I open the table in datasheet view, click on the field name column header, go to the TABLE contextual tab, and click on the After Update button in the After Events group. All good. However, this opens up the MACRO builder.

What you describe is called a "Data Macro". It is a macro attached to/in the table.

There is not a "Code Builder" option in/for a table..

[Melodyh]

Hi! Thanks for your replies! HiTechCoach...I'm with ya! Not good "form" (heh) to let the end user edit directly in a table or query. I sort of inherited this database and have run with it as that's what they like.

I decided to try to just use the Data Macro in the After Update for the table. What I discovered was that the macro is triggered when ANY field in the record of that table is updated. I really just wanted an email sent if a particular radio button is checked off for a record. Apparently, that's not how it works!

I think I'm just going to have to put my foot down and insist that they use a form in continuous view. Then I can control the update event for that particular field.

Thanks for helping me think it all the way through, all!

Melody

[HiTechCoach]

Hi! Thanks for your replies! HiTechCoach...I'm with ya! Not good "form" (heh) to let the end user edit directly in a table or query. I sort of inherited this database and have run with it as that's what they like.

I decided to try to just use the Data Macro in the After Update for the table. What I discovered was that the macro is triggered when ANY field in the record of that table is updated. I really just wanted an email sent if a particular radio button is checked off for a record. Apparently, that's not how it works!

I think I'm just going to have to put my foot down and insist that they use a form in continuous view. Then I can control the update event for that particular field.

Thanks for helping me think it all the way through, all!

Melody

Melody,

You welcome. Glad we could assist.

Good luck with your project.

[CJ_London]

I think I'm just going to have to put my foot down and insist that they use a form in continuous view.

Not sure if it has been mentioned, but you can create a form in datasheet view and you can add code to the controls (although I do recommend continuous form as a rule). Problem with datasheet view form (in my opinion) is users can hide and even delete columns.