OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Keep struggling with the same problem over.. and over..

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
wubs23
General User
General User


Joined: 14 Mar 2009
Posts: 6
Location: Netherlands

PostPosted: Thu Mar 19, 2009 3:42 am    Post subject: Keep struggling with the same problem over.. and over.. Reply with quote

For some reason, I keep getting the same problem over, and over... and over!

I made 2 tables.

1 called [merken]

merkID [prikey]
merk Name [name of the brand]

1 called [collectie]
collectieID [priKey]
collectieMerk [brandname of the collection]
collectieName [name of the collection]

Relations:
[Merken.MerkID] (1) ------------ (n) [Collecties.CollectieMerk]

I also made a query, both tables in there.
relation in the query: [Merken.MerkID] ---------- [Collecties.CollectieMerk]

Now I want to make a form based on this query.
In this form, there have to be 2 listboxes.
1 for brandname, (Merken.MerkName)
1 for collection name, (Collecties.CollectieName)

But I want to first choose from the brands, and then the second listbox to ONLY display the collections of THAT brand.

For some reason, when I make that, either I cannot put anything in, just go from record to record, or it shows all brands and all collections from all brands.

How can I change this? I use OOo Base 3.

I have no idea what macros are, so if I have to do something with macros or code, please tell me what to click when, and what to type where.

I already posted this question a couple of times, and every time people post URL's to other topics. This does not work, since I do not the code that they post there.
Please just give me an answer instead of a link: i have already read those posts but do not understand the,m

Thank you.
_________________
Rick Wubs
Front Office Assistant, Lorka B.V.
www.primalaminaat.nl

rick.wubs@gmail.com
info@primalaminaat.nl
Back to top
View user's profile Send private message Visit poster's website MSN Messenger
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Thu Mar 19, 2009 4:26 am    Post subject: Reply with quote

Hello

I think there is no easy solution in the way you want. Only with macro's

I point you to a solution of Villeroy. Villeroy gives an other idea.
I did wrote: use forms. If you make a form and select a brand then in the next form you can display the things you need.

Forms are basic for OOo-base.

Villeroy and Voobase have written a lot of post where they explain the basics in a way I can not, so try to find post of them.

The best advice on this moment is starting with reading several post on this forum or the other forum. I do read it for a year and the question do not change a lot.
The problem for people when starting with OOo-base is understanding:
a) OOo-base has limited capacity
b) How to use the forms in base

And maybe as first lesson in the basic. You must understand that the word form is use for two different things.
a) writerdocument
b) what you see in the form navigator.

The form navigator is an other tool then the navigator.
In the form navigator we talk about mainforms and subforms. One form as writer document can have several mainforms (form navigator). Every mainform can have several subforms. See it as a directory tree.

I hope this helps you , and I can understand it is difficult.
Romke
Back to top
View user's profile Send private message
wubs23
General User
General User


Joined: 14 Mar 2009
Posts: 6
Location: Netherlands

PostPosted: Thu Mar 19, 2009 4:33 am    Post subject: Reply with quote

Thank you for your reply.

I have read most of the posts here, as well as the OOo Manual.

But there are 2 problems that are not explained.

1 is that if i create a form using the form wizard, and i select a query as data source,
for some reason I can not enter data in that newly created form.
I can only slide through the records of the tables behind the query.

Also, there must be a way to get only certain values in the 2nd listbox.
If this has to be done with macro's, so be it.
But could somebody then please explain me what to enter where?
because the other posts do tell what to enter, but not where and WHY.

And if that isnt explained, i cannot modify the macros for my own needs, and i have to ask the same question every time.

Thank you for your reply though; you are at least a person that reads what i want, and then tells me what to do and WHY.

and that WHY part is just great. I dont want to ask questions every day.. I want to answer them! but for that i do need to understand the reasons behind the advice people give me.

thank you again!
_________________
Rick Wubs
Front Office Assistant, Lorka B.V.
www.primalaminaat.nl

rick.wubs@gmail.com
info@primalaminaat.nl
Back to top
View user's profile Send private message Visit poster's website MSN Messenger
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Mar 19, 2009 4:38 am    Post subject: Reply with quote

Quick and ugly database including several filters one way or the other: combo_filter3.odb
The subform-approach uses the extra-table "Filter" only to provide the required binding for the combo box. The subform shows the corresponding records of the main table as a writable record set, but subforms can only show exactly matching records related to the subform ("X=Y"). They can not show records where "X>Y" or "X contains Y".
Same form/subform with pictures and a report bound to the filtered row set: pictures.odb

