[Solved] Macros for listbox contents and new form buttons

[Hezza1506]

Hi,

I have been building a database for a few weeks now, with a lot of help from various people on this site and the alternative forum. The whole thing is running perfectly now, and there are just a couple more things that I am trying to sort out that will make operation cleaner and more user-friendly.

I have two functions that I have been trying to figure out since the weekend but failing miserably. I believe that both need macros to make them work, although it is possible that one could be done using subforms, but that isn't working for me so far. I get lost every time I start looking at macros and even though I've found many posts taking people through creation of macros, I just get completely lost - some of the posts are even showing people how to do what I want to do, I just don't understand how they're doing it! A lot of the time people are suggesting installing additional programs, but I really don't want to have to do that - my laptop is finicky at the best of times and I'd rather not risk adding new items that could screw it up more.

The two functions that I am looking for are a button in one data entry form that opens another of my data entry forms, and something that makes a selection in one listbox update the choices available from a second listbox. As I said before, both of these are discussed on other posts, but trust me - I'm lost.

The button is to be sited on a form called "MainEntryForm-ShowInfo" and should open another of my forms: "MainEntryForm-CatDetails" - that's all it needs to do: there is no requirement for it to fill in any fields or anything, just open the form.

The listboxes are on a mainform called "MainEntryForm-ShowResult". The mainform is tied to a table called "ShowResult", which has four fields:
ClassID - foreign key from the "Classes" table
PenID - foreign key from the "PenInfo" table
Place
Report
The two foreign keys also form a compound primary key.

At present there are two listboxes, one of which passes back the "ClassID" and the other the "PenID", so that the place and report fields can be filled in related to the correct class and pen. What I want is for the options available in the "PenID" listbox to reduce when an option is selected in the "ClassID" listbox. Both the "Classes" table and the "PenInfo" table contain a foreign key "ShowID", which is the primary key in a table called "ShowInfo".

I tried creating a subform tied to the "PenInfo" table, and linked to the "ShowResult" table via the "PenID". I then moved the "PenID" listbox into it, thinking that the "PenID" listbox would then only display the items relevant to the selection made in the "ClassID" listbox. Instead, all that happened was the "PenID" listbox became greyed out and therefore could not be used.

I also tried having the subform tied to the "ShowResult" table like the mainform, and linking the two together using the "ClassID" field, but this had the same result of leaving the "PenID" listbox greyed out.

I am happy to use the subform idea if that is better than using a macro, and if it is possible.

If you are suggesting use of a macro, could you please do a step-by-step on it, because I've not even figured out yet how you build a macro in the first place!

I have attached the database to this post in case that helps anyone to help me! However, there is no point just embedding the macros into the database and re-uploading it, because my network settings won't let me download databases at the moment (that's a new addition this week, that I'm trying to get sorted). If you do use the uploaded database to help me, you'll still have to do a step-by-step on what I should be doing so that I can add the macro myself on my own copy of the database.

Sorry for duplicating other queries, but there are obviously a lot of people out there with a lot more knowledge of whatever language is used for macros etc. than I have. I've also been off ill with a virus this week and therefore concentration is not what it could/should be, but I'm too impatient to wait until I'm better and then try to figure it out!

Thanks in advance,

Hezza

[Hezza1506]

Managed to figure out how to limit the selections within one listbox using the selection made from another, but did so using mainform/subform relationships.

Still haven't a clue how to program a button to open a new form, though.

[Villeroy]

http://www.oooforum.org/forum/viewtopic ... 634#311634

And again a macro-free approach: http://www.mediafire.com/download.php?pnbmtnttzul

[Hezza1506]

Villeroy,

I've tried the code on that other post that you linked to, but I'm getting an error message, still. I've posted a new message on the bottom of that thread with the error message etc. Any chance you could take a look and see if you can tell where I'm going wrong?

Cheers

[Villeroy]

Error messages use to have meaningful messages. If you can not debug, avoid all macros until you learned some programming. Please, do not start programming with any dialect of Basic.
The code can not run in version 3.0.x. I believe it uses the new macro container for database documents in 3.1.

This is the code to open all forms. Store it under "My Macros", assign the form's name to the button's property "Additional Information". The macro reads the form name from this tag of the calling button.
The variant to load a report freezes the office on my system (Linux, OOo3.0.1 by Sun).

Code: Select all

REM  *****  BASIC  *****
Sub openFormByTag(oEv)
REM OOo3.0: Reads the form name from a calling control's tag 
	cWhat = com.sun.star.sdb.application.DatabaseObject.FORM
	oModel = oEv.Source.getModel()
	sName = oModel.Tag
	oView = oModel.Parent.Parent.Parent.Parent.getCurrentController()
	oView.loadComponent(cWhat, sName, FALSE)
End Sub

Sub openReportByTag(oEv)
REM Call this to freeze OOo 3.0.1
	cWhat = com.sun.star.sdb.application.DatabaseObject.REPORT
	oModel = oEv.Source.getModel()
	sName = oModel.Tag
	oView = oModel.Parent.Parent.Parent.Parent.getCurrentController()
	oView.loadComponent(cWhat, sName, FALSE)
End Sub

[Hezza1506]

Villeroy,

Many, many thanks for that - you've just solved the last issue that I've got! The button is now working and that means I can close off my last forum thread!

Thanks again,

Hezza