Solved

Creating and comparing text fields

Posted on 2011-03-04
7
208 Views
Last Modified: 2012-05-11
I have a form called “frmppm” that displays Monthly Accounts, Amounts & Total of the Amounts when you enter the Person ID and Select the Period. This data is all from a table called “ppm”.
Question
My next task is to create another text box next to my Totals that has the Annual pay of this user divided by 12. This data comes from the “Annual Pay” field from another table called “Vendors”. The Fiscal year starts on the 1st of September of a year and ends on August 31st of the following year. Example we are in Fiscal Year 2011 which is indicated by “FY11” in the "FY" field of the vendors table. If the period selected for example is October 2010, then the “Annual Pay” that should be selected from the “Vendors” table is the one that has “FY11” and divide that amount by 12 to display in this new text box. If the period selected for example is October 2009, then the “Annual Pay” that should be selected from the “Vendors” table is the one that has “FY10” and divide that amount by 12 to display in this new text box. These 2 amounts from the text boxes are suppose to be the same but if they are different I want an error message to display Saying  they are not equal. Can you please help me to accomplish this task. Attached is a copy of the database with the ppm table, query and form. Also Attached is a copy of the Vendors table in excel format. I have also hi lighted the fields that matter in the vendors table.

Database11.mdb
Vendors-Table.xlsx
0
Comment
Question by:Chrisjack001
  • 3
  • 3
7 Comments
 
LVL 84
Comment Utility
You can use the DLookup function to "lookup" your value:

Me.YourTextbox = DLookup("Annual Pay", "Vendors", "FY='FY" & Cstr(Right(Me.txFY,2) - 1)) & "'")
0
 
LVL 6

Expert Comment

by:TinTombStone
Comment Utility
I think that is about it
0
 
LVL 6

Expert Comment

by:TinTombStone
Comment Utility
Sorry hit the wrong button!

I have made the requested changes (and a couple more)

See what you think

Bye the way, before you add too much more to the database, you need to take a serious look at the table structure

It will make your life a lot harder in the future if you dont sort it out now Database11.mdb
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:Chrisjack001
Comment Utility
This is it but they need to type the Person_nbr in the Person ID field as it was originally. Is it not possible to accomplish both
0
 
LVL 6

Accepted Solution

by:
TinTombStone earned 500 total points
Comment Utility
Changed it back for you Database11.mdb
0
 

Author Comment

by:Chrisjack001
Comment Utility
That Part is working again fine but it seems the error message is popping up every time you change the Month year date even when the totals are equal the error message keeps popping up. Thanks for your help again
0
 

Author Closing Comment

by:Chrisjack001
Comment Utility
Please disregard my previous email. The form is working perfectly as required. You are awesome. Thanks for all your help.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

728 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

11 Experts available now in Live!

Get 1:1 Help Now