Need help with form

[Guest]

Hello, I posted a question last week and I guess it was too vague, I have not
gotten any response. I will attempt to be more clear.

I am working with Access 2000, I have ADO and DAO both referenced in the
library.

My form is based on a table. This is an input form for a 5 yr budget. The
first year is broke down in 12 months and a total, the 2nd year is broke down
into 4 quarters and a total, the last 3 years are totals only.

What I would like to happen is when the user enters data into Jan. the Total
Yr1 field updates by adding the values in Jan-Dec. When the user enters data
in Feb. the Total Yr1 updates again.

Since the field I have named Total Yr1 is bound to the main table, I have
placed a hidden unbound text box on the form in order to perform the
calculations. It is called Calculate1. For its control source I have summed
the Jan-Dec fields. My next attempt was to use the Jan field's AfterUpdate
event to say the Total Yr1 = Calculate1.value, and I planned to use the same
event for all the months. The problem is that it calculates ALL the records,
giving me a running sum. I only want to see a total for the current record
that is being used. Upon leaving the form I want to make sure it calculates
again.

My first instinct was to write a function that would perform the addition of
the 12 months, and to call on that function for the updates. I have not had
any success on getting that to work. It has been several years since I have
worked on a database and I am somewhat rusty. I'm also self-taught and my
knowledge is very limited.

I sure would appreciate if someone could get me pointed in the right
direction. This form will work without the totals, but I think it would be
much more beneficial to the user if they could see the totals. The users are
more accustomed to using Excel and if the form could function more like an
excel spreadsheet, the happier I would be.

Donna

 

[Nikos Yannacopoulos]

Donna,

