After Update To Open Form When A Field Is Higher Than Another Field

I am not sure if my syntax is correct so bear with me.

I have a form that is capturing project data by resource. Each resource is allocated a % of his or her time. When projects are assigned, if the % assigned exceeds the % allocated, I would like the following:

A check box (exceptions) to automatically be checked as well as the form (frmExceptions to open) This form is linked to a table that stores the resourceID and ProjectID. I need to be able to track exceptions by Project.

Can this be done easily on an afterupdate script in VB?

If so can you please help me with the code:

Field Names in my SubForm:

ResourceID:
ProjectID
Project% >> This is what a manager would enter a % by project
TotalProject >> Autofilled from Parent Form Table Source. This is what the Project%
should not exceed.
Exception (check box)

This can be tricky.
Each resource may be assigned multiple projects. I need to figure out a way to take into account totals on all Project%, not by Project ID.

Here is my code that I started for the manual process of just checking the exceptions box:

-----------------------------------------------------------------------------------------------------------------
Private Sub Check38_Click()
Dim stDocName As String
Dim stLinkCriteria As String

    stDocName = "frmExceptions"
    stLinkCriteria = "[ResourceID]=" & "'" & Me![ResourceID] & "'"

    DoCmd.OpenForm stDocName, , stLinkCriteria

End Sub
-----------------------------------------------------------------------------------------------------------

This is not working effectively, my form opens, however the ResourceID does not automatically populate.
gracie1972Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
jcrozier21Connect With a Mentor Commented:
Ok, I have changed the control sources of the two % totals on the parent form, and I have added a check for each percentage field on the subform. I also took the liberty of renaming two of the fields in your table, as having the '%' as part of a field name is a bad idea.

I've attached the mdb I modified, is this what you were after?
NACS-Resource-DB---BE---Copy.mdb
0
 
jcrozier21Commented:
I believe I can help you, but I need a little more info.

1) If I have 50% available and I am assigning a % by Project, how can I accurately calculate the totals for all ?

50%? Is this project% or daytoday%? What are you trying to total?

2) If I have  a resource and assign 60% (total determined from Question 1), how can I get my form to trigger an exception (Pop up window to notify we have exceeded the resources available %) this will trigger an exception form to open.

Are you simply after a check to see whether the total % (daytoday or project?) exceeds 100%?

I gather that this application is to track and assign human resources, am I right?
0
 
gracie1972Author Commented:
Yes I am working on a simple access database for tracking resources by project.  It get complicated as my subform tracks each project by project ID>

The 50% available is what is assigned to the Resource in the frmEditResources form.

The subform (frmProjects_subform) lists each project.  I am trying to find a solution that will know that totals in the main form automatically subtract in the subform by project ID.  However I can do this simply, but if I have more than one projectID it only totals on a line by line basis.  

Does this make sense?  I attached my DB for your review to see if I am explaining this correctly.
NACS-Resource-DB---BE---Copy.mdb
0
 
gracie1972Author Commented:
Well not entirely.  I see what you are doing.  I need to wrap my head around what was changed.  The message box and formulas seem to work. I need to change a few things.  The main form needed to have the data from the source table.  This is the entry form.  The calculations you did there, I can actually move to the subform which is what I need.  I will get back to you here shortly, but I think this will work :-)
0
 
gracie1972Author Commented:
Thanks!
0
All Courses

From novice to tech pro — start learning today.