Dependent Listbox - Simple Example

[Kennaway]

Hi! I'm looking for a simply example of a dependent/cascading listbox in a form, and need a hand finishing.

Background
The example is a financial ledger, where each entry has a Category and Subcategory (for example: Income - Customer1, or Living - Gasoline). The list of Categories is only about 8 items long - no issue generating a listbox for this selection. But the Subcategory listbox should depend on the Category selected.

The database currently has 3 tables - Category, Subcategory, and Ledger. Category is organized by two columns: CatID and Category. Subcategory is organized by three columns: SubID, Category, and Subcategory, where Category is an integer value from CatID. To keep the example simple, Ledger has only four columns: LdgID, Amount, Category, and Subcategory, where Category and Subcategory are also integer values from their primary keys.

In the Category listbox I am not having any issue having the listbox display from the Category column in the Category table, and returning the integer value, with the SQL statement in the listbox -> Data -> List Content property:

SELECT "Category", "CatID" FROM "Category"

The Issue
The Subcategory listbox is where I need help. I've been able to limit the Subcategory results to the Category listbox selection in the Ledger table by LdgID = 0 (i.e. it works for the FIRST entry, but the rest still depend on the first). With the following SQL statement in the listbox -> Data -> List Content property:

SELECT DISTINCT "Subcategory", "SubID" from "Subcategory" where "Category" = ( select "Category" from "Ledger" where "LdgID" = '0')

The Question
I want to direct it to the current row, for a new row entry, or to modify the current entry. Is there a simple statement to direct LdgID to equal 'current row', or lastrow + 1 in the event of a new entry, or some other solution?

I feel like this should be a simple solution, and I'm just missing the last bit - however, from the looking around I've done so far, I haven't found a straightforward example. Maybe this pushes the limit of Base without incorporating macros? I'm also finding the limitation that I need to create a 'Refresh' button/macro in order to have the Subcategory listbox update.

I'm looking for a simple solution that makes this form completion fairly seemless / painless for the user. Suggestions?

[Villeroy]

No simple solution. viewtopic.php?f=83&t=46470