Web App Data Macro Issue

[jollsamox]

Hi, hoping someone can help! I've got a web app, with three tables: Projects, Tasks and Staff.
In the tasks, there is a section for who the task is assigned to - a lookup value from the staff table. I have managed to successfully create a macro where on insert the email address of the person assigned to the task is found in the Staff table.

Look up record in Staff, where [staff].[id]=[Tasks].[Task Owner].
Set local var expression = [Staff].[Emil Address]

However, when i try to create a data macro to do the same elsewhere, it says it can't resolve the reference to Staff. Basically, i'm trying to do a similar thing in my Projects table, where if the project status is changed to completed (lookup value manually entered) an email is sent to all the Staff listed as stakeholders for that project.

Can anyone help?

 

[AccessJunkie]

Hi,

Could you perhaps post a screenshot of the macro window showing what you have currently that is not working? Or perhaps writing out exactly the logic you have tried in the other table events.

That would help us figure out what the potential resolution is.

Thanks,

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
Senior Content Developer - Modern Assistance and Support Experience - Microsoft Corporation

Author - Microsoft Access 2013 Inside Out
Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010/2013 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/en-us/legal/Copyright/Default.aspx
----------

 

[jollsamox]

Hi Thanks for helping. Please find attached two screen shots. When i try to create a data macro it says it is unable to resolve the reference to 'Tasks'. Oddly, this sometimes changes to 'Stakeholders'.

Any ideas!?

 

[AccessJunkie]

Hi,

Thanks for the extra screenshots, that was very helpful. I think I see the problem now.

The issue is this SetLocalVar action inside the LookUpRecord action:

SetLocalVar
Name varTargetDate
Expression =[Tasks].[Target Completion Date]


The reason for the error here is that you are currently inside the context of the Stakeholders LookUpRecord action block. Access has no idea at that point in time what the Tasks table is nor how to get a value from it. Think of a LookupRecord block, or a ForEachRecord block as well, as a type of bubble and you can only interact with things in that bubble or things you have added into the bubble.

So for that specific SetLocalVar action, Access is essentially saying, "Wait, I'm inside the Stakeholders table right now so I have no idea what this Tasks table is."

So how do you fix this?

If you want to use something from the Tasks table (like a field value) you need to assign those variables *before* you enter the LookupRecord action. Once you assign those variables, then you can use them within the context of the Stakeholders LookupRecord action.

So the easiest thing to do is click and drag that specific SetLocalVar action out of the LookupRecord action and place it right before it. In this manner, Access will read the value of the Target Completion Date field from the Tasks table, assign it to the variable name you defined, and then you can reference that value by variable name within the context of the LookUpRecord action.

Hope that explains the issue.

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
Senior Content Developer - Modern Assistance and Support Experience - Microsoft Corporation

Author - Microsoft Access 2013 Inside Out
Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010/2013 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/en-us/legal/Copyright/Default.aspx
----------

 

[jollsamox]

Thanks for this - unfortunately though this hasn't resolved what i'm trying to do, i think i've perhaps explained it badly. So i have:

Stakeholders database, with email address as a field.
Projects table with a list of stakeholders who need updating, one of them a project manager.
Tasks table, where the task is assigned to one of the stakeholders.

So what i'm trying to do is set a data macro to run when the status field in the tasks table is changed.

The data macro is to lookup in the relevant projects table who the project manager is when the tasks table status field is changed. Then send an email to that stakeholder.

So the available linked data should be:
Task.Name is linked to a Project
Task.Owner to Stakeholder.ID
Projects.Project Manager to Stakeholder ID

Does that help?

 

[cavalli]

Hello jollsamox, did you found a solution to this issue? I'm have exactly the same problem...