The other form document in combo_filter3.odb has 2 independent forms rather than a form/subform combination. One form is bound to "Filter" whereas the other form is bound to a SQL-query which combines content of table "Filter" with the related records in the main table (or all records if the filter is null). The combination of two tables returns a read-only recrod set. This is an easy and flexible filter, but you can not edit the results. A third form demonstrates pattern matching where "X contains Y".

My latest approach uses the same 2 independent forms with some tweaked SQL in order to return writable record sets and removable filters: [SOLVED] Form: enter a string expression to populate a grid
This combines the flexible filtering with write-access at the cost of performance, adding a little grain of complexity (select ... where X in (select ...))

Yes, if you are familiar with some programming concepts, macros are handy. But programming Base forms is a pain in the ass, explaining it to others is even more painful and contrary to SQL it is incompatible to anything else (and it does not help to understand the database as is).
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Thu Mar 19, 2009 5:28 am    Post subject: Reply with quote

Hello

You can only change the data in a form when it is based on a real table. A table must have a primary key otherwise you cannot change the table

Do you know there is a wizard for help with the listbox and combobox.

read the tutorial of Benitez about forms

Romke
Back to top
View user's profile Send private message
wubs23
General User
General User


Joined: 14 Mar 2009
Posts: 6
Location: Netherlands

PostPosted: Thu Mar 19, 2009 5:36 am    Post subject: Reply with quote

Okay, so forms based on queries can never be changed?

because i ofcourse always include at least one primary key in my tables..
but i heard from a database expert that it is better to work in layers.

table, on that a query, on that a form..
_________________
Rick Wubs
Front Office Assistant, Lorka B.V.
www.primalaminaat.nl

rick.wubs@gmail.com
info@primalaminaat.nl
Back to top
View user's profile Send private message Visit poster's website MSN Messenger
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Thu Mar 19, 2009 6:07 am    Post subject: Reply with quote

Hello

A subform is normally a query. If you use a table and use master and slave fields then it is changed in a query. But that query can be changed.
It seemes that it is possible to use a form based on a query but it must then have a primary key and no calculated fields.

I always folow the next rule: I make a query and if I can change the data then I have follow the rules. If I cannot change the data then I have not follow the rules and change something so that it works. The computer knows the rules better as I do.
In case of a bug, I have to the bug and wait until there is no bug more. Real simple.

It is not clear to me what you mean with layers but that says more about me as home user. I think it is the same as: don't have a value more then one time in your database.

Romke
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Mar 19, 2009 6:12 am    Post subject: Reply with quote

Layer 0: Certain databases are not editable for technical reasons (plain text, spreadsheets) others may refuse write-access due to permissions (databases where you log-in).

Having write-access on the very first layer, write access can be controlled on 3 layers within Base:

Layer 1: When you remove the primary key from a table you make it read-only. With a primary key you have write access. The PK tells the database engine the exact storage-position of the currently edited record, so it can "put a finger" on the record.
All views are read-only in Base. [views: Queries in the scope of the backend database. Base shows them as read-only tables.]

Layer 2: A Base-query is editable if it uses the row set of a single table including the primary key.
Code:
SELECT "ID", "Name", "Amount" * "Price" AS "Netto" FROM "Articles"

In this example the "horizontal calculation results" of "Netto" are not editable of course, but the "Name" and "ID" are editable.
This type of query can rearrange and filter the rows and columns of a single table while providing full write access.
Adding a new record by the example's 2 fields fails if the table has more mandatory fields that must not be missing. For instance, a mandatory "Price" is not included in the record set. You can not add a new article by Name and ID leaving out the price.
Unfortunately, all queries in "direct SQL" mode return read-only row sets.
All "vertical calculations" (sum, count group by,...) return read-only row sets because each line represents an aggregation of table rows rather than one distinct table row.
The following is not editable due to the combination of 2 tables reflecting a relation between "Articles"."Vendor_ID" and "Vendors"."ID":
Code:
SELECT "Article"."ID", "Article"."Name", "Vendor"."Name" FROM "Articles", "Vendors" WHERE "Articles"."Vendor_ID"="Vendors"."ID"

Other database tools are able to track the vendor name by following the unique ID into the other table and put a finger on the other table's record for editing, but Base can not. Base collects the data form both tables and presents all data read-only.

Layer 3: Quite often it makes sense to restrict access on level 3. You can restrict a form to be editable or not, allow insertion of records or not, delete records or not or you can make a form that is made to add new records only. Single controls may be disabled, so they show some auto-value or calculated result.
A list box does the trick to write the right Vendor_ID for an Article by picking a vendor's name.
A subform does the trick to show all vendor details in a writable manner for the selected article in the main form.
List/Combo boxes select values, forms select rows.
Table controls can bundle groups of controls in columns, showing one row of the same control group for each record.
For full write access across tables a main form provides a writable record set from layer 1 or 2 while the subform provides another writable record set, showing all data related to the main form's currently selected record.
You can mix the controls of main and subform so everything looks like a single editable form for both tables.

