Form with dynamic number of columns

[TimTDP]

I need to create a production forecast form based on previous sales history.
The history is based a sales and grouped by month & year

So on the form, which needs to be a continuous form, I want products to show as rows and months as columns
The sales history per month needs to be displayed as well as a field allowing to user to enter the production forecast.

I can write the sales history to a temp table.

However I never know how many months history the user is going to want displayed at run time. Could be 3, 7, 12 or 15!

Attached spreadsheet shows what I am trying to achieve.

Is this possible and if yes, how would I do it?

Many thanks in advance

 

[plog]

Technically it is possible. But its one of those things that if you have to ask, you're not going to be able to do it. Its going to take some complex VBA to generate a dynamic form and then even more complex VBA to build the SQL to take all those inputs and insert them into your table(s).

My suggestion is either to use a regular form/subform where they choose the product, if its for Actual or Budget and then they input the date. Then they keep dropping down lines and filling in new records. The example you posted would then translate into 56 different lines on a form.

 

[vbaInet]

... and it looks like this is more appropriate in Excel. However, the layout you're trying to achieve is probably possible in a crosstab query.

 

[CJ_London]

You can do it fairly simply by having your form recordsource as a crosstab query - but you'll need to alias your columns with a generic name - e.g. month1, month2 etc rather than a specific name such as Apr 2014, May 2014 etc.

Note that your crosstab will need to specify the column headings e.g.

....
....
PIVOT MthName IN (Month1,Month2,etc)

Otherwise as Plog says, it's going to require complex VBA

 

[vbaInet]

Not quite so straightforward because there are two sub fields/categories called "Actual" and "Budget".

 

[CJ_London]

So there is

OK, I would refer the OP to this thread which has a similar requirement

http://www.access-programmers.co.uk/forums/showthread.php?t=266749

 

[TimTDP]

Thanks guys