Solved

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

Posted on 2013-01-14
5
361 Views
Last Modified: 2013-01-16
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.
0
Comment
Question by:gracie1972
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 3

Expert Comment

by:jcrozier21
ID: 38776456
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
 

Author Comment

by:gracie1972
ID: 38776496
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
 
LVL 3

Accepted Solution

by:
jcrozier21 earned 500 total points
ID: 38776566
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
 

Author Comment

by:gracie1972
ID: 38779523
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
 

Author Closing Comment

by:gracie1972
ID: 38783278
Thanks!
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question