Contents of one list box dependent upon another's selection?

[Decker87]

I can confirm that this works the same in versions 2.0-Novell and 2.2. I e-mailed drew jensen, perhaps he will help me out.

[TessaES]

Maybe you should reload the form after updating the SQL statement for the listbox

[Decker87]

It sort of works. I updated my sql statement to read

[mercurion]

is the download still available?

i would like to study it in detail .... thanks

[DrewJensen]

Hi guys,

Sorry I have been so tied up with other things..

I need to read over this and see if I can help - two more things ahead of this however...but I'll get to it later today..

Again sorr for being so slow...all though the forums speed isn't exactly helping at the moment.
_________________
Blog - http://baseanswers.spaces.live.com/

[Andrew White]

Hi Drew

Thanks very much for your original reply to Decker87's request for help concerning making the available entries in one list box dependent on the entry selected, first, in another list box. I am currently working on a form that requires just such a functionality.

In your original post you mentioned that you were willing to explain the code line-by-line if Decker87 requested it:

"Again, if you want to go over the specifics of this code snippet - even if it is line by line, just let me know and we will do so."

Decker87 did not request it but I would greatly appreciate reading such an explanation if your offer still stands.

I have gone through/studied the OOo 2.3 Help Section on "Macros and Programming", read quite a few posts on related topics (on the OOoForum and elsewhere), and, as a result, I now understand the very basics of BASIC (e.g. the use and meaning of “sub”, “variable”, “function” and “DIM”). However, my lack of understanding still centers on some of the specifics of the coding you used in your post on "dependent list boxes" and the significance of certain commands.

Your coding looked very simple and easy to follow, and I would really like to understand how to use it, as simply, for my forms and data. I'm very new to the terms and phases used in explaining technical computer/programming related topics (as used by those more familiar to the computer science and programming fields) and would appreciate it a lot if you used as descriptive and simple plain English as possible. I think the latter may also prove useful to other "newbies" to programming and the OOoForum in future, if you have the time to reply to this request and use it therein. Thanks.

Below are my specific questions for the first set of coding:

REM *****
REM ***** onStatusChange_STKOUT_Category( oEv as object )
REM *****
REM ***** the event handler for the category list box
REM *****
REM ***** I broke this out as a seperate handler from
REM ***** the other controls because it must
REM ***** be the first thing entered as it
REM ***** is used to populate the itemcode list
REM *****
sub onStatusChange_STKOUT_Category( oEv as object )

REM *****
REM ***** if the user selected a category
REM ***** then update the itemcode listbox
REM ***** and enable the data entry controls
REM *****
if oEv.Source.Model.CurrentValue <> "" then
limitItems( oEv.Source.Model.Parent, oEv.Source.Model.CurrentValue )
enable_STKIN_STKOUT_Controls( oEv.Source.Model.Parent, True )
else
REM *****
REM ***** no category so disable
REM ***** everything else
REM *****
enable_STKIN_STKOUT_Controls( oEv.Source.Model.Parent, False )
end if

end sub

1) Concerning the opening "sub" code:

i) Is "onStatusChange" recognised by OOoBase 2.3 and is there a list of the commands that refer to other such control box "Events". If so, where would I find it?
ii) What does "_STKOUT_Category" refer to? To me, it looks like it refers to a field/column called "Category" in a table called "_STKOUT_". Please can you explain this term in broken up "bits" and also mention the significance of the underscores (_) if there is one.
iii) What is the significance of " ( oEv as object ) "? What does that do?

2) What do the individual parts of "if oEv.Source.Model.CurrentValue <> "" " mean and what is their significance and the significance of using the full stops (periods) between the words? Do "Source" and "Model" change with each and every database? ie are they database dependent terms that would change according to the terms/names used in a specific database?

3) The same questions as those written in 2 (above) are relevant to
"then limitItems( oEv.Source.Model.Parent, oEv.Source.Model.CurrentValue )" plus, what does "(oEv.Source.Model.Parent and OEv.Source.Model.CurrentValue) mean?

4) With "enable_STKIN_STKOUT_Controls( oEv.Source.Model.Parent, True )
else
enable_STKIN_STKOUT_Controls( oEv.Source.Model.Parent, False ) "

What does "_STKIN_STKOUT_Controls" refer to? please could you break this up and explain it bit-by-bit.

