Combo Box in a Base Subform?

[windyweather]

I have a database with 3 tables:
Character
Stones_Held
Stone_Types

I'd like to make a form to fill out the Stones_Held table.
It has the following fields:
ID - autofill unique id
Stone_ID - ID joined from Stones_Types field
Number - of stones held

Stone Types is:
ID - unique ID of the stone type
Stone_Type_Name

I have made a form with the main form from the Character table, and a sub form of the Stones_Held table - joined across Character ID. This works.
I would now like to be able to set the Stone_Type in the sub form from a combo box that is populated by the Stone_Types table.

I have looked at the data tab of the combo box and nothing seems to work. In one case I got numbers, but what I want to do is pull down the box and see the Stone_Type_Name in the combo box, but when I make a selection, the field is actually filled in with the ID of the corresponding record. To maintain Normal Form of the database in a user friendly manner. Surely this is what the Combo Box is for, right?

If you want to know the why and wherefore of the project, here's the blog entry with all the details and an example - slightly out of date.
http://www.windyweather.net/wp/2009/01/05/2moons-management-database/
{{ This question is a simplified example of the real thing. }}

Thanks much for your help,
- windy

[Voobase]

Hi there Windy,

You may find that you need to use a listbox instead. The differences between a listbox and a combobox is that a listbox can display one thing (Stone_Type) and bind a different thing (ID from Stone_Type table) into the table field you choose (Stone_ID) of the form the listbox is sitting in. A combobox can only display the same thing as to which it binds to the datafield. There is also a difference between the two as far as operation goes as with a combobox you could type into it a different word than is already in its list. This could make the choice hard between the two types of controls.

It is possible to use a little macro with a combobox to enable it to bind a separate value to the form than it is displaying. I'll try to dig up an example.

The easiest way to put these controls onto your form is to have the "wizards" button pushed in (which is towards the bottom of the "form controls" toolbar). This steps you through the creation of the list or combo box after you have drawn it onto your form in design mode. I'll have a quick look for an example of the macro you would need to turn a combobox into a listbox (sort of).........

If you really want to use a combobox, have a look at this post here (towards the bottom) where I have discussed the issue. http://www.oooforum.org/forum/viewtopic.phtml?t=72134 I'll reproduce the macro here but change it a bit. At the moment the macro uses SQL to determine the ID from what was selected in the combobox and then SQL to put it into the data table of the form the combobox is sitting in. I will add one line so that the value is copied to the rowset of the form as well. This means the value is available in the form straight away without needing to use refreshrow() to bring the data from the table to the form again. The other thing is that when a new value is written into the combobox it will not be stored in the table that supplies the combobox. I'll see if I can pen a little section that offers to save it to the table. I have tried to use your field and form names where possible however check them for accuracy with case etc. I probably won't be able to test it so let me know if it works or not.

[windyweather]

Voobase,

Thanks much. Listboxes are just fine. I've got it working very nicely. I've updated my posting here: http://www.windyweather.net/wp/2009/01/05/2moons-management-database/ with a working example.

I'll probably add a form for Rings too, since they seem to be an important loot type.

Since I"m using a spreadsheet for the subform, the wizard doesn't work. But I made a field outside the spreadsheet and put a list box in it and the wizard made the field work, then I copied the sittings into the column for the sheet and it worked very nicely.

Thanks again,
- windy

[Voobase]

Hi Windy,