I'm afraid your new post isn't really more enlightening than the
previous one... at least not to me :-(

Reading the first part I couldn't help thinking this sounded more like
an Excel thing, so I wasn't really surprised at your closing comment. I
suppose there must be a compelling reason for doing this in Access
rather than Excel. What are you trying to achive with this database?

Now, on the question at hand, it would help greatly if you told us s a
bit about your design: is it just one table, with 12 month + 1 total
field for yr1, then 4 q. +1 total field for yr2 and another 3 total
fields for years 3 to 5? Or what?

Nikos

 

[Guest]

Hello Nikos,
Thank you for replying. This db will be used as a input for budget data,
some queries will be run for escalation, and lots of reports will be
generated.

We will be entering the data by cost center, then by expense element. There
is one main table that includes the UserID, Jan-Dec (12 fields), 1st year
total for that line item, 4 quarters for second year, 2nd year total, for
that line item, 3rd, 4th & 5th year totals, again, for that line item. There
is a unique number for each record, the primary key. The user ID is indexed.
I created a reference table for the cost centers, expense elements so the
user will only have to key in the number or pick from a dropdown list, and
the actual account names will be on the reports/form.

There will be a dozen or so people inputting their own data for their dept.
The man who is designing this db and has asked for my help, wants each person
to have their own table/form. When everything has been input, we will combine
all of the user's tables into one common table, run queries and reports. He
wants to keep everything in Access.

I know how to design the reports to show the sums of the cost centers etc.
I'm just hung up on this form.

I have made a totals query from the main input table and grouped by the cost
center, then summed the first and second year. I made a sub form from this
totals query and put it on the main form, linking the user ID. Its still in
the design stage, not sure if I will use it or not. I have the view set as a
datasheet view.

The input form itself is in Form view. Each record will contain the userID,
the cost center, expense element, a description of the expense for the user's
benefit, then all the months, quarters, years, mentioned above.

Some of the user's will know how much they want to expense out to a cost
center for the first year and will only need to split the amount into 12
equal amounts. Other user's will have more exact information to put in each
time frame. There is an unbound text box the user can put an amount in that
will be divided into 12 equal amounts and will be input to the Jan-Dec
fields, the total goes into the 1styear total. OR the user can input the data
in each field, or a combination of the two.

I want to keep it simple and flexible for the user. They are used to working
with a program that inputs similar to Excel. To make it even more fun, we are
in the middle of changing to a new system and all our current cost centers
and expense element codes will be changed. We have incoporated this info into
the reference table. At this time we do not have a complete list of the new
accounting codes/names. I don't forsee this as a problem for the end
results. The user will be able to see the old and the new codes on the input
form when they make their selection from the dropdown list and the
appropriate names will be in the reports as well. We will be inputting data
under the old codes and by the time the budget is processed in August, we
should be able to report using the new codes. time will tell.

I've never had a need to perform a calculation at the form level, its
something I would do in a query or at the report level. I don't need to
"Find" a record, or "GoTo" a record. I've gotten all my Access 2000
develper/programming books out and read all I can find on working with
recordsets. I'm still at a loss. If it can be done in Excel, why can't it
be done in Access is my question.

I suggested we have an Excel spreadsheet as an input form and bring that
over to Access, but he doesn't want to do that. If it were up to me... that's
what I'd do. But its not up to me, and this guy is confident that I can
make it work in Access. If we can't get the totals to show up on the record
as they are inputting the data, then so be it.

I truly appreciate you taking the time to read this lengthy message. Donna

 

[Nikos Yannacopoulos]

Wow! I almost feel guilty for amking you write all this!

We will be entering the data by cost center, then by expense element. There
is one main table that includes the UserID, Jan-Dec (12 fields), 1st year
total for that line item, 4 quarters for second year, 2nd year total, for
that line item, 3rd, 4th & 5th year totals, again, for that line item. There
is a unique number for each record, the primary key. The user ID is indexed.
I created a reference table for the cost centers, expense elements so the
user will only have to key in the number or pick from a dropdown list, and
the actual account names will be on the reports/form.

You should drop those annual total fields for the first two years;
calculating them on the fly is a matter of a simple summation, and the
golden rule in Access is if you can calculate it don't store it.

There will be a dozen or so people inputting their own data for their dept.
The man who is designing this db and has asked for my help, wants each person
to have their own table/form. When everything has been input, we will combine
all of the user's tables into one common table, run queries and reports. He
wants to keep everything in Access.

Keeping everything in Access is fine, but I don't agree on the separate
table / form per user. All be data should be in one place, and a comon
form should be used.

If it can be done in Excel, why can't it be done in Access is my question.

Access and Excel are different apps, intended for different purposes.
Don't let the datasheet appearence fool you, it is not a spreadsheet!
Some things can be done in both, but not in the same way.

I suggested we have an Excel spreadsheet as an input form and bring that
over to Access, but he doesn't want to do that. If it were up to me... that's
what I'd do.

Hmm... not sure I would want that either.


Now let me get to the general part. I am not sure I would keep that
design as far as the table in question goes, I think i would use that as
the One side of a One-To_Many relationship to a table that stores one
record per user/cost center/expense element (or whatever) one more field
for the year and 12 fileds for the months, so for each record in the
current table there would be five records in the new table, and more
added year on year (continuity is an added benefit).
Now, splitting an annual or quarterly total to the respective months
through some code behind the form is easy, and shouldn't concern you.

The first thing to concentrate on and dedicate as much time as required
to, is to get your data design right and, believe me, it wiill pay
further down the development. On the contary, if you cut corners in your
data structure, you'll regret it sooner than you think.

Pls think this over, and come back with questions if you need.

Regards,
Nikos

 

[Guest]

Thanks Nikos,
I couldn't agree with you more on getting the design in order first. This db
is relatively simple and will mostly be used to print out reports. I
apologize for the long message before.

Can you recommend any resources to learn more about working with recordsets?

Thanks, Donna

 

[Nikos Yannacopoulos]

Can you recommend any resources to learn more about working with recordsets?

Not really... I learned what I know relying mostly on Access VBA help.

Once you finalize your data design, you can post back with specific
questions and someone (or me, if it so happens) will be able to help you
with them.

For what it's worth at this stage, assuming the data structure I
described yesterday, I envisage a form or subform (single form view /
one year at a time) with:
* 12 controls for monthly data
* 4 controls for quarterly data
* 1 control for annual data
* a command button to ditribute an annual total evenly accross 12 months
and 4 quarters
* a command button to distribute quarterly totals accross respective
months and update annual total
* code to update annual and quarterly totals automatically whenever a
monthly value is entered / changed

or something along these lines.

Nikos

 

[Guest]

Hi Nikos,
Due to the nature of the budget reports, I will keep the current table
structure in the 5 year time frame. The form contains:

*Controls for the userID, cost center, expense element, user desc.
* 12 controls for monthly data - year 1
* 4 controls for quarterly data - year 2
* 1 control for annual data - year 3
* 1 control for annual data - year 4
* 1 control for annual data - year 5

I am using a text box for the user to input the amount they wanted to have
split into 12 equal monthly amounts for year 1; another text box for the 4
qtrs for year 2.

Currently I have placed a subform showing the total for year1 and year 2 for
that record (based on underlying Totals query) but I would rather use an
unbound text box that will update the total - one for year1 and one for year
2 - on the form; as the user moves through the fields.

This is the only form I need. The rest of the work will be in queries and
reports. Thanks for your advice and suggestions. You've been very helpful.

Donna

 

[Nikos Yannacopoulos]

Due to the nature of the budget reports, I will keep the current table

structure in the 5 year time frame. The form contains:

I'm not convinced, but it's your project, not mine!

Currently I have placed a subform showing the total for year1 and year 2 for
that record (based on underlying Totals query) but I would rather use an
unbound text box that will update the total - one for year1 and one for year
2 - on the form; as the user moves through the fields.

Agreed.

Assuming the controls for each month in year 1 are named Month1 through
Month12, and the one for the total for year 1 is named Total1, I would
plae the following sub in the form's module, and call it from each of
the 12 monthly controls' Before Update Event:

Sub Update_Total_Yr1()
Dim vTotal As Double
For i = 1 to 12
vTotal = vTotal + Me.Controls("Month" & i)
Next
Me.Total1 = vTotal
End Sub

so the total gets updated whenever any month is changed manually.

Also, I would use a command button to distribute evenly across months
the current amount in Total1 (wether entered manually or calculated as a
sum of previous manual entries in the months). The code behind it would
look something like:

Private Sub CommandSomething_Click()
For i = 1 to 12
Me.Controls("Month" & i) = Me.Total1 / 12
Next
End Sub

Likewise for Q1 - Q4 and Total2

HTH,
Nikos