Working with subforms in base - filling table value with field from related table

[gregfordyce]

I am new to Base and database’s in general. I am setting up a database to track payments made by club members in a bank account. Once a month I get a statement with all the payments made that month. Club members pay monthly and some payments will cover more than one member. (This is a scout group so siblings are possible).

I have set up 4 tables. I am using the HSQL embedded database engine in 4.0 on Ubuntu 12.04

• tbl_Name (NameID, Name, BankID) --Name is a text field, BankID is a foreign key
• tbl_BankRef(BankID,BankRef) --BankRef is a text field as per bank statements
• tbl_StandingOrder(PaymentID, Date, Amount, BankID) --Amount is payment amount, BankID is a foreign key
• tbl_TermPayment (PaymentID, NameID, Amount) --PaymentID and NameID are this tables primary key and foreign key

The first field in the first three tables are auto incrementing primary keys, the last table uses PaymentID and NameID as its primary key. I have set up relationships between the tables as appropriate.

My problem is designing a form to allow me to easily input the payment data. I have used the form wizard to set up a form for the tbl_StandingOrder that has a subform of tbl_TermPayment. I have then added a list box to the main form (tbl_StandingOrder) that gives me the BankRef field and fills in the BankID field in the form. What I can’t figure out is how to get the subform to give me a list of ‘tbl_Name.Name’ filtered to the BankID (and in place of tbl_TermPayment.NameID) in the main form. Maybe my subform should be a query instead of a table? Any help greatly appreciated.

Edit: I’ve made a query q_Name that is the same as tbl_Name only sorted first by BankID (ascending) and then by Name (ascending). I know this table/query is the key to my problem but haven’t yet figured out how to use it in my form. I need to filter and display all Names in the subform that have a BankID from the main form.

EDIT 2: The q_Name query was a dead end, but I’ve made some progress. I’ve linked the master fields in the main form “PaymentID” and “BankID” with the slave fields “PaymentID” and “NameID”. I didn’t realize I could do this, but it makes sense with the relationships I setup.

Now the problem is that NameID is a 1 to many relationship with BankID (1 BankID, many NameID) but the table in the subform is only giving me one nameID not all NameIDs with a matching BankID from the main form. How can I fix this?

EDIT 3: Turns out the idea in edit 2 didn’t work as I thought, it was giving me a Name ID with the same value as BankID which was completly useless.

I will start a new topic with and try to refrase the question.

[gregfordyce]

I’ve tried setting up a table control in a subform using this query but still can’t make this work.

SELECT "tbl_TermPayment"."PaymentID", "tbl_TermPayment"."NameID", "tbl_Name"."BankID", "tbl_Name"."Name", "tbl_TermPayment"."TermPayment" FROM "tbl_TermPayment", "tbl_Name" WHERE "tbl_TermPayment"."NameID" = "tbl_Name"."NameID" ORDER BY "tbl_TermPayment"."PaymentID" ASC, "tbl_Name"."BankID" ASC, "tbl_Name"."Name" ASC

This query has all the fields but doesn’t do what I want. I am trying to get the subform (tbl_TermPayment) to display all names that match the ‘BankID’ field in the “tbl_StandingOrder” table that is in the main form. So the “NameID” field in the table should be replaced with the relevant “Name” field from the “tbl_Name” filtering on “BankID”.

What is the best way to achieve this? Maybe I need another subform with “tbl_Name”? If so what should be the heiracy of these 2 subforms.

[gregfordyce]

This query is close to what I need SELECT "BankID", "Name", "NameID" FROM "tbl_Name" WHERE "BankID" = 1 ORDER BY "Name" ASC but instead of WHERE “BankID” = 1 I need something like WHERE “BankID” = “tbl_StandingOrder”.“BankID”. Is this syntax correct? How would I implement this in my subform so that it points to the active row in the main form?

[gregfordyce]

O.K. so I’ve now got a working database that does what I needed it to do. That’s the good news. The bad news is that I didn’t use LibreOffice. I found SailformsPro for Android and easily set up my database on a tablet.

So it’s not a very good answer, but this was my solution to this problem.

I also found lots of information on the OpenOffice forums so if you must use Base head over there, lots more info and example databases.