Solved

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

Posted on 2013-01-14
5
335 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
  • 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now