Linking Tick Box controls 2

[Viv1]

I have 3 tick boxes that affect the value in a text box on the same form called Subscriptions_Balance. They all work individually but not together. Is there a way to write these so they work together as both the Direct_Debit and Reduced_Subscriptions need to know whether the EC_Through_Igem is true?


Private Sub Direct_Debit_AfterUpdate()
If Me!Direct_Debit = True Then
Me!Subscriptions_Balance = (Me!EC_Fee + Me!Subscriptions) - Me!Direct_Debit_Discount
Else Me!Subscriptions_Balance = Me!EC_Fee + Me!Subscriptions

End If
End Sub


Private Sub Reduced_Subscriptions_AfterUpdate()
If Me!Reduced_Subscriptions = True Then
Me!Subscriptions_Balance = Me!Reduced_EC_Fee + Me!Reduced_Subscription
Else
Me!Subscriptions_Balance = Me!Subscriptions + Me!EC_Fee

End If
End Sub


Private Sub EC_Through_IGEM_AfterUpdate()
If Me!EC_Through_IGEM = True Then
Me!Subscriptions_Balance = Me!Subscriptions + Me!EC_Fee
Else
Me!Subscriptions_Balance = Me!Subscriptions
End If
End Sub

 

[TheAceMan1]

How are ya Viv1 . . . .

What you ask is all well and good, except, you forgot to mention what happens to Direct_Debit and Reduced_Subscriptions if EC_Through_Igem is True/False?

or

Just what is the interaction your looking for?

TheAceMan

 

[Viv1]

HI,

Thanks for the reply...

Ok, the Subscriptions_Balance field is affected when EC_Through_IGEM is True/False.

Direct_Debit takes a discount off full Subscriptions but not off Reduced_Subscriptions.

Reduced_Subscriptions takes the balance to £44.

The Subscriptions_Balance is a combination of either Subscriptions/Reduced_Subscriptions with EC_Fees (If EC_Through_IGEM= True).

If EC_Through_IGEM = False Then
Subscriptions_Balance = Subscriptions/Reduced_Subs (NOT + EC_Fees)

BUT

If EC_Through_IGEM = True Then
Subscriptions_Balance = Subscriptions/Reduced_Subs + EC Fees


I just don't seem to be able to get them to work together to get the right balance.

Thanks, Viv

 

[TheAceMan1]

Viv1! . . . . . .

First, your problem is eaisly resolvable. Its the required logic thats still a little hazy. So hang in there!

To save time and prevent other questions, I've devised the logic table below, which accounts for all the possible combinations of your check boxes. For each line, show the actual formula required for Subscriptions_Balance. If something is not allowed or calculations are not performed, type it as such. Note: DD=Direct_Debit checkbox, RS=Reduced_Subscriptions checkbox, and EC=EC_Through_IGEM checkbox.Also 1=True, 0=False.

 EC    RS    DD
----- ----- -----
  1     1     1   =
  1     1     0   =
  1     0     1   =
  1     0     0   =
  0     1     1   =
  0     1     0   =
  0     0     1   =
  0     0     0   =

The table will reveal resolution of the problem.

I await . . . . . . .

TheAceMan

 

[Viv1]

Ok,
The table is as follows...

EC RS DD
----- ----- -----
1 1 1 = Reduced_EC_Fee + Reduced_Subscriptions
1 1 0 = Reduced_EC_Fee + Reduced_Subscriptions
1 0 1 = (EC_Fee + Subscriptions) - DD_Discount
1 0 0 = EC_Fee + Subscriptions
0 1 1 = Reduced_Subscriptions
0 1 0 = Reduced_Subscriptions
0 0 1 = Subscriptions - Direct_Debit-Discount
0 0 0 = Subscriptions

Reduced Fees do not apply for a DD_Discount.

I have tried and tried today but cannot get the check boxes to link properly...either 2 work or none! (I am a beginner with VBA!).

If you can see the logic now and the solution is still easy that will be fantastic! I have been driving myself crazy with this!

Thanks for helping

 

[TheAceMan1]

Relax Viv1!

