Populating combo box based on selection in another combo box

[kearnguy]

I have two combo boxes on a form.

The first is ClassCategory. The second is ClassName.

I want ClassName to only show data corresponding to the selection in ClassCategory.

Example:

- ClassCategory has available: Art, Science, and Math
- User selects "Science"
- ClassName shows: Biology and Chemistry
- User goes back to ClassCategory and selects "Art"
- ClassName now shows: Painting, Sculpture, and Crafts

How do I do this? I now it probably is based on a query, but I don't know where to begin.

Thanks

[Voobase]

I.m not sure how easy it is to do with combobox's but an example on how to use listbox's this way using macros can be found here:

http://www.oooforum.org/forum/viewtopic.phtml?t=57424

The macros if importance in the posting are:

onStatusChange_STKOUT_Category( oEv as object ) and

sub limitItems( aDataForm as variant, aCategory as string )

Cheers

Voo

[kearnguy]

Thanks Voobase. I will play with this, but I am a total noob to database programming. I can read the code and understand the majority of what it is doing, but I have no clue where this code goes.

Is there a sample database that I can download and reverse engineer it to learn how to accomplish what I want?

PS You are right, I could do this with list boxes instead of combo boxes.

[Voobase]

Hi again,

Yes Base can have a bit of a learning curve attached to it. It wasn't too long ago I was at total noob status too. I will try to list the important things you need to learn to be able to comfortably design your own databases in Base:


1/ When creating your tables it is best to have an auto incrementing primary key on every one of your tables. Create your tables in design view (not Wizard) and put in your various fields and field types. The best way to get the auto incrementing primary key is to then press save and it will prompt you for a primary key. Select yes and once it is created you must then click on the primark key ID field and select "auto increment" down the bottom. Most people will be after a one to many relationship between there tables so don't forget to create an extra field in your second table of type "integer" that you label something like "Foreign_Key" (not auto incrementing)

2/ Using the form wizard is a really good way to get a basic database going that will have a 1 to many type relationship between two tables. Just follow it through and when it gets to subform you can ask the wizard to link the "ID" field in your master form to the "foreign key" of your subform. It is good to display these fields in your form while designing, so that you can see what is going on.

3/ Once created, to change things in your form you need to launch the form in design mode. To do this right click on the form and select edit. When you want to toggle between design and normal modes there is a button on your "Form Design" toolbar for this.

4/ There is also a button on the "Form design" toolbar for launching the "Form Navigator" This is increadably handy as it shows you the structure of your form in a tree like view. You can also select directly the individual controls and forms to examine their properties (right click). This gets you past the noob hurdle of not being able to see the properties of things because the form wizard automatically groups a control with its title. The other way around this is to right click on your fields and controls and select "ungroup".

5/ When you have found the properties of your forms and controls you will notice there are three tabs up the top. The "general" tab includes what the name of the control is (you will need to know this when using macros). The "data" tab shows you what data field the control is working with and the "events" tab is for choosing what action will trigger a macro.

6/ Make sure on your "Form Control" toolbar you have the "Wizards" button pressed in. This will launch a wizard when ever you drop something like a "listbox" or "tablecontrol" on your form. Make sure you have selected a field in either your mainform or subform when drawing a new control on your form so it ends up in the correct form. This can be checked with the "Form Navigator"

7/ With the fields in your form it is good to right click on them and choose "anchor to page". This will stop them from moving around on you too much.

8/You are now ready to think about macros. To edit your macros go to Tools>Macros>Organise Macros>OpenOffice.org basic or just press ALT F11. You can store your macros in "standard" or create your own folder by choosing Organizer>new. Click on the "module 1" macro file and choose edit. Put a new macro in... like

[Voobase]

continued...

I Should also include a little more about ListBox's and Combobox's here...

Listbox's and combobox's are best used getting their "List Source" from separate tables. A combobox differs from a listbox in that, rather than having to select something from a list, it allows you to type in your own entry if required. A limitation of a combobox is that it only has one field that it can use to display and "put somewhere else". A listbox however also has something called a "bound field". This is an extra field and is usually selected as the primary key from the table that supplies the list source (when you create the listbox). In the data properties it just shows a "1" no matter what you have selected.

By having a bound field, the listbox can display one thing whilst storing the related primary key somewhere else. This feature is what enables us to create a situation where one listbox can narrow the field of another. If you look at the data properties of a listbox you will see a SQL statement that is responsible for deciding what is in the list. Usually something like:

[kearnguy]

Awesome info... I am just now sitting down to read through it all. I appreciate the time you took to type it all out. Hopefully I will be able to get my solution from what I'll learn. Thanks again.

[Voobase]

No worries,

Give a yell if you get lost doing the listbox's.

Cheers

Voo

[kearnguy]

I guess I just don't get the basics somewhere. Forget auto-filtering of listbox data, I can't even get a listbox populated!

Here is my setup:

Sessions (Table)
- SessionID
- SessionName

ClassCategories (Table)
- CategoryID
- CategoryName

