Need a way to calculate budget remaining while still in input form, for warning (1 Viewer)

[CIC]

My brain has pretty much gone to mush after three days of banging my head, so I am reaching out for help! I hope this does not get too confusing during my explaination.

I am using Access 2003 (and must use Access 2003 due to standardization) to set up a financial database to track transactions applied against pre-set budgets, that will reduce as they are used.

I have a number of issues, but we will start with the calculation of the warning message first.

I have been asked to include a warning message that, while the user is attempting to take money from an account that will be placed in a negative balance, will warn the user as soon as they enter the amount requested.

In essence, the input form asks for:
IO (account from drop-down list, number from tblIOLookup)
GL (subaccount to IO, drop-downj list, number from tblGLLookup)
Substantiation (Memo field)
Amount (number, to be entered)

The Budget amount is by GL (subaccount) which is stored in the tblGLLookup table. Part of the issue, which stems into another problem, is that the GL accounts are sub accounts to multiple IOs, or perhaps best described as the other way around:

IO's can be referrenced to any GL Account (i.e. IO 200494, IO 200495, IO 203760 can apply funds from GL 1131, GL 1132, GL 13999, GL 4128, etc)
(There are more than 30 IO accounts and more than 800 GL Accounts, any any IO can use funds from any GL account)

So, I believe I need assistance with the following:

a) while entering a new transaction, I need to be able to know how many dollars from a GL Account have already been used, so that when the user enters their requested amount into the Amount field, a warning will appear IF the user will put the GL Account into a negative balance; and

b) how can I query the TransactionEntry Table to have it total each GL Account and show me with IO Account has referenced the GL Account; and

***Bonus Marks!!

I have been asked to not allow a record to impact the Budget amount unless the record has been "approved" - as in the "Super-User" will go in and check off each record after reviewing the substantiation. I am at a loss as to how this can be done.

ANY any ALL assistance will be appreciated - I am on a tight timeline (I started Monday afternoon and they want to have this done by Friday, I said doubtful) so your input is appreciated!

Thanks!

 

[grzzlo]

My guess would be that this requires a DSum. Something like this maybe?

DSum("Amount","TransactionEntry", "Approved = -1 And GL=" & Me.cboGL & " And IO=" & me.cboIO)

I'm not clear on the connection between GL and IO, though...

 

[grzzlo]

Or maybe it should just be this:

DSum("Amount","TransactionEntry", "Approved = -1 And GL=" & Me.cboGL)

Do you just need to know the total amount approved per GL? Or is it the total amount per GL per IO?

That is, could you have two different IOs referencing the same GL and have a different budget for each? Or is there just one budget for each GL?

I think I need a more concrete example.

By the way this is just responding to the first question.

 

[CIC]

Or maybe it should just be this:

DSum("Amount","TransactionEntry", "Approved = -1 And GL=" & Me.cboGL)

Do you just need to know the total amount approved per GL? Or is it the total amount per GL per IO?

That is, could you have two different IOs referencing the same GL and have a different budget for each? Or is there just one budget for each GL?

I think I need a more concrete example.

By the way this is just responding to the first question.

Thank you for the response!!

Now, it has been more than four years since I actually "programmed" an Access DB and so I would just like confirmation on where to put the DSum entry listed above. I believe it goes in the Form but not sure where.

As for IO and GL, it is only the GL Accounts that have a budget set, the IO units are more like project accounts, wherein one person may control 2 or more IO accounts but each IO account can use money from ANY GL Account.