In Base you can filter records easily by means of selected records and related subforms. This can not work with list/combo boxes because these controls select values rather than records. However you can easily use table controls as multi-line / multi-column list boxes.
brands_collections_colours.odb

Work-arounds to make list/combos work as predefined filters without writing macros:

My simple form/subform filters work on level 3 entirely. I use the filter table because the list box wants to be bound. So I bind it to a dummy table and let the subform display the related records.
pictures.odb

My slightly advanced filter-forms with 2 independent tables go like this:
[Form1]-->[Table Filter]-->[SQL]-->[Form2]
[Level 3]-->[ Level 1 ] --->[Level 2]->[Level 3]
[SOLVED] Form: enter a string expression to populate a grid with linked database therein pattern_match.odb

Both work-arounds can be used with arbitrary complex criteria sets in an extended filter table.

Comments
Most form-related problems come from the wrong direction of thinking. People have very concrete imaginations of input forms before they even started to build up layer 1. Relations on layer 1 enforce that you can not enter invlid records.
If layer 1 does not include relations, you may be able to create relations in forms nevertheless. However, if layer 1 is not even suitable for relations, your form will not do the trick without writing your own database application in Basic (this is a horror trip).
Base forms have a very limited tools set to map relations to form controls. Abandon all imagination how you want a form to behave.
Try to streamline your mind along the given tools.
Use as many built-in features as you can. Your mind is the flexible part, Base is static. This will help you to write very efficient macros as well.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org


Last edited by Villeroy on Sat Mar 21, 2009 3:28 am; edited 6 times in total
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Fri Mar 20, 2009 9:36 am    Post subject: Reply with quote

Hello Villeroy

Nice to read the information about layers. I have never read about this. It give a structure to things I knew.

Romke
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Mar 21, 2009 1:41 am    Post subject: Reply with quote

In this model the connection would be "Layer 0" which also determines the access mode. In Base file-based sources are read-only (except dBase) and 3rd-party drivers may be restrictive in many ways (different row cursors, access privileges,...). I tweaked the above "article" a little bit.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
PLDP
General User
General User


Joined: 20 Feb 2009
Posts: 9

PostPosted: Wed Apr 01, 2009 9:27 am    Post subject: Reply with quote

Hello everybody.
I am also a newbye that has been trying to make a database in base and finding every single problem for whom, fortunately, I could find most of the answers in this forum. Thanks to all the participants with the questions-problems and the answers, and mainly to Villeroy for his creative and, if possible no macros, solutions, which have been very helpful. Finally I got my database finished and it is working fine, except that I am using the "Open form by tag" to open a form from another form, proposed by Villeroy and although it has been working correctly, suddenly it doesn`t open the form anymore and shows me the following message pointing to the line oView.loadComponent(cWhat, sName, FALSE) and saying "BASIC execution error. Property or method not found". I am not familiar with macros. Could anyone explain to me what should I do to get rid of this error and have the database working properly again?. I am using OO 3.01 on a Mac OS X 10.5.6
Another question regarding the possibilities of this database, is there a limit capacity on the number of records it can manage withouth collapsing or making funny things?. I wouldn´t like to see all the work lost after a while because of this limitation.
Again many many thanks for all your contribution. Only with generous people like you can this project go ahead.
Best regards
Pedro
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Apr 01, 2009 11:57 am    Post subject: Reply with quote

Thank you for the flowers. Please do not append posts to an existing topic unless you refer directly to it. Your flowers and the new problem are completely unrelated to the topic.

Your button has to be attached to one of the main forms of your form document. I guess your button is attached to a subform. Get the form navigator and drag the button onto any main form object.
Code:

oModel.Parent.Parent.Parent.Parent.getCurrentController()

oModel is the button
oModel.Parent is the button's form (control container bound to a record set)
oModel.Parent.Parent.Parent is the form's form document
oModel.Parent.Parent.Parent.Parent is the form documents container, the database document from where we get a controller thingy.
If the button belongs to any subform, it is burried another level deeper and we get the current controller of the form document rather than the database document's controller. That controller "has no idea" how to open a form.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
PLDP
General User
General User


Joined: 20 Feb 2009
Posts: 9

PostPosted: Thu Apr 02, 2009 2:43 am    Post subject: Reply with quote

Thanks Villeroy. Now it is working again. I introduced your filter form solution as the main form, which is wonderful because in such a way, all the related forms open with the information corresponding to the specific record, but, as you mentioned the macro got lost.
Thanks again and sorry for posting my problem in the wrong thread.
Keep the flowers, you deserve them.
Pedro
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group