How do I populate a listbox with descriptions from another table

[taholmes160]

Hi Folks:

I am working on developing a database for use with Model Railroading. I have 2 tables that are concerned here. Table one is called TabLocoCodes and contains 3 fields
1 LocoCodeID
2 Loco Code
3 Loco Description

The second table is TabLoco - and contains 8 fields

1. Loco ID
2. Road Name
3. Road Number
4. Loco Type
5. Color
6. Controls
7. Light Return
8. Image

What I would like to do is as follows:

On the form for entering data in TabLoco (called Create Loco Card) I would like to have a list box that will display the descriptions of the different types of locomotives (TabLocoCode.Loco Description), and allow the user to pick one, but then will insert the loco code (TabLocoCode.Loco Code) into the Loco Type Field of TabLoco

Can someone please provide me a tutorial or point me to the right one, I’m having trouble locating one so far

Thanks
TIM

[Ratslinger]

Hello,

What it appears you need is a list box on the main form (containing Loco Description) and record data connected to it on a sub form (TabLoco table).

You can do this with table filtering. See this post for how that works → Filter/Search with Forms (leveraging SubForms).

Here is a sample of how it may be applied in your situation → ModelRailroading.odb

The sample differs in TabLocoCodes as I can’t see need for both LocoCodeID & LocoCode.

Another potential problem seen is in TabLoco. You have an image field. There are two ways to implement images - embedded and linked. With embedded the entire image is in the DB. Linked is just that, a link to the image. With the default DB (HSQLDB v1.8 embedded) there is a tendency to lose data when storing images (or large amounts of data). This is not present in most other DB’s including HSQLDB split. Therefore, if you are going to include images, best to do so as linked or change DB’s. See this post → How to insert a link to an odb database to display an image.

Edit 2018-04-09:

Thank you for posting the sample. Would have a harder time trying to figure out the problem without it.

You are trying to accomplish with a list box something it is not meant to do. A list box can display one piece of information and save another into a field but you are wanting to display one and save two others in two different fields. Right now you display the Name & save the ID in LocoCodeID. In addition your question wants to save the LocoCode in LocoType. Not capable with a list box unless you write macro code to do this.

Now what seems to be a better solution is to change the key in TabLocoCodes. Drop the ID & make LocoCode the primary key. It is unique and makes LocoCodeID unnecessary. The listbox then uses:

"SELECT "Loco Description", "LocoCode" FROM "TabLocoCodes""

No code needed and you still have proper links between tables (TabLocoCodes.LocoCode->TabLoco.Loco Type - a one to many relation). Of course you need to delete the old relation & set the new one.

If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.

[taholmes160]

Hi:

While it appears that this will work, it doesnt appear to do it the way that I am looking for, as I am inputting a loco to the table, it will be one of a a dozen or so types that the prototype railroad actually runs (or ran), I have a list of those types stored with their code and description in a different table, and want to call the descriptions to populate the listbox, but insert the code value into the table for use in later paperwork. Here is what I have developed so far, and it seems to be working, there are other parts of the database as well that are not yet working

OpenCard.odb

I also have the images working as suggested in the linked article

thanks
TIM

[Ratslinger]

Please use comment or amend original question if space is needed for responding. Answers are to be used to respond to original question.