Ask Your Question
0

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

asked 2013-09-07 16:31:39 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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 ... (more)

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-11-08 21:10:59.903706

Comments

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 ...(more)

gregfordyce gravatar imagegregfordyce ( 2013-09-08 21:13:28 +0200 )edit

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 gravatar imagegregfordyce ( 2013-09-09 23:06:55 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2013-09-16 13:59:54 +0200

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.

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2013-09-07 16:31:39 +0200

Seen: 3,304 times

Last updated: Sep 16 '13