Calculations

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

-----Original Message-----
I am developing a new database. I have a table for
EXPENSE.

How do I set a form to verify.

EXPENSE Total - Breakdown of expenses equals ??.

Expense - GAS+Entertainment+Supplies
100 20 20 60 = 0
Click to expand...


Assuming you have tables something like the following:

ExpenseReports
ReportID AutoNumber
EmployeeNumber
DateSubmitted
....

Categories
CategoryID AutoNumber
Category Text (Gas, Hotel, Etc.)

ExpenseDetail
DetailID AutoNumber
ReportID Number (foreign key to ExpenseReports)
Category Number (foreign key to Categories)
Amount Currency

and assuming you have a columnar subform showing the
details placed on a main form, you can display the total
by placing an invisible control in the subform's footer
with source:

=Sum([Amount])

and place a control on the main form referring to it:

=yoursubformname.Form!yourcontrolname

HTH
Kevin Sprinkel

If your situation is different, please post more detail
regarding your table structures and what you'd like to see
in your form.
 
-----Original Message-----
-----Original Message-----
I am developing a new database. I have a table for
EXPENSE.

How do I set a form to verify.

EXPENSE Total - Breakdown of expenses equals ??.

Expense - GAS+Entertainment+Supplies
100 20 20 60 = 0
Click to expand...


Assuming you have tables something like the following:

ExpenseReports
ReportID AutoNumber
EmployeeNumber
DateSubmitted
....

Categories
CategoryID AutoNumber
Category Text (Gas, Hotel, Etc.)

ExpenseDetail
DetailID AutoNumber
ReportID Number (foreign key to ExpenseReports)
Category Number (foreign key to Categories)
Amount Currency

and assuming you have a columnar subform showing the
details placed on a main form, you can display the total
by placing an invisible control in the subform's footer
with source:

=Sum([Amount])

and place a control on the main form referring to it:

=yoursubformname.Form!yourcontrolname

HTH
Kevin Sprinkel

If your situation is different, please post more detail
regarding your table structures and what you'd like to see
in your form.




.
Thanks Kevin. This is a very small company, with only
Click to expand...
one employee. I have only created a single table for
expenses. They had this information in EXCEL with two
different colums. The first one added the expense
breakdown with the Formula =sum(G1:o1) then the next
column did the verification with formula =f1-p1 (column
f is the total expense column and column p is the total
of the breakdown.

I real don't even care if this information shows on the
table. I just want to create a form that will verify
what has been posted. If the veification is not zero,
show the difference and not allow them to exit this
record until corrected and verifications is zero.

Thanks again, RFM
 
Thanks Kevin. This is a very small company, with only
one employee. I have only created a single table for
expenses. They had this information in EXCEL with two
different colums. The first one added the expense
breakdown with the Formula =sum(G1:o1) then the next
column did the verification with formula =f1-p1 (column
f is the total expense column and column p is the total
of the breakdown.

I real don't even care if this information shows on the
table. I just want to create a form that will verify
what has been posted. If the veification is not zero,
show the difference and not allow them to exit this
record until corrected and verifications is zero.

Thanks again, RFM
.
Click to expand...

Please post the structure of your table(s), and I can tell
you how to implement your desired result.

Best regards.

Kevin Sprinkel
 
-----Original Message-----

Please post the structure of your table(s), and I can tell
you how to implement your desired result.

Best regards.

Kevin Sprinkel
.
Kevin, I was able to create a box on my form that does
Click to expand...
the calculations.

However, one is never satisfied. Could you help with a
Msg Box.
Format is general number
data is control source =(Amount)-({General Expense}+
{Adverising)+(Fuel and oil)+(Auto repairs)+
{Entertainment}]

I am try to accomplish a way for: When exiting the
record, if the box value is not zero, a message would
appears stating "You are out of balance." Then
corrections must be completed before exiting this record.

Any suggestions?

I really appreciate your patience with me. RFM
 
I am try to accomplish a way for: When exiting the
record, if the box value is not zero, a message would
appears stating "You are out of balance." Then
corrections must be completed before exiting this record.

Any suggestions?
Click to expand...

RFM,

The basic strategy is using the Before Update event of the
form:

Private Sub Form_BeforeUpdate (Cancel As Integer)
' Display warning message box that total does not add up.

Dim strMsg As String, strTitle As String
Dim intstyle As Integer

If Me!yourcontrolname <> 0 Then
strMsg = "Out of balance."
strTitle = "Error Detected"
intStyle = vbOKOnly + vbInformation
MsgBox strMsg, intStyle, strTitle
DoCmd.CancelEvent
End If

End Sub

In the If statement, replace "yourcontrolname" with the
name of the control that should total zero.

HTH
Kevin Sprinkel
Becker & Frondorf
 
-----Original Message-----

RFM,

The basic strategy is using the Before Update event of the
form:

Private Sub Form_BeforeUpdate (Cancel As Integer)
' Display warning message box that total does not add up.

Dim strMsg As String, strTitle As String
Dim intstyle As Integer

If Me!yourcontrolname <> 0 Then
strMsg = "Out of balance."
strTitle = "Error Detected"
intStyle = vbOKOnly + vbInformation
MsgBox strMsg, intStyle, strTitle
DoCmd.CancelEvent
End If

End Sub

In the If statement, replace "yourcontrolname" with the
name of the control that should total zero.

HTH
Kevin Sprinkel
Becker & Frondorf
.
You are a genius. That is just what I needed. It works
Click to expand...
great. THANK YOU, THANK YOU, THANK YOU RFM
 
Back
Top