Don't go crazy . . . . . This is one of the reasons why there's Tek-Tips!. And yes, the solution is still easy!

Started writing the code when I noticed an ambiguity with Reduced_Subscriptions. Although it is the name for one of the check boxes, you also use it within the routine itself. Since it is represents a checkbox, it will only return (-1) or (0). Are you aware of this? Understand, you cannot have a checkbox and a field with the same name!. It may only some sort of typo. As a matter of fact, check the spelling of all Named items. Reslove this before I finish the code and let me know. (Your answer could change the logical flow of things).

Also you stated: DD_Discount. I assume you ment Direct_Debit_Discount. Am I correct in this?

Again . . . . . relax . . . . . resolution is at hand. I have meetings to attend, so I may not be able to post back until tomorrow mourning. Until then . . . . . . . .


TheAceMan

 

[TheAceMan1]

OK Viv1 . . . .(Sorry to post so late . . .)

First, backup and delete the code in the AfterUpdate Event for each CheckBox.(Delete the code, not the whole routine).

Add the following line to each CheckBox AfterUpdate Event:

Call CalcBal

Next, add the following code to the module:

Public Sub CalcBal()
   Dim cbEC As Control, cbDD As Control, cbRS As Control
   Dim valDDD As Control, SubBal As Control
   Dim valEC As Control, valREC As Control
   Dim valSub As Control, valRSub As Control
   Dim Bin As Byte
   
   With Me
      'CheckBoxes
      Set cbEC = .EC_Through_IGEM
      Set cbRS = .Reduced_Subscriptions
      Set cbDD = .Direct_Debit
      'Controls
      Set valDDD = .Direct_Debit_Discount
      Set valEC = .EC_Fee
      Set valREC = .Reduced_EC_Fee
      Set valSub = .Subscriptions
      Set valRSub = .Reduced_Subscription
      Set SubBal = .Subscriptions_Balance
   End With
   
   'SetUp Binary
   If cbEC Then Bin = Bin Or 4
   If cbRS Then Bin = Bin Or 2
   If cbDD Then Bin = Bin Or 1
   
   'Calculations
   If Bin > 5 Then
      SubBal = valREC + valRSub
   ElseIf Bin = 5 Then
      SubBal = valEC + valSub - valDDD
   ElseIf Bin = 4 Then
      SubBal = valEC + valSub
   ElseIf Bin > 1 Then
      SubBal = valRSub
   ElseIf Bin = 1 Then
      SubBal = valSub - valDDD
   Else
      SubBal = valSub
   End If
   
   Set cbDD = Nothing
   Set cbEC = Nothing
   Set cbRS = Nothing
   Set valDDD = Nothing
   Set valEC = Nothing
   Set valREC = Nothing
   Set valSub = Nothing
   Set valRSub = Nothing
   Set SubBal = Nothing
End Sub

Compile and save, then give it a whirl . . .

This should do it!


TheAceMan

 

[Viv1]

Thank you so much for all the help! It is REALLY appreciated! And I am getting the hang of VBA now as well.

Ok, I did that code and it works perfectly.

One thing I need is when a text box called 'Age' has a value of 70 or over for the balance to be different again! (SORRY!)

If Age >= 70 Then Subscriptions_Balance = 0
UNLESS EC_Through_IGEM = True Then Subscriptions_Balance = Reduced_EC_Fee

I will have another go with this today and see if I can get it to work but what I have tried so far hasn't worked.

****

I have a subform on the form we've been using called 'Account_History'. This has the following fields:
- MembershipNo (PK and REF to Member Table)
- Payment_Date
- Subscription_Amount
- EC_Fee_Amount
- Payment_Method (eg. Cheque etc)
- Payment_For (eg. Subscriptions/Subscriptions and EC Fee/EC Fee)
- Total (Subscription_Amount + EC_Fee_Amount)

When a payment is entered I need to to reduce the Subscriptions_Balance field that we have been calculating in member (using EC/RS/DD).

