D
default105
Access 2k3
Access 2k file format
Good Day,
I was wondering if I could modify my existing code to work with the
following situation. Let me explain my goal first then the code I am using.
I have a form called Members and in it a command button to view
payments(Payments is another form). This form is filtered using the MemberID
field in both forms. I am using several fields on the main form Members to
display the Balance Outstanding and the Accrued Credit. I am using the
following code to generate the effect.
Dim BOResult
BOResult = IIf(IsNull([MemberID]), 0, DCount("*", "Payments", "[MemberID]="
& [MemberID]) * [MemberDues] - DSum("PaymentAmount", "Payments",
"[MemberID]=" & [MemberID]))
If BOResult <= 0 Then
Me![BalanceOutstanding].Value = "$0.00"
Me![BalanceOutstanding].ForeColor = 0
Me![AccruedCredit].ForeColor = 4227072
Me![AccruedCredit].Value = BOResult
Else
Me![BalanceOutstanding].ForeColor = 2105599
Me![BalanceOutstanding].Value = BOResult
Me![AccruedCredit].ForeColor = 0
Me![AccruedCredit].Value = "$0.00"
End If
The problem is it uses dcount to get the total number of records and
multiplys it by the monthly dues then subtracts the total number of records
payments(which is not accurate and my faulty code, I need it to know the
number of months to calculate the total amount due and then subtract the
total payments to render a result). I was thinking that if I would use a
dcount to count the number of times a check box(indicating a new month
payment) is checked(yes), and multiply it by member dues then subtract it
from the total number of payments, it would then be better for the user
because they will not be doing calulations in their head plus they would have
a better trend of when members payments are made.
I do not know how to get dcount to work when counting a particular field, in
a different form, that is filtered to memberID. I have failed with all
attempts but I feel that the reasoning is sound for this to work or maybe I
am on the wrong track and I need to use different coding. Any help given is
greatly appreciated and will be documented in the database. Thanx
Access 2k file format
Good Day,
I was wondering if I could modify my existing code to work with the
following situation. Let me explain my goal first then the code I am using.
I have a form called Members and in it a command button to view
payments(Payments is another form). This form is filtered using the MemberID
field in both forms. I am using several fields on the main form Members to
display the Balance Outstanding and the Accrued Credit. I am using the
following code to generate the effect.
Dim BOResult
BOResult = IIf(IsNull([MemberID]), 0, DCount("*", "Payments", "[MemberID]="
& [MemberID]) * [MemberDues] - DSum("PaymentAmount", "Payments",
"[MemberID]=" & [MemberID]))
If BOResult <= 0 Then
Me![BalanceOutstanding].Value = "$0.00"
Me![BalanceOutstanding].ForeColor = 0
Me![AccruedCredit].ForeColor = 4227072
Me![AccruedCredit].Value = BOResult
Else
Me![BalanceOutstanding].ForeColor = 2105599
Me![BalanceOutstanding].Value = BOResult
Me![AccruedCredit].ForeColor = 0
Me![AccruedCredit].Value = "$0.00"
End If
The problem is it uses dcount to get the total number of records and
multiplys it by the monthly dues then subtracts the total number of records
payments(which is not accurate and my faulty code, I need it to know the
number of months to calculate the total amount due and then subtract the
total payments to render a result). I was thinking that if I would use a
dcount to count the number of times a check box(indicating a new month
payment) is checked(yes), and multiply it by member dues then subtract it
from the total number of payments, it would then be better for the user
because they will not be doing calulations in their head plus they would have
a better trend of when members payments are made.
I do not know how to get dcount to work when counting a particular field, in
a different form, that is filtered to memberID. I have failed with all
attempts but I feel that the reasoning is sound for this to work or maybe I
am on the wrong track and I need to use different coding. Any help given is
greatly appreciated and will be documented in the database. Thanx