I will definitely have a few more questions, do you recommend I use this one thread or should I open others (as they don't all relate directly to the Form)?

THANKS again!

 

[grzzlo]

I'm new to the forum myself, but I think best practice would be to put each question in a new thread. I think each thread should be one topic such that someone with a similar question in the future can search the forum and find an answer without having to start a new thread.

I can't tell you exactly where the code goes in the form because I'm not sure what your form looks like, but I think it might go in the Form_BeforeUpdate event.

Or here's one possible solution:

You have a combo box bound to the GL field called cboGL. Then you have 2 unbound text boxes that look up their values based on the value in cboGL. The first one (here called txtBudget) displays the budget for that GL and has a control source like this:

=IIf([cboGL]>0,Nz(DLookUp("Budget","tblGLLookup","ID=" & [cboGL])))

Of course, you may need to change the field and table names above.

Then you have another (here called txtBalance) that displays the current balance with a control source like this:

=IIf([cboGL]>0,Nz(DSum("Amount","tblTransactionEntry", "Approved = -1 And GL=" & [cboGL])))

Then you have the following code in Form_BeforeUpdate:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Nz(Me.txtBalance) + Nz(Me.txtAmount) > Nz(Me.txtBudget) Then
        MsgBox "You're over budget"
        Cancel = True
    End If
End Sub

or you could skip the two unbound text boxes (depending on whether you want the user to know what the budget is as they enter transactions) and put it all in Form_BeforeUpdate:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Nz(DSum("Amount","tblTransactionEntry", "Approved = -1 And GL=" & Nz(Me.cboGL))) + Nz(Me.txtAmount) > Nz(DLookUp("Budget","tblGLLookup","ID=" & Nz(Me.cboGL))) Then
        MsgBox "You're over budget"
        Cancel = True
    End If
End Sub

If you do input checking you might be able to skip some of those Nz()'s. You should have already checked to make sure that txtAmount <> 0, for example.

To answer your second question:

Make a (select) query in design view. Show table TransactionEntry. View Totals. Group By GL, Group By IO, Sum Amount.

That's assuming that each GL only has one IO referencing it.

If this helped, please say a little prayer for me getting my question answered. (I'm answering all the questions I can today in hopes that the good karma will come back to me. It doesn't look hopeful.) You can also just hit the "Thanks" button below, but I don't know if that'll improve my karma or not...

 

[CIC]

or you could skip the two unbound text boxes (depending on whether you want the user to know what the budget is as they enter transactions) and put it all in Form_BeforeUpdate:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Nz(DSum("Amount","tblTransactionEntry", "Approved = -1 And GL=" & Nz(Me.cboGL))) + Nz(Me.txtAmount) > Nz(DLookUp("Budget","tblGLLookup","ID=" & Nz(Me.cboGL))) Then
        MsgBox "You're over budget"
        Cancel = True
    End If
End Sub

If you do input checking you might be able to skip some of those Nz()'s. You should have already checked to make sure that txtAmount <> 0, for example.

To answer your second question:

Make a (select) query in design view. Show table TransactionEntry. View Totals. Group By GL, Group By IO, Sum Amount.

That's assuming that each GL only has one IO referencing it.

If this helped, please say a little prayer for me getting my question answered. (I'm answering all the questions I can today in hopes that the good karma will come back to me. It doesn't look hopeful.) You can also just hit the "Thanks" button below, but I don't know if that'll improve my karma or not...

Again, thank you for your help - I was hoping that your good karma would help in getting your questions answered (I looked at this myself but short of removing the Undo item from the Access Toolbar, I didn't have a solution short of this possibly being at the Windows Registry level).

I have inserted the code you provided into the BeforeUpdate section in the Form's DesignView Properties (for the Form itself, not for one of the txt boxes) but received an error about a Macro that was not defined. So, in reading the code, it does make sense to me but I appear to be doing something incorrectly in applying it to my form.

Additionally, I still need to figure out how I can create a report showing both All IOs and the GLs and GL Amounts they reference, as well as a GL Report with GL Amounts, the IOs referencing them and the Budgets assigned/remaining.

I Think I am in way over my head, but then again fighting a flu this week is not helping my headspace, so I'm sorry if my questions border on beginner level, but that is where my brain is at!

Thanks again - I have been sure to Thank you for all your posts so far, as you are obviously trying.

 

[CIC]

More Details on my Database (as it is now)

Ok, here is more structure to the Database as it is currently:

tblIOLookup

Fields:
IO (number, Primary Key)
IO_Desc (txt)
IO_Cat (txt)

tblGLLookup

Fields:
GLID (number, Primary Key)
GLDesc (txt)
GLType (txt)
Budget (number, set statically one per year)

tblTransactionEntry

Fields:
ID (Autonum, Primary Key)
Date (Date, Date())
IO (number, RELATION from tblIOLookup)
GLID (number, RELATION from tblGLLookup)
Amount (number)
Substant (memo)
BudgetWarn (Number, not sure if this is needed!)
Approved (checkbox, only SuperUser is supposed to be able to use - how?)

There are five other tables that exist ONLY for Lookup data for a Worksheet Form I have to build, they are not involved in the main functioning of the TransactionEntry.


Forms:

TransactionEntry - directly correlates to tblTransactionEntry)
**built with wizard, no programming aside from making IO and GLID ComboBoxes to display Column 1 & 2 of their respective tbls)

Query:

qGLAmt - built with wizard - creates output for GLID and Sum of Amounts pertaining to each GLID (i.e. GLID 1131 Sum of Amounts 1359 (1200 +59+ 100, all entries in tblTransactionEntry))

NOTE:

I have been asked to set up security whereby specific users can only enter transactions under specific GLs (ie. User A can only enter trasaction for IO 204462 and IO 210663, keeping in mind that these IOs can reference ANY GLID account.) there are 8 users that they want this ype of security for, plus the ability for all 8 users to be able to VIEW ONLY all data in the database.

I have no been able to find any way to provide such security in Access 2003 with my depth of knowledge ans as such this request, along with the APPROVAL BY TRANSACTION request have been denied at this time, unless I can find a method.

I am providing these databse details to better describe what I am being asked to do and hope I can find soolutions to everything I have been asked to do, specifically:

- "Lock" the tblGLLookup table so that no changes can be made, except by the Admin (SuperUser)
- Provide a Budget Warning message while an individual is entering the Amount into the Amount field on the TransactionEntry Form (keeping in mind that others will have already depleated part or all of the Budget assigned in the tblGLLookup for the GLID being used and having a way to determine the Budget remaining, which I believe grzzlo has already helped me with if I can figure out how to institute grzzlo's code suggestions)
- Provide reports sorted by IO and then by GLID Sum of Amounts
- Provide reports sorted by GLID and then bu IOs Sum of Amounts that reference each GLID
- Set up a Worksheet Form for planning purposes, that calls on other Tables (strictly for Lookup data) and instigate Calculated fields and totals.

I hope this sheds additional light on my situation and Thanks again to grzzlo for their input so far and I have (higher) hopes that I may actually complete this task, but doubt it will be on-schedule.

 

[grzzlo]

I assume you changed the names to match your field names and control names? The main thing is for you to understand what the code is doing and then change it to make it work for you.

See if you can break the code apart and get one piece of it working at a time. Start with the DSum, for example. When I was starting out, I would put code in the On Dbl Click event of a text box to test it.

Put something like this in the On Dbl Click event of your substant text box:

MsgBox DSum("Amount", "tblTransactionEntry", "GL = 4128 And Approved = -1")

Then double-click the text box and see what happens. If it works, try and make it so that it looks up the value based on what's in your GL control on the form. And work step by step from there.

In the end, it should be in the BeforeUpdate event of the form. Start small there, too. Put a simple msgbox there to start out.

I would tell your boss not to expect user-level security any time soon.