Each year in December a 'Subscription Run' is performed, this uses the correct Subscriptions and/or EC Fees from the check boxes ticked using the correct fees for that year (Stored in the Fee tables). This total needs to be shown in the Subscriptions_Balance text box (as you have helped me with already). The problem is I need the most recent payment in Account_History.Total to reduce the Subscriptions_Balance by however much is paid. Then at the end of the year the Subscription_Balance needs to be calculated and entered again so that invoices can be sent.

Have you any ideas on how I can solve this?

Basically, for example:
Member 1234 Has a balance of £100 in 2003 (£70 Subscriptions and £30 EC Fees)

When they make a payment (in Account_History form) that year (2003) of £100 their Subscriptions_Balance needs to be = £0

Then at the end of the year the Call Function needs to make the balance £100 again so that a new invoice can be created for 2004.

The Account_History form is a subform that brings up all payments for the member currectly being shown in a tabular structure. I need to order these most recent first and to subtract the most recent (when entered) from the Subscriptions_Balance. Maybe 2 text boxes are needed showing how much the member should pay(Subscriptions_Balance) and a running balance using Member.Subscriptions_Balance and Account_History.Total??

God, this sounds confusing! You seem very good at Access tho so I have faith that you can push me in the right direction!

Thank you so much

Viv

 

[TheAceMan1]

Viv1 . . . . . . . I assume Age is on the same Form and has priority in calculations. IF so, replace the calculations part of the code with the following:

'Calculations
   If (Me!Age >= 70) And (cbEC = True) Then
      SubBal = valREC
   ElseIf Me!Age >= 70 Then
      SubBal=0
   ElseIf Bin > 5 Then
      SubBal = valREC + valRSub
   ElseIf Bin = 5 Then
      SubBal = valEC + valSub - valDDD
   ElseIf Bin = 4 Then
      SubBal = valEC + valSub
   ElseIf Bin > 1 Then
      SubBal = valRSub
   ElseIf Bin = 1 Then
      SubBal = valSub - valDDD
   Else
      SubBal = valSub
   End If

Hav'nt had time to go over the rest. Will get to it as soon as I can . . . . . . . . .


TheAceMan

 

[Viv1]

Thanks so much again...I really appreciate your help

 

[TheAceMan1]

Hay Viv1!

How about a Star for all the work?

TheAceMan

 

[Viv1]

If you can help me sort the other stuff you can have loads of stars!

 

[TheAceMan1]

You drive a hard bargain Viv1!

I hav'nt abandoned you, just getting caught up in year end closing at the Company and my children. (Its enough to drive a man mad. (TheAceMan is Single Father of three).

I've reviewed your post of the 15th and will get back to you tonight or tomorrow mourning. Til then Merry Christmas . . . .

TheAceMan

 

[TheAceMan1]

Viv1 . . . . .

I finally managed to do a complete review of your post on Dec 15. Again, with me, the programming is never a problem. It's understanding the logic of what you want thats required. So I have questions for clarification . . . . . .

1) You said: Each year in December a 'Subscription Run' is performed, this uses the correct    Subscriptions and /or EC Fees from the check boxes ticked using the correct fees for that    year(Stored in the Fee tables). This total needs to be shown in the Subscriptions_Balance    text box(as you have helped me with already).
   
   Are you saying this new Subscriptions_Balance is coming from the Fee Table, instead of      the CalcBal routine, according to the TickBoxes?
   
   If the above is true, does the new balance follow the same logic table formulas, omly the    data comes from the Fee Table? 
   
2) Are you saying you want Total Payments for the year sbutracted from CalcBal and the       balance calculated from the Fee Table?   

3) You said in your example of Member 1234: When they make a payment(in Account_History       form) that year(2003) of 100 their Subscriptions_Balance needs to be zero. Then at the      end of the year the Call Function needs to make the balance 100 again so that a new       invoice can be created for 2004.

What you want can be done, what I don't understand is carrying over 100 to 2004, when the balance was resolved?

Sugestions:
Definitely go with two textboxes. Information you need to see will not be hidden and you can follow it throughout the year.

If necessary, redo the formula's for the logic table according to the Fee Table.

Programatically, its eaiser to subtract the total Payments for the year(so far), including latest entry.

