Multiple Elements on One Sub-Form?

[simonpickering]

Hi,

I'm new to the forum, struggling and would really appreciate some help with an Access Form problem.

I'm building a database that will track payments made to individuals.

Each single payment consists of multiple elements that, when added together, result in one cheque made payable to the person. These Payment Elements are currently 'Hotel Room', 'Travel', 'Food' and 'Honorarium'.

I'd like to track all the elements so that I can add up, say, all the Food costs for a period.

I've made tables similar to these:

tblPerson
ID, FirstName, Surname, DoB etc.

tblPayment
ID, PersonID, Date, Method, etc.

tblPaymentElements
ID, PaymentID, PaymentAmount, ElementType

tlbElementTypes
ID, Description
​

PaymentElements are the many side of a one-to-many with Payments. ElementTypes is just a lookup table for the descriptions ('Hotel Room' etc).

So now I'm stuck.

I'd like a sub-form that contains rows of Payments - one row for each payment. There would be text boxes for Date and then the four current Payment Element types and possibly some code that adds the payments to display the total.

I can't work out how to filter each Payment Element text box on the sub form so that the first one only shows the record for Payment Element Type 1 (Hotel), the second for Payment Element Type 2 (Travel), the third for Type 3 (Food) etc.

It's as if I need four different sub-forms within my sub-form - but that seems complicated! I must be able to filter for the PaymentElement ID - within the text box perhaps?

Am I on the right lines? My only other way I can think of is to create separate tables for each payment type - but I think that breaks normalization rules.

Thanks for your time,

Simon

 

[MStef]

I think you don't need so many tables, (and forms).
Look at "DemoPaymentA20022003.mdb", (attachment, zip).
Look at Tables, Forms(VBA), RELATIONSHIPS.
Adapt it in your mdb.

 

[simonpickering]

Hi MStef,

Thank you so much for your time.

Your design isn't quite what I was looking for. I'm too new on the forum to post mock-up images of what I'm thinking but if I can do it in text:

Person Form
---------------
Name: Fred Smith
DoB: 1954-10-30

Sub-Form
------------
Labels -> Date; Hotel; Travel; Food; Honorarium
------ Rows -----
[2012-09-02] [£55.00] [£11.56] [£15.00] [£45.00]
[2012-10-30] [£65.00] [£23.46] [£09.14] [£45.00]
[2012-10-30] [£47.00] [£03.23] [£19.12] [£45.00]
​

Where the columns [Hotel], [Travel], [Food] and [Honorarium] are all fields from ONE table (tblPaymentElements) where the PaymentID is the same.

So the PaymentElements table might have:

PersonID, PaymentID, PaymentAmount, ElementType
0000001, 0000001, 55.00, 1
0000001, 0000001, 11.56, 2
0000001, 0000001, 15.00, 3
0000001, 0000001, 45.00, 4
0000001, 0000002, 65.00, 1
0000001, 0000002, 23.46, 2
0000001, 0000002, 9.14, 3
0000001, 0000002, 45.00, 4
etc.
​

Which would be linked to the PaymentID containing info that does not need to be stored in the Elements table (such as payment date and payment type).

So I'd like the sub-form to show - in one row - the four fields from the SAME table where the PaymentID is the same.

Later I might add a new control to the sub-form for the 5th field.

I'm thinking that somehow I need to create a query based on the SAME table that produces an UPDATABLE field for each of the PaymentElements where the PaymentID = n

...or perhaps I need to have sub-forms within my sub-form linking PaymentID where ElementType = n

...or something!

Does that make sense?

Simon

 

[simonpickering]

PS I love what you did in the VBA to make the comboboxes automatically dropdown when the user tabs in to them:

Private Sub ComboNameHere_GotFocus()
     Me.PaymentMethod.Dropdown
End Sub

Really good for keyboard users