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]

Do not feather yourself Viv1 . . . . . .

AceMan is preparing to go away for the holidays. Won't be back home (New York) until the 5th of Jan. Plan to leave tomorrow.

I may get back to you a little late, but I will get back (Always take laptop with me). A lotta good people I need to see.

I'll try to post tomorrow. Until then . . . .

~~~~~~~~~~~~~~~~~~~~~~
~~ Merry Christmas! ~~
~~~~~~~~~~~~~~~~~~~~~~

TheAceMan

 

[Viv1]

It's ok for some! I am sat in work and am bored! Hope you had a nice holiday and a Merry Christmas.

I look forward to working out the rest of this code and then to learn it so I can do it myself more!

Thanks!

 

[Viv1]

Hi Ace Man,

Hope you had a nice Christmas? I don't suppose you've had a chance to think how I can credit/debit the balance yet have you? Not wanting to sound desperate...which I am! Wish I was better at VBA and Access! There's a few other little things I need to ask too which I am sure are easy but I can't find how to do it.

Sorry for all the questions but you seem to know your stuff and I really need the help!

Thank you, Viv

 

[TheAceMan1]

Happy New Year Viv1 . . . . . .

Need to refresh my orenitation with this thread. I'll get back to you in a few days . . . . .

TheAceMan

 

[TheAceMan1]

Are you there Viv1! . . . . . .

Important!: Don't forget to backup the database before you make any changes!


If I understand you correctly you want:

1)The default calculation(view) of Subscriptions_Balance to always show reduction by Account_History!Total, with the option of calculating without Account_History!Total and reverting back? Is this correct?

If the above is correct, will you need the option for the current member record, all members, or both? I suggest all members (I believe this is what you want) with two command buttons for calculations; one for Subscriptions_Balance with Account_History!Total, and one without(any payments automatically update the balance). If you require both, then I suggest an additional form be made to select the options. The opening of this form can triggered by many things . . . . a command button or double-clicking the Subscriptions_Balance control . . . for instance, or an option group could be added to select current record or all.

2) To order the recordsource for Account_History by Payment_Date Decending?

This is easy. Just make a query with the same fields, set the sort field to decending and change the Recordsource property for the form to the query.

3) To store the values used to calculate the balance.

We will get to this if we can get past the two above(they need to be done first). A big problem here is that you don't know what was used for all current records in the database. Aside from that, be assured they can be saved as well as the check combo and formula used.

Elaborate on these things well, as your designing a part of your interface here . . . . and this is how its going to work!

Questions
---------

a) are the Fees & EC_Fees tables, look-up tables?

b) Your description of the Account_History tables leads me to believe its involved in a One-To-One relationship via MembershipNo. Is this true?

c) Is Account_History a subform on the Members form?

Info I Need
-----------

To get a better Idea of whats going on in your database and to resolve what appears may be relationship problems, please post(if your not going to break any proprietary laws) all table relationship pairs using the following format. Note: look at the Relationship Window when you gather this info. IF a relationship link does not show the typical 1 to many identifiers, right click the link & select edit. At the bottom of the window that opens is the type of relationship.

TableName!PrimaryKeyName > 1 to Many > TableName!ForeignKeyName
TableName!PrimaryKeyName > 1 to 1 > TableName!PrimaryKeyName

Communication
-------------

In an attempt to enhance our communications and hopefully provide help of another kind . . . . You have a great tendency to over complicate things Viv, to the point it all seems so overwhelming to you. If you simply state what you want without all the where's & whys, you will be more to the point, specific, and less confused. Let the other party ask for more specifics if required.

For instance: I want a table sorted by date, decending. Simple . . . to the point . . . . specific. I can say the same thing after explaining the complexity of a database(one or more paragraphs) and one reading it, would be totally confused by the time they got to the end and miss the point! . . . . . . . . see what I mean!

Relax . . . . . . . your in good hands . . . . . . .[/code]


TheAceMan

 

[Viv1]

Hi,

Yep I am here...I will answer those questions this afternoon. Got to make sure I'm telling you the right info!

Thanks!

 

[Viv1]

Right, here goes….

I am terrible at explaining things as I always try and get too much info across, sorry!

Regarding the Subscriptions_Balance field, this is now un-changing (static) and only shows how much a member should pay each year regardless of whether they have paid anything or not. I have created some fields in the Member table to perform calculations in:

- Running_Balance
- Calculate_Total
- Left_Over
- Spare_Field

