Access 2013 Web App - Requery Problem (1 Viewer)

manz

New member
Local time
Today, 06:57
Joined
Feb 18, 2015
Messages
5
Hi

I am try to requery a datasheet based on data contained in a related table. So I have two tables, Tasks and Employee. The Tasks table contains a field called 'requested by' to represent the person who requested the task. I only want to display the tasks relating to the logged in user. The 'requested by' field uses a lookup to the Employee table. I understand that the 'requested by' field in the Tasks table will not store the employee name but rather the ID for that employee in the Employee table.

To start with I created a data macro to which I pass the userdisplayname (current logged in user) which then does a lookup against the Employee table. if a match is found it returns the ID for the related employee. I then created a UI macro which calls the data macro and passes the ID to use in a requery. I then added this to the load event of the view. However every time I run it, I get a error: Invalid field name 'Task.Requested_By' and when I click on macro details I get the following:-

Macro Name:
Outstanding FE : filterByUser
Condition:

Action Name:
RequeryRecords
Arguments:
[Task].[Requested_By]=[returnedID]
Error Number:
2950

Please can you help me as this is stopping from completing my project. I need to be able to filter the tasks based on the logged in user so that user cannot amend other users tasks.

Many thanks!
 

manz

New member
Local time
Today, 06:57
Joined
Feb 18, 2015
Messages
5
By the way the app is being hosted on SharePoint Online.
 

AccessJunkie

Senior Managing Editor
Local time
Yesterday, 23:57
Joined
May 11, 2006
Messages
278
Hi,

This is just a educated guess at the moment, but you mentioned your table names are Tasks (with an s at the end) and Employee.

Your UI macro shows that you are using Task (no s) in the Where clause. Is that the issue here? If your table name is indeed Tasks, you need to use Tasks in the Where clause - assuming of course the view is based directly on the table and not a query.

--------------------
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
----------
 

manz

New member
Local time
Today, 06:57
Joined
Feb 18, 2015
Messages
5
Hi Jeff,

Sorry, the table is called Task and I am using Task in the where clause. I am at a loss. The view is based on a table rather than query. If I change the macro say to a unrelated field (not related to another table) such as status e.g. Where Task.Status="Completed" it seems to work fine. It just does not want to work on the lookup field. I even checked the ID's in the Employee table, knowing data exists in the Task table with ID 2, I tried a where Task.RequestedBy="2" and I got the same error.

I appear to be really stuck now.

Thank you

Mandeep
 

manz

New member
Local time
Today, 06:57
Joined
Feb 18, 2015
Messages
5
Jeff,

I checked again and the view was based on a query rather than the table. I changed it to the table and it works fine. Thank you so much for your help. I have spent literally days playing around with it to work out how to get it to work and it was a simple as that. I assume if I include both the Task fields and Employee fields in the same query, I can the view to the query and the macro should work?

Thanks again!

Mandeep
 

AccessJunkie

Senior Managing Editor
Local time
Yesterday, 23:57
Joined
May 11, 2006
Messages
278
Hi Mandeep,

I'm pleased to hear you worked through the issue.

If the view you are trying to open using OpenPopup macro action has a query as it's record source, then you must use the query name like this:

[FieldNameHere]=[NameOfQueryHere].[FieldNameInThatQuery]

If you are using a table name, then substitute the actual table name in the appropriate spot.

One thing to note which sometimes trips people up is that this technique won't work if the view you are trying to open uses an Embedded Query as its record source. The reason for this is you have no way of knowing what the embedded query name is that Access creates behind the scenes for the web app view.

Good luck with your project.

--------------------
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
----------
 

manz

New member
Local time
Today, 06:57
Joined
Feb 18, 2015
Messages
5
Thanks Jeff!

Your assistance is much appreciated!
 

AccessJunkie

Senior Managing Editor
Local time
Yesterday, 23:57
Joined
May 11, 2006
Messages
278
You're very welcome, glad I could help with your question.

Good luck with your Access web app project.

--------------------
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
----------
 

Users who are viewing this thread

Top Bottom