Suggestions Please - Form Coding

[tmdrake]

I have a form with 4 comboboxes (ProjectID, DisciplineName, SectionNumber and
LastName) where the user can select from any or multiple boxes, hit select
and the results display in a subform (this works fine). Once the results are
displayed the user can double click on any one of the fields (named same as
comboboxes) and another form appears giving more detailed information for the
field. In that detailed information there is a field for Week Ending,
Projected Man Hours and Actual Man Hours.

How do I code the select button that when the user clicks after making
selections from the one or multiple comboboxes the Total Projected Man Hours
and the Acutal Man Hours are summed and displayed in 2 different txt boxes on
the Main Form.

Secondly, what code would I use that when a records is double clicked on in
the subform, the user is asked to enter a Start Date and End Date and this
information is automatically exported to a Excel Spreadsheet.

I really hope this makes sense. Any and all help is truly appreciated

Thanks

 

[John Smith]

Put controls in the footer of your sub-form with control sources of:
=Sum(ProjectedManHours) and Sum(ActualManHours)

If you want to show them on the main form instead, hide them and put controls
on the main form which refer to them:

=SubFormname.Form.ProjectedSum etc.

For your export, write a query with Start and End date as parameters and use

DoCmd.OutputTo acOutputQuery, "Query Name", acFormatXLS

to export it.

HTH
John
##################################
Don't Print - Save trees

 

[tmdrake]

Thanks for your help, it is greatly appreciated.
If you want to show them on the main form instead, hide them and put controls

on the main form which refer to them:

=SubFormname.Form.ProjectedSum etc.

This is what I loaded for the control source of the control (text box) on
the main form:
=frmHours_Worked subform.Form.Projected_Man_HoursSum

I received an error message: "The expression you entered contains an invalid
syntax".

Please tell what I'm doing wrong.

Thanks again

 

[John Smith]

You have a space in 'frmHours_Worked subform'. If that is the correct name of
the subform control then I suggest renaming it without the space. I find that
spaces in object names just make life difficult so would always avoid them!

John
##################################
Don't Print - Save trees

 

[tmdrake]

Changed the string to read:

=frmHours_Worked_subform.Form.Projected_Man_HoursSum

Now the following appears in the txt box: #Name?

What does this mean and how do I fix it.

Thanks again

 

[John Smith]

Are you sure that you have all of the names correct?

Especially, is frmHours_Worked_subform the name of the subform control on the
same form as you have your control on? Note that the control may or may not
have the same name as the form that you have in it but it is the control name
rather than the form name that you need to reference it by.

One more thought, usually either syntax will work but you might try:

=frmHours_Worked_subform.Form!Projected_Man_HoursSum

It depends on how the control was created.

HTH
John
##################################
Don't Print - Save trees

 

[tmdrake]

Does this string apply if the calculations are on a form inside a subform,
inside a form?

Thanks

 

[John Smith]

It should do, you can reference any part of your Form/sub-form object, you
just have to work out the path from where you are to the object that you need
to refer to. Just remember that a sub-form has to be reached via the .Form
property of the control that it is held in which may or may not have the same
name as the form.

John
##################################
Don't Print - Save trees