I have a Table of People with an ID number as primary key and a Form (People) which shows everything. I have a Dashboard type Form (Dashboard) with a list box containing the first and surnames and the record number sorted by name. I want to open the Form (People) when an item from the list box is clicked, and I want it to open at the specific record set by ID. As yet, I cannot see how to pass the ID - or even find out what it is in a Macro.
I would prefer the following:
Listbox will save the “ID” of table “People” in a table (might be named “Filter”, ID is boolean here, field peopleID is used).
Form “People” has it’s source as a query:
SELECT * FROM "People"
WHERE "ID" = COALESCE((SELECT "peopleID" FROM "Filter" WHERE "ID" = True), "ID")
The form would show all rows of “People” if nothing has been chosen. It will show only the row with the special “ID” if it has been chosen.
This would work in a form directly (2 Mainforms, 1 Button to refresh the form for “People”).
For opening another form document automatically you will need a little bit macro code.
Thanks, RobertG
As usual, I have explained myself poorly. I have a large Form (frmPersons) which contains a MainForm and several selectable sub-forms. The MainForm of frmPersons is bound to a table (Persons) and each sub-form bound to an associated Table which is tied to the Persons Table by ID, the Persons Table’s Primary Key. Each sub-form is bound to its own Table, and the information presented in a Table Control. This all runs fine using Buttons calling macros to make all sub-form contents invisible except the chosen one.
Now I want to call that frmPersons Form from a Dashboard Form which has a List Box bound to a Query putting the Persons in alphabet order. The Query provides the List Box with two Columns - Name and ID. When the user selects a Record - line in the List Box - I want to call the frmPersons Form and have it Open at the Record indicated by the ID. With the user still able to move back and forth in the Persons Table.
You could solve this in 2 ways:
- Open the form and setting a filter to “ID” of table “Persons”. But you have to deactivate the filter when moving to other rows of the table.
- Getting the right row of the table “Persons” through the listbox. This will depend on the sorting of the form and also on the used database. Here the code for the listbox, if table is sorted by ID:
SELECT "a"."Name",
(SELECT COUNT("ID") WHERE "ID" <= "a"."ID") AS "RowNr"
FROM "Persons" AS "a"
This will work in every database. You will get the row number and could set the form to this absolute number.