Creating a grid form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have to create a form that allows users to enter resource allocation. The
rows will be resources and the columns will be months. Allocations will be
percentages. The users will start by selecting a date range using a from/to
function. The default will be the next 12 months. I want to display each
resource and their current allocation for each month within the selected
period. The number of resources could be up to 30 and the number of months
would default to the next 12 months but may be up to 3 years.

To do this, I need to dynamically create the column headings for the months
within the range, then populate them with allocations for each resource -
sort of like a spreadsheet. I have a table that contains PersonNo, YearNo,
MonthNo and Allocation (e.g. Person 1 in Month 3, Year 2007, Allocation 50%)

I can think of a number of approaches including multiple subforms for each
month and setting the criteria using VBA but thought before I start, I would
ask if anyone has a suggestion as to the best way to structure the form.
 
Hi Peter
Great site and lots of useful stuff I had not seen before. It is now on my
bookmarks and I will be sure to use it.

A flex grid is possible however I do need editing facility. I will look at
your comments on editing and see if it is a possibility. As you can imagine,
setting up resources on a project can be a fairly intensive data entry
exercise - particilarly as this is replacing Excel spreadsheets where people
have gotton used to copying by just dragging an allocation in one cell across
many cells.

Would be interested to see other possible solutions.
 
Hi Neville

If you want that sort of interaction then, as you say, a FlexGrid
control may not be best.

Have you considered using the Microsoft Office Spreadsheet ActiveX
control which comes with Office. This is a cut down version of Excel
which can be embedded in Access forms. I have not used it in a
database myself so I don't know its capabilities but it may do what
you want.

Peter Hibbs.
 
Back
Top