The Running_Balance field is the one which shows how much a member has outstanding that year. When they pay, if they do not pay the full amount then the remaining will go into the Left_Over field. At the end of the year the Subscriptions_Balance and Left_Over fields are added together to give the balance for the next year to stop members from paying too little. The spare fields are just there to perform calculations as I’ve been told I ask text boxes to perform too many tasks and show results…which doesn’t work! This will need to work for ALL members as you suggested. I think a cmdButton on the Account_History subform to enter the payment is a good idea, it’s a tabular structure.

Ok, to answer your questions:

a) Not sure what you mean by look-up table. The Fees are stored and referenced on the Member form so people using the database can see how much full subscriptions/reduced subscriptions, direct debit discount and EC fees are while using the member form (shown in text boxes, the form is then based on a SELECT….statement)
b) Account_History and Member are one to many. 1 Member can have 0..1..n entries in Account_History (for each year a subscription is paid and recorded)
c) Yes, Account_History is a subform on the Members form, linked by MembershipNo.


Relationships

Member!MembershipNo > 1 To Many > Account_History!MembershipNo

EC_Fees!EC_Registration > 1 To Many > Member!EC_Registration

Fees!Membership_Grade > 1 To Many > Member!Membership_Grade


Right, I will let you ask questions now!

 

[TheAceMan1]

OK Viv1 . . . .

Your not terrible at explaining things. Just keep the content of your posts focused and short as possible.

As of the new fields you've added, are they working correctly?

Forget about LookUp Tables, your not using them.

In my previous post is 1) now voided?

As for things you want to do, do you still require 2) & 3) of my previous post?

If MembershipNo is the FK of Account_History, whats the PK?

With things as they stand now, what do you want to do next?


TheAceMan

 

[Viv1]

Ok, I think I forgot the PK part of the Account_History table: It is a composite key made up of Payment_Date AND MembershipNo, so a member can have many payments using various dates.

Well, the fields on the member form are there but I am not sure about the coding behind them, where to put it or how to write it so it applies for each member! Here’s what I would like them to do:

Running_Balance = This shows any amount outstanding from the static Subscriptions_Balance field or the whole amount that the member owes, this could be 0 if the total in Subscriptions_Balance is paid or the same as Subscriptions_Balance.

Calculate_Total = At the end of the year when subscriptions are credited this field adds the Running_Balance field with the Subscriptions_Balance field

Left_Over = This field is used for calculation of the amount remaining by subtracting the most recent Balance amount (in Account_History) from Running_Balance in the Member form, could be 0 if full amount is paid.


Spare_Field = In case of any extra calculations needed.


For Example:
Subscriptions_Balance = £100

Running_Balance = £100
Balance (Amount Paid) = £75
Left_Over = £25

At the end of the year:
Running_Balance = Subscriptions_Balance (£100) + Left_Over (£25)
= £125


The re-crediting of the Running_Balance field with Subscriptions_Balance and Left_Over I would like to have a ‘Subscriptions’ cmdButton and an ‘On_Click()’ event. When the payment is entered into the Account_History subform I am not sure how to make this subtract from the Running_Balance field. Do you recommend a cmdButton or OnEnter in the subform? This needs to work for ALL members.

Ok, question 1) is not voided, just a different way of doing it using a few fields for the calculations so that all eventualities can be catered for.

2) and 3) are still necessary. Am I correct in thinking that in 3) you meant the EC (and Reduced) and Subscriptions (and Reduced) and how they make up the balance so I can show the total of each paid by every member in the database? E.g. Total EC AND Total Subscriptions (bearing in mind some people get a discount paying by DD)

What I need to do is:
a) Work out how to make the fields calculate the running balance, linked from the latest payment made and store any left over value, if any
b) Have a cmdButton that adds the Subscriptions_Balance to the Running_Balance field and puts this in the Running_Balance field (using the Left_Over field for the calculation
c) Make the database recognise when a payment is entered and to subtract this from the appropriate member’s Running_Balance, maybe using a cmdButton on the Account_History subform
d) Calculate the total Reduced and Full EC Fees and Subscriptions paid, including the amount of discount paid by DD payers paying Full Subscriptions.
e) Link the calculated fields back to the table as at the moment they do not link to that and cannot be queried. The Age field that calculates the age using the DOB does not show up in the table only on the form….not sure how to make it update in the form too

And I think that is about all! I can see sone light at the end of the tunnel!

Thanks so much for the help! Viv

 

[Viv1]

Hi,

Any joy?? Sorry to pester..getting worried!

Thanks, Viv