Classes (Table)
- ClassID
- SessionName
- CategoryName
- ClassName


Now I build a form called "AddNewClass" to populate the Classes table. I want a listbox to with SessionName as its source and a listbox with CategoryName as its source. All I could get was the integers from each of those tables to show up...

What am I doing wrong?

[Voobase]

Hi again,

No, I think you are on the right track. A list box will display one thing and put another field from the listbox table's row somewhere else. Having the integers (Primary Key) show up in the table the listbox is attached to is a good thing. This way it is copying the Primary Key that identifies the particular row of data that holds your category name (or class name) and copying it into the other table where it is then known as a Foreign Key. You must make sure however that it ends up in an integer field that is called something along the lines of Category_FK or Class_FK. The advantage of doing it this way is that you are guaranteed that the Primary Key will always be unique where a class name may not always be.

The only downside of doing it this way is that you will need a macro to copy the actual Category or Class name across to the table the listbox is sitting in (your classes table), unless you use a query to get that name for you. Doing it this way should be OK for you because you will need a macro to make one listbox limit the items in the other. You can just incorporate copying the name across into those macros by adding a couple of extra lines of code.

....OK What I have done is quickly put together an example. I had already done something similar so quickly adapted it. Its a bit rough but I might be able to tidy it up a bit on the weekend. I have put it on mediafire.com. You will need to unzip the file then..

http://www.mediafire.com/?zndlcyafyzg
(EDIT: I have updated this example, it's simpler and works better. See next page of this thread)

Make sure you register the db in Open office (see point 10 from before) and import the script file which are the Macros.

The example has one extra table to what you have, the sesXREF table. Macro's are fired from the "on status changed" event of both listbox's and there is a Macro attached to the MainForm's "BeforeRecordChange" event.

There is also another form for populating the listbox tables with the correct categories and classes.

I'll explain more later, but have a play with it and see how you go.


Cheers

Voo

[kearnguy]

Amazing stuff! I really appreciate your help. I am stumbling around this sample and getting some stuff to do what I want, and confused by other stuff...but I am learning. The macro is way beyond me in terms of knowing the Base framework (I'm a web dev - html, css, actionScript, JS, etc.), but I am starting to see how things work together.

I will probably have more questions as I keep playing, but feel free to chime in whenever you can if you want to clarify, explain, or clean up code.

Again, thank you thank you thank you for your willingness to help a noob who bit off more than he could chew. I'll keep you posted...

[JDHeinzmann]

Regarding the Bound field in a listbox,

[Voobase]

Yeah thanks JD,

I was being a bit slack by not fully explaining that bit. What I should have said was that... it just displays a 1 no matter which field you have selected as your extra field.

I am wondering though, in what situation you would need to return more than this one extra field when using a listbox as it can only have a single bound field. All I can think of is, if you wanted to change the bound field "on the fly" by using a macro to change that property rather than having to change the Select statement and reload the list. Spose that is a good enough reason.

Cheers

Voo

[Voobase]

Hi Kearnguy,

No worries. Happy to help.

I couldn't tidy that example this weekend as I spent most of Saturday chasing a bug which was stopping my main form from opening in the project I am working on. It even effected my backups and a reload of Open Office didn't help. Really had me going. What it ended up being was the Open Office "Quick starter" (I'm fairly sure). Its turned off now!

Good that you are learning. If you play with the macros by 'commenting' out bits to see what they do, by what they don't do, is always a good way of reverse engineering things. There is a lot in the macro that is just hacks to get it to behave the way I want it to.

An example of this is, after the second listbox has had its items limited, I have to load a non limited "select" statement back into the listbox, ready for when you change to another row in the subform. If I didn't do this then as you changed rows the listbox would show a blank value and also blank out any "bound values" that had already been put in the subform by that second listbox.

The "BeforeRecordAction" macro looks complicated but is just performing a "refresh" of the listbox. The "var_refresh" global variable is there so the macro only does a "refresh" when it is needed to, i.e after the SQL statement has been sent to the listbox. The refresh is needed to actually put the new list in the listbox list, but takes a fraction of a second, so I only have it occur just after listbox values have changed something and you then wish to change rows of the subform, not for when just browsing through the subform records which also calls the "BeforeRecordAction" event. The rest of this macro is there due to needing to use this particular event without an error occuring. There is a document that explains why, which is called "Forms and Dialogs" by C. Benitez, if you see P18.
http://www.geocities.com/rbenitez22/

Also in the macro is shown two different methods to copy the text from the listbox selection to another field in the subform's grid. The second way uses a "prepared statement" which would copy the value directly into the table the subform is based on using SQL. The first way is simpler and copy's the text into a control (text box or grid field) in the subform and then has the form do the updating of the table later when it does an "UpdateRow".

Anyway, if you have any questions on the listbox example happy to help here. I guess more general or other questions you might want to start a new thread.

I'll also try to tidy the example up a bit more when I get a chance with some clearer comments etc.

Cheers

Voo

[JDHeinzmann]

Hi, Voo,

[Voobase]

Thanks JD,