[Solved] Single field lookup populating other fields

[mtmccain]

I know how to make a combo box or a list box. But what I am wanting to do is look up from an ID list field and the information that goes with that ID field with is a "last name" field and a "First name" field automatically fill in on the appropriate fields of another table on a form (these are 3 separate fields. Right now I am using a combo field for each of these and I have to look each one up separately. Is there a way to do this? Thanks in advance. Mike.

Title Edited. A descriptive title for posts helps others who are searching for solutions and increases the chances of a reply (Hagar, Moderator).

[RPG]

Hello

I think make a query where you have all the data what you need. Add also the ID in the query as a separated field. Use this query in a listbox.

Code: Select all

Select
  "last name" ||  ' ' ||  "First name"  as "FullName",
  "ID" 
from "yourtable"

See for more information here.

You can use the list box for showing and input data.

Romke

Romke

[DACM]

The part about "posting" (storing) each of the 3 fields to "another" table as "separate" fields is a problem. This can be done without macros but it's not very user-friendly compared to a macro-driven form.

I was playing with the non-macro method recently using a rather flat-file database table (forgive me).
Example: 'InvoiceWithoutMacros.odb'

Some explanation: Without macros this is generally done with a "Dummy Table" which is limited to a single record which is used to store the current selection of a Listbox. The Listbox, itself, is populated from a Source Table. The Listbox's Bound field mechanism is used to save the user-selection to the Form's Dummy Table, normally as an ID field. So in your case, we use SQL to create a Display field and a Bound field for the Listbox from a Source Table, just like Romke outlined above. In this case, "FullName" is displayed while the associated "ID" is stored to the Dummy Table.

Once the user-selection is stored in the Dummy Table, it can be used to filter (SQL 'where' clause) additional Listboxes. We use Listboxes because it's one of only a couple of Base Form-Controls that can pull data from a Source Table while saving to a Target Table using the Bound-field storage-mechanism. This is similar to your idea, except in this case, each additional Listbox will have a single selection available (due to the SQL filtering by the ID stored in the Dummy Table). These additional Listboxes will require a Sub/Form because they won't be used to store data to the Dummy Table, but to a separate and final Target Table. In this way, with multiple Listboxes, we can transfer any number of fields from the Source Table record to a record in the Target Table.

This a work in progress for me, so I expect there's plenty of room for improvement.

Some commentary: This non-macro approach is a serious hack but I am impressed with the flexibility. This can actually be used to create invoices, which I would have assumed could only be done with macros in Base. This also exposes some weaknesses in the various Base controls which, if enhanced slightly, could be much more useful. For instance, a Listbox would be much more useful if it offered multiple Bound-fields and corresponding target-Data fields -- perhaps like the multiple link fields in Form/Subform relationships. This would virtually eliminate the clunky nature of this non-macro approach, as it would eliminate the Dummy Table and all additional Listboxes used to implement the solution.

Do a search for "invoice" or "dummy table" for more information and examples.

[mtmccain]

Thanks use a query as a work around.