BASE: Subform with flexible relationship to form

[CEAuke]

Hi guys

I have a form and subform where the subform’s data is created BEFORE the main form.
I guess in the normal flow, the parent record is created first and then the children. E.g. invoice header first and then invoice lines.

However, I want to build a form where you select which invoices you want to pay. So I’m thinking that the parent form should be payments and in the child form it should show all the invoices that’s unpaid. Once you select the invoices and the payment is created, the form should in future only show you the invoices paid by that payment.

so my invoice table will have a payment_id field.
In the beginning the subform should show where payment_id is null. Once payment status=saved/paid or something the logic of the subform should change to show where payment_id = main form payment ID.

I dont know how to modify my subform to implement this. Should I base it on SQL query? How do I pull in the value of the main form into the SQL statement to get the payment_id?

Is my model wrong?
Maybe I can start with a form with only listing the invoices and checkboxes against them and when you check some and press a button, a macro will create a payment record and update all the ticket invoices. But how do I page through selected records in a macro?

[frofa]

Hello CEAuke:

Assuming these things:

1. you want to allow the payment of one or more invoices on a single payment (i.e. payment table rows relate to invoice table rows 1:n)

2. the invoice records must exist before a payment on them can be made

3. you want your form to show an editable list of unpaid invoices

I envisage a form with subform that allows you to create a new payment record first, then you’d simply add the payment ID (number) as the linking (foreign) key to whichever rows you choose in the unpaid invoices list of the subform (each record is saved when clicking on the next). In theory, when you refresh the form, the newly-paid invoice rows should ‘disappear’ from the listing (as it is filtered to show only invoices with a null payment id FK).

As an ‘extra’, you might also want to setup a second subform showing a filtered list of invoices, so that when you select any given payment from the list of existing payments (of the payments table) in the main form, that filtered list will show the associated invoices in the subform. You could probably use buttons to save records and refresh the form (or macros to make things more seamless).

[CEAuke]

Hi. The comment section wont allow a long enough reply, so I’m writing an answer in response of your proposal

[CEAuke]

Thanks. Your idea worked. Two subforms did the trick as I explained the longer answer section that I replied to earlier.

[frofa]

Hi again: There is a very nice example database for class enrollments HERE that shows how to use 2 lists on a form with buttons to move items from one list to another (using a macro). It looks very similar to what you are trying to achieve.

[CEAuke]

Hi Frofa

Thanks for the thorough reply!
I think I see what you’re saying. This is almost like a field chooser form. You have two lists (A+B, in subforms) when you select a row in list A and press the button to add, it removes from the A subform and adds it to the B subform. And vice versa.

So subform A (unassigned invoices) will be a SQL query with a where condition where payment_id is null.
Subform B (the chosen invoices) will have a a table query joined to the payment.
I just have to figure out what to put in the macro. Something like update current subform A record to add the payment ID and refresh subform A.
I’ll give it a shot and let you know how I get by.