Help getting a calculated field query info into my form (1 Viewer)

Hello all!

I'm new and need help lol! I haven't used Access in about 12 years, but have a new role where the person before me made a db. I've re-vamped it and made it more user friendly, but I'm stuck on this one thing and it's making me mental. I imagine it's pretty simple for those with years of experience so I'm hoping if I beg, someone can help

I have a form with the following information on it:

Project Manager Hours
Managing Consultant Hours
Solutions Architect Hours
Lead Consultant Hours
Senior Consultant Hours
Staff Consultant Hours
Total Hours

What I want to do is to have Access automatically enter the number of hours into the "Total Hours" field/control rather than having to manually add it up with a calculator. Someone told me to use a calculated field query so I managed, finally, to build that (and when I did, realized I had to take the "Total Hours" field out of my table b/c it can't reference anything there), but now I have no idea how to get my "Total Hours" field to reference the results of the query.

My friend thinks I may need to run an Event Procedure so that Access knows to run the query every time an entry is made into one of the above roles and then have it run again to put the total in. It's also possible inhe related record, that some roles will have a default of zero (since a contract may not use all roles) so I have to consider that too. Also, I will need the Total Hours number in some reports that are in my db...and I have Access 2007.

I suspect I need code...but that's where I'm at the end of my knowledge!

Anyone? Pretty, pretty, pretty please?

Thank you!!!
Lynne

In your total hours field on the form make sure it is unbound and enter the following in the control source property

=[Total1]+[Total2]+etc

Where total1 and total2 are the names of the fields in the table.

DCrake said:

In your total hours field on the form make sure it is unbound and enter the following in the control source property

=[Total1]+[Total2]+etc

Where total1 and total2 are the names of the fields in the table.

Click to expand...

And use NZ so that if there is a null it won't honk everything up:

=Nz([Total1],0)+Nz([Total2],0)+etc

OMG I love you!! How is it that easy?? LOL!

I went back and updated the fields so that they default to zero and they are required...would this be okay or should I still us the Nz?

Don't go far...I made need more help lol!

Thanks SO much!!!
Lynne

tempestes said:

OMG I love you!!

Click to expand...

Marriage next perhaps?

But who would the bridegroom be? SOS or DCrake?

vbaInet said:

Marriage next perhaps?

But who would the bridegroom be? SOS or DCrake?

Click to expand...

LOL uh oh, what did I get myself into
That problem has been making me mental for a week that's why I was so happy. I knew it would be easier for someone with experience, but not that easy...I'm slightly embarassed haha!

No wedding for anyone until this db is up and running

tempestes said:

No wedding for anyone until this db is up and running

Click to expand...

Come on guys, add Lynne aka tempestes to your Favourites

Sorry for robbing your thread. I just had to throw in that line

Setting the Default to Zero would work, without using the Nz() function, unless some the idiotic user comes along and deletes the Zero from the field. Sadly, idiotic users are everywhere! I'd go ahead and use the Nz().

missinglinq said:

Setting the Default to Zero would work, without using the Nz() function, unless some the idiotic user comes along and deletes the Zero from the field. Sadly, idiotic users are everywhere! I'd go ahead and use the Nz().

Click to expand...

Allrighty, I've put in the Nz function just to be on the safe side as yes...way too many idiotic users out there!

Speaking of, is there a way to write down a "how to use this db" document of sort? I am not able to sit down and train the person who will be using it for our data entry as she's in another location so I thought it might be nice to summarize what each form and report does and include it right in the db...maybe a form?

Thanks again to everyone...days of aggravation are almost to an end and I don't think I'll need to take home my Access for Dummies tonight lol!

Lynne