Let's say that Table1 has an Integer Table2ID field that references a Primary Key Integer ID field in Table2. However, the form must allow the user to select the Table2 record using the two fields that make up an index: a Category VarChar and a Name VarChar field.
When a record can be indicated with a single field, you use a drop-down listbox to list the available field values. The selected value defines the record you need, and that value, or the value of another field in the same record, can be saved in the bound field. However, in this case the records are indicated by combining two fields.
Ideally, the form would have two drop-down listboxes, one for the Table2.Category field and one for the Table2.Name field. The combination of the selected values in these two listboxes would define the Table2.ID value for the Table1.Table2ID field.
When browsing existing records, the Table2.Category listbox selected value would be the Category value and the Table2.Name listbox selected value would be the Name value of the record indicated by the Table1.Table2ID field. When creating a new record both listbox selected values would be empty.
In either case, the drop-down content of the Table2.Category listbox would be the list of DISTINCT Category values from Table2. When a Category value is selected for a new record, or is already selected in an existing record, the drop-down content of the Table2.Name listbox would be filled with the Names from Table2 records that have that Category value. When no Category value is yet selected, the Name drop-down content would be empty (unless NULL is a valid Category value).
When a new Name value is selected, the Table2 index is complete and the Table1.Table2ID field would be set to the ID value of that Table2 record.
Unless I've missed something (always possible), this seems the only way to do this with the available form controls. However, I can find no way to configure the form controls to do this. A sub-form gridbox isn't appropriate since you only want to select one record by a multiple-field key, and not take up the form with browsing all of them. Although I can use a parameter field in a Query, I cannot seem to use one as the bound field for the Table2.Category listbox that the Table2.Name listbox SQL command can reference.
This is such a basic operation for selecting records and it should be doable without macros. Does anyone have any ideas how this might be achieved in an OOo form?
-- -------------------------------------------------------- The Snake Pit - Development www.TheSnakePitDev.com Curtis Clauson [EMAIL PROTECTED] Proprietor
"Any sufficiently over-complicated magic is indistinguishable from technology." -- Llelan D.
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
