Calculation in subform

[essence]

Here is the scenario: I have a table Finance with the following fields

Table Finance
Id
ProjectNo
Type
Amount
Commitments
Expenditure

Type is a combobox with the selections PR, PV, SR, PDN

This information is displayed in a subform 'Financials' with linkage to the main form via projectno and commitments and expenditure are unbound fields.

Once a type is selected, the user enters the value of the type in field ‘Amount’.

My problem starts now:
When the user selects PR, I need amount to be assigned to commitments
When the user selects PV, I need amount to be assigned to expenditure
When the user selects SR, I need amount to be assigned to commitments
When the user selects PDN, I need the commitments textbox to reflect a deduction of the value of the amount (always a negative figure) and the expenditure textbox to increase by the value entered in amount.
Also, I need the commitments and expenditure values to be posted and saved to the table.

I am thinking about placing an if statement in the AfterUpdate event of the amount textbox that would look something like

If Type=PR then
txtAmount = txtCommitments
Else
If Type = PV then
txtAmount = txtExpenditure
Else
If Type = SR then
txtAmount = txtCommitments
Else
If Type = PDN then
txtCommitments = txtCommitments - txt Amount
txtExpenditure = txtExpenditure + txt Amount
End If
End If
End If
End If

However, when I enter this code, it doesn't run. Can someone offer some assistance.

Thanks

 

[boblarson]

First of all, you should rename your field "Type" as that is an Access reserved word and the use of it may cause you unwanted results, depending on where you use it.

Here's an example of what should work, if you changed your field name to something like expType and your text box that houses expType to txtType:

Select Case txtType
 Case "PR"
    Me.txtAmount = Me.txtCommitments
 Case "PV"
    Me.txtAmount = Me.txtExpenditure
 Case "SR"
    Me.txtAmount = Me.txtCommitments
 Case "PDN"
    Me.txtCommitments = Me.txtCommitments - Me.txtAmount 
    Me.txtExpenditure = Me.txtExpenditure + Me.txtAmount
End Select

 

[essence]

I tried the code but it is not working. Should I place the code on another event? It is currently on after update of the amount text field. The amount entered is not being displayed in commitments or expenditure. How can I bind the information to the field and save it to the table?

 

[boblarson]

I tried the code but it is not working.

When you say it is not working are you getting error messages, or is it just not doing anything?

 

[essence]

Sorry, it is not doing anything

 

[boblarson]

Can you upload a copy of your database (first going to Tools > Database Tools > Compact and Repair and compacting and then zipping it with WinZip or something like it)?

 

[essence]

Sorry about the delay...but here is the db file.

 

[boblarson]

Well, I solved the mystery of why it wasn't working. You (and therefore I when I modified your code) had your formulas backwards. You need to set them thus:

Select Case cboRequisitionType
 Case "Purchase Requisition"
    txtCommitments = Amount
 Case "Payment Voucher"
    txtExpenditure = Amount
 Case "Stores Requisition"
    txtCommitments = Me.Amount
 Case "Part Delivery Note (PDN)"
    txtCommitments = txtCommitments - Amount
    txtExpenditure = txtExpenditure + Amount
End Select

Also, you need to bind the Expenditure text box to Expenditures or else it isn't going to do you any good.

 

[essence]

Oh Bob, you're a genius! Thanks, works like a charm.

 

[boblarson]

So you got it okay? I saw the email that got generated, I believe from your last post, but you edited it. Originally it said that you still couldn't get it to work. So you came back and modified the post to say you did get it?

I just want to make sure you've got it.

 

[essence]

Originally, I was not getting it to work but I checked the name of the text boxes/combo box and it was not the same as in the code. Now, that part works great. So yes, I got it okay. Thanks again.