473,574 Members | 5,884 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Form to perform calculation

77 New Member
Hello,

I am working on a project for my department to design a form to calculate budgets. I really don't know much about access or programming in general.

At this point, I am trying to design a form where I can add fields to a table. (At least I think this is what I want to do). The form currently has a text box for each category of employee, which is bound to a table. Every employee category will have different rules that determine the final budget. What I need is a way to add in additional employees within a category: for example one budget might have three category one employees, two category five employees, and ten category eight employees. Each of these employees will have will then have attendant information associated with it such as the name, salary, percent of effort etc...

My form currently has a listing for each employee category. What I would like to design is either a form or subform where I can input the number of employees within each cateogry, and a new field or new text box will automatically be created.

Thanks for your help.

James
Feb 6 '07
133 11657
Rabbit
12,516 Recognized Expert Moderator MVP
I'm off work for the 3-day weekend. I'll get back to this when I'm in the office on Tuesday. Perhaps someone else will be able to guide you along until I get back.
Feb 9 '07 #21
NeoPa
32,564 Recognized Expert Moderator MVP
One other thing. I created a new text box for project end date, and I used the following statement tied to "Project Start":
Expand|Select|Wrap|Line Numbers
  1. =DateSerial(Year([Project Start])+([# of years]),Month([Project Start]),Day([Project Start])-1)
  2.  
However, now when I switch to a new record, the Project Start field and Project End one are blank. Is there anything I can do to prevent it from changing?

Thanks.
I won't take on this whole project, but while Rabbit's away I think I may be able to help here (assuming you can express more clearly what you want). What does the last question (in bold) mean? To prevent what from changing from what?
Feb 10 '07 #22
muhes
77 New Member
Thanks NeoPa for your response.

I have been working with Rabbit to design a form. Most recently, he informed me that I could place a text box (or anything else) in the header of a form with a continuous view so that I don't have repetition of unnecessary fields (these fields have the same data for each record in the form for any one budget). I placed three text boxes in the header, one for project start date, one for # of years ( both of which are fields in TblEntry), and a third field with the code provided to calculate the project end date.

Each record represents a person in the budget, and each has a distinct employee category which influences how their salary is calculated. The problem is that after I have entered all of the data for the first person, when I tab down to the next record, the date fields are blank. My question is is there a way to freeze or copy the data in a given field so that it doesn't change.


Thanks,

James
Feb 10 '07 #23
NeoPa
32,564 Recognized Expert Moderator MVP
To prevent what from changing from what?
So I guess the answer is to prevent the calculated control [Project End] from changing from the previously calculated value.
Please confirm this is correct.

As the calculation is performed on the current record of the form, a new record doesn't have the data to work with so has to resolve to blank. One way to get around this (assuming I understand your previous reply correctly) is to take away the Control Source of the [Project End] control and set it, when required, in the OnCurrent event procedure.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     If (Me![Project Start] & Me![# of Years]) > "" Then _
  3.         Me![Project End] = DateAdd("yyyy", _
  4.                                    Me![# of Years], _
  5.                                    Me![Project Start])
  6. End Sub
Feb 10 '07 #24
muhes
77 New Member
Thank you NeoPa for your response.

Yes, you are correct that I don't want the project end date to change;however, I also don't want the project start date and the # of years to change (these three fields are all part of the form header). I don't know whether what you suggested below will take that into account. The information in these fields would be valid for the entire project (for the entire budget). Basically, I don't want any of the fields used in the header of the form to change once data has been entered.

Thanks for your help.


So I guess the answer is to prevent the calculated control [Project End] from changing from the previously calculated value.
Please confirm this is correct.

As the calculation is performed on the current record of the form, a new record doesn't have the data to work with so has to resolve to blank. One way to get around this (assuming I understand your previous reply correctly) is to take away the Control Source of the [Project End] control and set it, when required, in the OnCurrent event procedure.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     If (Me![Project Start] & Me![# of Years]) > "" Then _
  3.         Me![Project End] = DateAdd("yyyy", _
  4.                                    Me![# of Years], _
  5.                                    Me![Project Start])
  6. End Sub
Feb 11 '07 #25
NeoPa
32,564 Recognized Expert Moderator MVP
Not exactly.
The controls you mention are bound controls so when you select the New record, there is nothing there to display. It sounds as if you may need a form with a sub-form rather than a single form using the Header and/or trailer.
Feb 11 '07 #26
muhes
77 New Member
Thank you again for your response.

I have redesigned both my form and tables slightly. I took the fields from TblEntry for "Project Start Date" and "# of years", and I deleted them. I then created a new table called TblDate with those fields and also "Budget ID", and "Date ID" which is the key for this table.

I then created a new form, and added all the information that was previously in the header of FrmEntry. I also added a command button that opens the FrmEntry. However, I still want to display the date information and # of years in the header of the FrmEntry. What do I need to do? Does what I have done solve my problem?

Thanks.
Feb 12 '07 #27
NeoPa
32,564 Recognized Expert Moderator MVP
As I'm just babysitting this thread until Rabbit gets back I've focused on one narrow area only. I am not in a position to make judgements that rely on a full understanding of your problem. It sounds as if you may well be on the right track but I'll leave any progression from here to Rabbit.
Feb 12 '07 #28
muhes
77 New Member
As I'm just babysitting this thread until Rabbit gets back I've focused on one narrow area only. I am not in a position to make judgements that rely on a full understanding of your problem. It sounds as if you may well be on the right track but I'll leave any progression from here to Rabbit.

Ok, thank you for your help.
Feb 12 '07 #29
Rabbit
12,516 Recognized Expert Moderator MVP
Just bumping to the end of my list. I go from oldest to most recent but want to tackle this one last.
Feb 13 '07 #30

Sign in to post your reply or Sign up for a free account.

Similar topics

2
3897
by: Del | last post by:
Thanks in advance for any help. I have a database that was created in Access 2000. Several users have been upgraded to Access 2003. Since upgrading to 2003 we have noticed that some of the calculated fields are not being populated. The database is a samll invoicing database on the form and report we have columns call Unit Cost. This is the...
2
1865
by: jquest | last post by:
Hi Again; I am improving my database and have to overcome some original design mistakes. I have 3500 records in the table and I have had to insert new fields to track things I originally didn't forsee. The new fields in the table now show nulls where there is no data. When I run a query based on the table and including the field that...
2
2352
by: DebbieG | last post by:
I have no idea how to start with this one. I have a subform where records could look similar to: Infraction Date Points 01/01/2000 3 06/01/2002 1 Somehow, I've got to calculate the points the driver has as of the current date. For instance, in the above example:
0
1719
by: gavo | last post by:
Hi. using A2K; i have a form containing a continous subform. The question is, how can i call a calculation for one of the fields in the continous subform from the main form. At the moment i have a button on the main form that will call a public code containing the calculation i need, the problem i have is that it will only perform the...
2
1836
by: RICHARD BROMBERG | last post by:
I have a form with thirty text boxes. Each of them has an input mask of 99. In each text box the user enters a numeric score in the range from 0 to 99. As each text box loses focus I want to perform a calculation involving all the numbers (ok the string representation of the numbers) . It doesn't matter which text box loses focus, the...
4
3751
by: vg-mail | last post by:
Hello all, I have identical design for form and report but I am getting calculation error on form and everything is OK on report. The form and report are build up on SQL statement. The calculation is very simple. The calculation is done in an underling query if I can call it a query or I should call it a SQL statement. It looks like a query...
1
2313
by: David | last post by:
Hi, I have a continuous form with 'x' amount of records. 1 field on each record is a number, of which I have a field at the top of the form which just shows the running sum. If I enter a new record, after I loose focus of the number field, I perform a save record and then test the data with a calculation, but the problem I have is that...
2
2382
by: campos | last post by:
Hi all, I ran into a headache problem. I have a windows form with a progress bar on it. Then I new a thread to do calculation for a long time. I want the progress bar to show the calculation progress. So I use a shared variable in calculation thread to allow main thread read it periodically in order to show the progress. Quesion comes out....
24
8407
by: MichaelK | last post by:
Who knows how to prevent submitting a form on the press Enter button before all fields on the form are filled up. People just enter the first field hit Enter and it submits the form and doing validation, of course flushing with the bunch of messages because rest of the fields are empty. I remember I had some pages where wrote the code to go...
11
6471
by: rajeevs | last post by:
Hi All I have two issues to put forward. First is bookmarking / or highlighting a particular record in a form. The form is continuous and the records are from a query result. One of the record in that form will be always the last added record from the table. The form display the records in a sort order from the query. What i need is to...
0
7741
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8259
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7838
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6491
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5637
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5328
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3781
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2257
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1090
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.