1

I like to use an unbound subform to display the result of an crosstab query. However following code 

Me.subFormForecastSummary.Form.RecordSource = "SELECT ....."

results in this error message: "The expression you entered refers to an object that is closed or doesn't exist".

When I use the Source Object in the form designer and specify the select statement, then the code above works fine and I am able to set the property RecordSource.

The issue I am facing is that the select statement is based on a crosstab query and can contain a different number of columns (in my case weeks) depending on the master record. The issue I am facing is that the datasheet only displays the columns as per the original query specified in SourceObject (the data itself is refreshed though). Does anyone know how I can force the columns to be refreshed as well or how I can get overcome the error message?

Thanks

borntosucceed
  • 23
  • 4
  • Have you tried with a simple SELECT statement? `SELECT a,b,c FROM someTable`. – Andre Jul 19 '18 at 10:28
  • Why not just display the saved crosstab query in your form as a sub form? – Minty Jul 19 '18 at 10:53
  • Yes I tried simple SELECT statement with the same result. – borntosucceed Jul 19 '18 at 10:58
  • Also tried to bind the crosstab query directly via the form designer. The issue there is that if the columns change of the resultset this is not reflected in the datasheet. It only shows the same number of columns that the for was saved with originally. – borntosucceed Jul 19 '18 at 11:00
  • I didn't say use a form. I said simply view the *query* in the form. Alternatively look at fixed column headings - this may help https://www.access-programmers.co.uk/forums/showthread.php?t=298615 – Minty Jul 19 '18 at 11:13
  • Set SourceObject property of Subform Container Control to the CROSSTAB query. Another tutorial on 'rolling date' also uses months but can be adapted for other time units http://www.fmsinc.com/MicrosoftAccess/query/crosstab-report/index.html – June7 Jul 19 '18 at 18:07

1 Answers1

0

Thanks for the responses thus far. Based on the comments and further research I have decided on following solution:

  • Created a dummy query returning 1 record with the maximum number of columns. I have named them 001 trough to 100
  • Still created a subform and bound it to the dummy query
  • In VBA I then change the recordsource at runtime as required
  • Also in VBA rename the captions as required

You would think there should be a more elegant solution, but this works for me

borntosucceed
  • 23
  • 4