You cannot use a passthrough query or a non fixed crosstab error message a a record source for a subform RRS feed

  • General discussion

  • Hi Access MVP's

    I thought I would raise an interesting error message I got, but was not very descriptive, 

    and only came about when I re-added the subform/ to the form.

    I have now resolved but thought of raising it if it is an issue, and maybe other developers may have come across it.

    1) Initially, I had an Access FE / Access BE

    The use of Crosstab as record source was fine in a subform

    2) moving to Access FE / Local SQL Server

    The same configuration above in point 1, The use of Crosstab as record source was fine in a subform

    3) When moving to Access FE / Azure

    Performance initiatives were employed.

    One of them was the introduction of pass through queries.

    At first the error message did not appear when executing the form/subform

    Only when I re-embedded the subform out of exhaustion to what was the issue.

    In the crosstab query, I had a left Join, in order to reveal the employees.  I was at first filtering this out by placing 

    Forms![Dashboard Details - Employee]!txtUser in the conditions for Employees.  

    This was creating an issue once I started using a pass through query, which was filtering out the particular user.  Hence there was no need to filter any longer.

    So I took it out and it started working.

    I then had to stop the Left join from producing all the employees, so I changed the relationship as per the below condition = 1, instead of 2 or 3.

    But what was interesting was that the message was not representative of the change made, or the issue that was present.

    For your knowledge.

    Thank you in Advance

    Tuesday, August 7, 2018 6:02 AM