Form design help

[Acropolis]

Hi Guys,

I am hoping someone can give me some inspiration here.

i am looking to create a form for project managers to enter a project budget into.

This will be limited information, with financial inputs on the following:

Income
Materials
Contractors
Equipment Hire

These categories could change though, so needs to be dynamic to handle the additional fields as and when added etc.

This will be broken down over the length of the project in months, which will be obtained when the project is created, some could be 2 months, others could be 5 years and anywhere is between.

Ideally i was thinking something like a CrossTab query with the financial section down the left hand side, and the months of the project across the top, but this can be used for data entry i believe? Certainly previous attempts at this have failed.

Does anyone have any idea's on how best i could go about achieving this? Without having to have a data sheet for entry controlled by something like a listbox or combo box to select the month for, as this is not great for data entry and have found that people tend to mess it up.

Any thoughts gratefully accepted and appreciated.

 

[Ranman256]

You wouldn't enter data that way.
Once you have the project Id from the tProject table, then data would be entered vertically in tProjExpense....

ProjID,Date,AmtSpent,Item

The data is entered here, THEN a Crosstab can be run to build your financial view.

 

[Acropolis]

Have the actual costs incurred all sorted already as that's an existing part of the system, but i am looking to enter a budget for the project when it's created, but this will be spread over the months of the project and looking for an easy way to put the data in without having loads of different text boxes.

Ideally something like a spreadsheet with the month in the columns.

 

[Acropolis]

Another way i could do it, is when the project is created all the entries for the months are created with a 0 value so the entry exists, just a way of editing them then?

 

[CJ_London]

You can make a form enabled for data entry based on a crosstab query, but it is a lot of work and I suspect unworkable if you are going to have 60 columns + for a 5 year project - max width of a form is 56cm. So each 'month' control would be around 5mm in width given you will have a number which need to be significantly wider.

Without having to have a data sheet for entry controlled by something like a listbox or combo box to select the month for, as this is not great for data entry and have found that people tend to mess it up.

you have the datepicker. Or you could try creating a type of monthpicker form

1. create a table called tblYears, populate it with one column - YearNum - and enter 2016, 2017 etc for the range you require

2. create another called tblMonths, with one column - MonthName - and populate with Jan, Feb etc

3. create a crosstab

TRANSFORM First(" ") AS Expr1
SELECT tblYears.YearNo
FROM tblYears, tblMonths
GROUP BY tblYears.YearNo
ORDER BY tblYears.YearNo
PIVOT tblMonths.MonthName In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

If you prefer, swap the rowheader and columnheaders round

4. create a continuous form based on this query

5. in a module create a function

Function selDate()
   MsgBox "Selected Month is " & Screen.ActiveControl.Name & " " & Screen.ActiveControl.Parent.YearNo
End Function

6. in your new form, select all the month textboxes and in the click event put

=seldate()

7. You can add your new form to your mainform as a subform and keep it hidden until the appropriate control is clicked

you'll need to modify your function to do something more than display a message box - perhaps assign to a global variable that your main form can reference. All depends on how you want things to work

 

[Acropolis]

Thanks for the help guys. I have decided to go a different route.

At a certain point before the form opens, all the records will be created with a 0 value, and displayed in a cross tab.

There will be a drop down for the element and month, and a text box to enter the amount. The user can change the value for the appropriate month rather than having to set all of them. As most of the months will be 0 anyway.

Will add some checking when they close the form to ensure that the values for the elements are totalling 0 - therefore nothing has been entered, and stop them from leaving if this is the case.

Thanks again for the help.