Automatic update form-fields

[ChrisWisselo]

Hello every one.

Very new to all this, but trying.

I have a form (payment info). It has a field 'AmountDue', and 5 payments. Then there is the field 'Balance' and 'Status' (see attached screenshots).

Now... I would like the following to happen:
Field 'Balance': Updated -> AmountDue minus the payments.

Field 'Status':
If 'Balance' = 'AmountDue' -> Display "Unpaid"
If 'Balance' < 'AmountDue' -> Display "Partial"
If 'Balance' = '0' -> Display "Paid".

How can I achieve this?! I've been going trough forums and other help-resources, but I can't figure it out.

Any advice is most welcome.
Thank you!

Chris
[imgnothumb]http://bytes.com/attachments/attachment/5993d1327856165/paid.jpg[/imgnothumb]
[imgnothumb]http://bytes.com/attachments/attachment/5994d1327856165/partial.jpg[/imgnothumb]
[imgnothumb]http://bytes.com/attachments/attachment/5995d1327856165/unpaid.jpg[/imgnothumb]
[imgnothumb]http://bytes.com/attachments/attachment/5996d1327857036/design.jpg[/imgnothumb]

[NeoPa]

My advice is to think again about the approach you're using. It's a very non-database type of thinking that doesn't use normalisation (See Database Normalisation and Table Structures).

You really would be better stopping yourself now from going down that route and getting into an approach that is more consistent with Relational (database) theory. The linked article can certainly help you to grasp some of the fundamental thinking behind this.

[ChrisWisselo]

Hello NeoPa.

Thanks for the article! That's making quite some sense to me.

I'm re-doing the whole database structure to make sure it is normalized to N3.

To bad I'm so ambitious and my database is gonna be holding quite some tables. So re-thinking the whole thing, drawing it first and then building it. Might not have been a bad idea from the start. LOL

Regards,
Chris

[NeoPa]

I'm really so pleased by your reply Chris.

It's not unusual to get such questions here, but very few have the perspicacity to realise so quickly that, even though it will involve them in some extra up-front work, such a change to their whole approach will save them massive headaches in the long run. Good for you :-)

[ChrisWisselo]

Thanks NeoPa! :D

I will also post my 'design' on the forum later, to get a bit more feedback on the structure. It's just in Microsoft Visio though, but it helps me to get a clear overview.

[ChrisWisselo]

@NeoPa: Posted my design (as a .png-image) in a new question: http://bytes.com/topic/access/answer...-design-review