5) The code just before "End Sub" reads "End if" but nothing further was given. Was that correct. Just "end if"?

6) Further in the same post (and different set of coding), when you referred to "oList", "oModel", "aDataform" and "aCategory" were you writing generically or are those terms meant to be replaced with terms/names specific to a certain database when the Macro is written for use in a specific database?

7) Finally, what do "passed", "pass in" and "passed over" mean?

Thanks very much for reading all of this, and I will greatly appreciate your help if and when you can offer it.

If it's more time efficient for you, please can you simply supply the coding for a generic example using terms named "Field1" and "Field2" (in tables named "TableA" and TableB, respectively), where entries in "Field2" are dependent on those selected in "Field1".

For me, I have two tables, the first named "ProjectNames" and the second named "TblSites". The two tables are linked by the Primary Key of the "ProjectNames" table named "ProjectID" in a 1-to-many relationship (one "ProjectID" linked to possibly more than one row in the “TblSites” table) The "TblSites" tables contains the field "ProjectID" as a foreign key, and also contains its own Primary Key named "SiteID" (automatically inserted numbers) and a field named "Mainplace" (the name of the site written in English letters).

Table "ProjectNames" will be the first table to be selected and "TblSites" the second to be selected. At the moment I have two functional list boxes in one form that display the full list of "ProjectName"s (associated with the "ProjectID" primary key values) and the full list of "Mainplace"s (site names associated with the "SiteID" primary key values), respectively.

The SQL statements for those list boxes, currently, are:

(SELECT "ProjectName" , "ProjectID" FROM "TblProjNames" ORDER BY "ProjectName" ASC) for creating a full list of project names in ascending order (minus the brackets/parentheses), and

(SELECT "MainPlace" , "SiteID" FROM "TblSites" ORDER BY "MainPlace" ASC) for creating a full list of mainplaces (site names) in acsending order (minus the brackets/parentheses)

All I want to do is be able to select one "ProjectName" from the first list box and have the second list box show only the "SiteName"s of the sites linked to that "ProjectName".

Yours sincerely
Andrew

[Leo Demarce]

I have read through this post and believe that this is where my question should be asked. I'm a newby to the system and am trying to do a relatively simple task.

In a form, I am using a combo box to show a list from a table. I then want to use the result of the selection to provide the filter for the 2nd selection. The first table is Categories, the 2nd is SubCategories.

Categories
CategoryID Category
1 Business
2 Personal

SubCategories
CatogoryID SubCatID Category
1 10 Client
1 20 Agent
2 10 Friend
2 20 Family

The first combo box is called “TypeComboBox” and populates field “Type” in the table “Address” by using the following SQL function in order to list Business or Personal
SELECT "Category" FROM "Categories" order by "CategoryID"

Now the 2nd combo box
SELECT "Category" FROM "SubCategories" where ??????????
I want to tell it to look at the value in the first combo box, then relate it to the CategoryID and filter the list by that CategoryID. That is, if I select Personal in the first Combo Box I should only see Friend and Family in the second Combo Box.

What would be the best way to do this?
Thanks for any help
Leo

[DrewJensen]

First - I apologize I totally missed Andrew Whites post in November - rather a busy time then and am not on this forum as much.

So - long ago as it was - it should work for the current question from Leo also.

I'll put that generic example up for download - not on this forum, as one cannot attach files but i will put a link back.

I'm finishing up one item now and since I have no life any more will get this up tonight
_________________
Blog - http://baseanswers.spaces.live.com/

[MercuryCrest]

BUMP!

Sorry to bump this thread but I want to make sure it gets resolved.

I'm having exactly the same problem as Leo. I had originally posted this as a new topic as I couldn't find this one but now that I'm here.....still nothing.

Anyways, just to keep this at the forefront of people's minds....

[Yatesbr]

I have a feeling this is exactly what I'm looking for, but I'm still a lot newer to databases or open office in general it seems compared to anyone else.

I currently have database that is needing a function where i can take the values of 2 items selected in a field and generate a third option based on the two previous.

The two options are Type selectors (one is SpeciesType, the other GroupType)

From these two options, once made, I'd like to get a list of all people who have these two categories selected.

There is a defined list for species, group, and people, and they do have their relations and IDs set up

Does this make sense or am I trying too much?