Page 2 - Calculated and send to totals. Question

My statement:
"I removed project relationships and tried to delete records and Access would not allow it."
is to point out that even without Relationships set up your records are somewhat secure. The records I attempted to delete were in Employee and Projects tables. Because they were records referred to in the Effort table, could not delete. You are able to delete ANY records from Effort table but only records from Employee and Projects that are NOT referenced by the Effort table. Please just be aware of this and continue using.

If the button works, then good, but I would not have expected that coding to work. I would have used:
(be sure to use the correct name of the combobox, I don't have the project with me here so can't check it). There should be ways to automate this action so user's don't even have to think about it, it would happen without their awareness. Explore possibilities of form events: OnActivate, OnGotFocus, OnCurrent.

I receive the same message again .Appears that the button don't work. You add cbx for EmployeeID ,why. June what you think that i can do to resolve the message when i try to add in the Effort Form.
Searching in the internet the Requiry method or the Recalc method on the afterupdate event apparently the solution, but i try and is not.
I think that the problem is that Relationships are no created "autonumber id" but not sure.

Attention - Anyone viewing this thread
I am working in a Temporaly Job for graduate student first experience, thanks to the Govern of Puerto Rico until july 3. I want to finish this application to future jobs.

So Any help is very appreciated.
Sincerely.

...............

In my last post I typed the Requery with bad syntax. (That's what happens when trying to do 3 things at same time.) I edited it hoping I did before you read. Please check the code. Use the name of the combobox on Effort form for the employee name. Should also include in the event a Requery of the projects combobox. I will look at project again when I get home tonight.

I placed another revision to the Box site:
http://www.box.net/shared/1lzpfi3bqi

I made a few edits:
Renamed the comboboxes on Effort form;
Changed captions on the Employee and Projects forms;
Added the following event and code to Effort form
Private Sub Form_Activate()
Me.cbxCategory.Requery
Me.cbxEmployeeID.Requery
Me.cbxProjectID.Requery
End Sub

Remember - When you enter a new record via the Employee or Projects forms MUST leave the row of that new record or close the form in order to commit to table. If you don't then the event code above will not do any good, nor will any other event. This is why best to have only one form open at a time.

This all worked for me.

Thanks June 7
One last question:
Is possible create a report like this?
Report Tittle =Date() PAGE
.................................................. ..Pending→Current
EmployeeID Start Date End Date AcademicCalendarSummer
ProjectTitle:
TotalEffort:

Duplicate AcademicCalendarSummer changing the label to current.

I possible do that the effort value for that Project Title appears below the correspond category and status ? Example
Employee Effort 06/28/2009 1 of 1
.................................................. ......Pending→(Current)
Nieves,Gilberto Start Date End Date AcademicCalendarSummer

Talent Search 2/7/2004 7/31/2004 ....... 0 .. ..60% .... 0

Upward Bound 1/1/2005 12/31/2006 ......90% .. ..0 .. 0
TOTALEFFORT....................................... .150

Yes.
I have put a new version of project with a new report in:
http://www.box.net/shared/1hn4s949rg

At least I think it offers what you are looking for, although I am not sure what you mean by Pending to Current. Have you changed Status values? Shouldn't matter, the report will display by group whatever values you have in your database.

You have EmployeeID with cbx. I use this code for the Effort Form.
Private Sub updatebutton_Click()

Me.EmployeeID.Requery
Me.Category.Requery
Me.ProjectID.Requery

End Sub

Why you use this code and the message disappears?
Private Sub Form_Activate()
Me.cbxCategory.Requery
Me.cbxEmployeeID.Requery
Me.cbxProjectID.Requery
End Sub

Thanks June7

If I understand you, it is working, you just want to understand why.

It is the comboboxes that need to be requeried so my code references the names of the comboboxes. I always prefix object names with characters that identify the kind of object. This is a conventional programming practice.

The Activate event gets 'fired' when you select the form. So if you keeping several forms open and move back and forth between them for data entry, every time you go back to the Effort form this event 'triggers' and executes the requeries on comboboxes. Remember, leave the line of the new record to commit to table else you will get the error message. Just occurred to me: possible solution to this is to add code in Deactivate event of the employee and project forms to move to previous record, thus committing the new record. Not sure what the code would be. May not be simple, I will have to explore.

Yes is working.

Where & How you add cbx to the comboboxes I only see cbx below the code Private Sub Form_Activate and in the Selection type: Form combobox on the Property Sheet on the Effort Form. Neither of the comboboxes have the control source to cbx. For example EmployeeID comboboxes don't have cbx but in the code you add cbx_ _ _ _ _..

sorry but not yet understand how June 7

I have put another revision of the project at the Box site.
http://www.box.net/shared/1hn4s949rg
I modified it as I suggested in previous post. Now whenever you leave the Employees or Projects form the first record on that form will be selected therefore committing the new record that may have been just entered.
The attached image shows the Effort form in design view and the PropertySheet window. On the PropertySheet you will see the name of the EmployeeID combobox is cbxEmployeeID. That is why I use that name in the code for Effort form. Same for CategoryID and ProjectID.

hello again June7 i want to change Proyect ID to File Number in the Proyect Form but when I delete the autonumber to asing number to the Proyects receive a message that the Proyect ID is related to other field in the Effort Form.


What i can do to asign File number to the Proyect without interfering with the other two form.

I want to this because when i use the report
In the Proyect ID/Title appears autonumber ID and the ProyectTitle. The user can't related this autonumber with the archived documents here.
I am working, trying to resolved

I solved using a input mask AA/-AAA.
I need change the total effort to subtotal, sum the academic, calendar and summer in 3 different subtotal for pending and current. I think that is done using the control source Property
Can anyone help me?

Used input mask for what?
Doesn't the report I provided already subtotal for each employee by Pending and Current and then also by Category within each of those? Do you want a summary report that does not group on employees? Copy this report and then change the grouping setup. Don't think you need to change the RecordSource query, change the grouping.

Following is what I did in response to your previous post:
Cannot delete the autonumber fields without doing some editing first because these are the key fields used to link records. It is not necessary to delete the autonumber field to do what you want. The quick fix is to add a new field to the Projects table (FileNumber). In the report, modify the textbox that shows the Project autonumber ID and title, replace the autonumber field name with the new FileNumber field. I have placed a new version of the project at the Box site. http://www.box.net/shared/vymzj4116f

If you want to use a natural ID (FileNumber) as the key field for Projects have to:
1. also add field to Effort table for the FileNumber data
2. enter file numbers into Projects and Effort table
3. delete the relationship on ProjectID: DatabaseTools/Relationships/AllRelationships
4. delete the ProjectID field from Effort table
5. delete the autonumber field from Projects
6. define FileNumber field in Projects as key field
You could follow these same steps to use a natural employee ID number as key field in Employees table.

I should have given more information in my Last Post. This is what I did.
The Proyect ID substitute to File Number and add an input mask AA/-AA. Because the Project are archieve on that office with ID numbers like 04-063. But the character
(-) does not appear in the report. I only see 04063/ProyectTitle. (This happen in the report only)

the other that happen is with the Effort totals in the Report is that it would be better doing this by subtotal to each category.
I having problem because what I doing to create the subtotals by category is to use the control source property to see if I can change the formula =Sum([Effort]) to this
=Sum([CategoryAcademic]) but those not work and give a mismatch error.

I hope this help