Hello All:

I have a table named "ProductivityAmount" which contains a bit value for a column named "EAC". In another table named "Productivity" I have a text lookup column named "Type". What I would like is this, when "EAC"=TRUE AND "Type"=1, I would like to RaiseError message when these two values are selected within a main form, so when these are met I want the user prevented from submitting this form. My thinking was this could be accomplish within the "ProductivityAmount" Before Change macro.

I have tried various things but I cannot seem to get the value within the "Productivity" "Type" column to be seen in the IF statement. I know that I am able to validate the "EAC" bit column but this is where I've placed the Before Chnage Macro. I suspect I need LookupRecord but these seem to confound me.

Any help would be appreciated and I have included the tbls referenced above in the attached screen shots. Please note that the "ProductivityType" tbl supports the "Type" column lookup within the "Productivity" tbl.

Thank you

Are you doing this in a form or datasheet? In a form, in the afterupdate event of the field control, set up a tempvar with the value of the control. Use the tempvar in the BeforeChange macro rather than referring directly to the field.

Bonnie

Bonnie,

Thanks for your assistance. I am doing this in a "ProductivityAmount" tbl which is where I believe the Before Change macro should reside. Regarding your guidance; would you be able to provide an example of what it is you're suggesting? I tried it on my own and I must be missing something when I place SetTempVar on the control within my main form as intellisense isn't picking it up.

This post has been edited by gdgonzal: May 24 2012, 10:56 AM

Excuse me, it should be AfterUpdate event of the control.

SetTempVar

Name: MTempvar (or whatever name you give it)
Expression: [controlname]

Then refer to this tempvar in the BeforeUpdate validation instead of the field name.

Bonnie