Required Info I Need:
Give the Actual names of the tables involved and their relationships(who's on the one side, who's on the many).

For each table, give the names of all fields of interest, including primarykeys as 1st entry.

Example Table Listing:

(Table Name Here)
PrimaryKeyName as Type (PK) > RelationshipTiedTo
FieldNames as Type

(Customers)
CustomerID as LongInteger (PK) > Orders!CustomerID
FirstName as Text

(Orders)
OrderID as LongInteger (PK) > OrderDetails!OrderID
OrderNo as LongInteger
OrderDate as Date

Its not confusing Viv1, it just has to be dealt with. Resolution is near!

I await . . . . . . . . .

TheAceMan

 

[Viv1]

Ok, here goes!

The Member form includes fields from the Fees table, the fields are:

- Subscriptions
- Reduced_Subscription
- EC_Fee
- Reduced_EC_Fee
- Direct_Debit_Discount

These are the fields used in the function CalcBal....they are shown on the Member form but are changed/updated/deleted in the Fee form. The function CalcBal is used to take the values from the Fee table (shown on the Member form) and depending on the check boxes ticked...uses different values.

Each year the subscriptions will increase slightly and these values will be changed on the Fees form and as the fields are shown on the Member form the values will change here and the CalcBal will work out the new balance.

So, Yes....the new balance follows the same logic and the data still comes from the Fees table/form.

In December, I need the balance to be credited so that every member in the database owes money shown my a positive balance (apart from those over 70 with no EC Fees). We then send out the invoices that need to show a breakdown of Subscriptions(with or without Direct_Debit_Discount)/Reduced_Subscriptions AND EC_Fees/Reduced_EC_Fees (if subscriptions or EC Fees is 0 then £0.00 is shown) This is obviously worked out using the CalcBal function that adds them together....but I need to store which values (Subs/EC) are used to make this balance. Should I have a Subscriptions_Balance AND EC_Fees_Balance THEN a Total_Balance used in the CalcBal function???


Here's the table schema:

(Member)
MembershipNo as Number(PK)
Membership_Grade as Text (FK)>Fees!Membership_Grade
EC_Registration as Text (FK)>EC_Fees!EC_Registration
DOB as Date
Age as Number
Direct_Debit as Yes/No
Reduced_Subscriptions as Yes/No
EC_Through_IGEM as Yes/No
Subscriptions_Balance as Currency


(Fees)
Membership_Grade as Text(PK)>Member!Membership_Grade
Subscriptions as Currency
Reduced_Subscription as Currency
Direct_Debit_Discount as Currency

(EC_Fees)
EC_Registration as Text(PK)>Member!EC_Registration
EC_Fee as Currency
Reduced_EC_Fee as Currency

If you can make sense of all that I will be amazed!!
Thanks!

 

[koresnordic]

hey aceman. You haven't finished this yet, but since you have put in a lot of hard work and viv1 seems a bit tight with the stars, I will credit you with one. It's people like you who make this forum worthwhile. I help when I can, but without people like yourself, I would have come unstuck a few times. This star is for you (I always give stars when people help me, but this is an extra special occasion).



Graham

 

[cranebill]

You get a star from me too.. i was able to use some of this in my problem and now I do not need to post a question

Bill

 

[TheAceMan1]

koresnordic . . . cranebill . . . how are ya!

I am no different from yourselves. At the age of 53, I did'nt get this far (and in pretty good standing), without the help of a great many people. Enough of those who spent an enormous amount of time, because as they said, "They Believe In Me!".

Its hard, trying to live up to that, and I do'nt think I can ever entertain it fully, but when you have those kind of people behind you (like yourselves), it provides all the drive you need to try.

I was taught to give back and always remember where I came from. Tek Tips is one way I accomplish this.

In great appreciation for your posts . . . . .

I Bow On One Knee To You Both!

Merry Christmas! . . . . . .

TheAceMan

 

[Viv1]

I wasn't being tight with the stars..this is the first time I've used Tek-Tips and I didn't realise that you gave stars!

 

[koresnordic]

Sorry viv1, didn't mean to offend



Graham