Multiple SubForms in a Form

Creating and using forms
Post Reply
soapyhudson
Posts: 4
Joined: Tue Jan 15, 2008 2:06 pm

Multiple SubForms in a Form

Post by soapyhudson »

I have tried several ways of creating a two (or more) sub-forms in a form and cannot get it to work - does anyone know how to do this, please?
I was also wondering (I admit I haven't investigated this yet) if it is possible to create tabbed forms, e.g. multiple tabbed forms to allow easy viewing of different types of data in one database - anyone?
QuazzieEvil
Volunteer
Posts: 283
Joined: Tue Dec 04, 2007 6:38 pm
Location: Houston, TX

Re: Multiple SubForms in a Form

Post by QuazzieEvil »

to create forms open the form navigator (button with compass icon in form design tool bar). to add another top-level form right-click on Forms and select New | Form. to add a sub forms right-click on the form you want and follow same steps --- as top level form. you must set the link master/slave fields on the sub form so the form-subform work properly. the master field is the field in the parent form that relates to the sub form, and the slave is the field in the sub form that relates to the parent form--these can be the same fields in a one-to-many relationship in the tables associated with the form/subform.
soapyhudson
Posts: 4
Joined: Tue Jan 15, 2008 2:06 pm

Re: Multiple SubForms in a Form

Post by soapyhudson »

Thanks for that! I need a bit more time to work on this obviously as I haven't quite managed to get this working yet. I expect I'll be back with more questions over the weekend.
soapyhudson
Posts: 4
Joined: Tue Jan 15, 2008 2:06 pm

Re: Multiple SubForms in a Form

Post by soapyhudson »

I am finding this very difficult to follow. I have managed to create tables to contain my three groups of data with appropriate fields and even a form with one sub-form (I'll come back to the small glitch with that once the main problem is solved) in it. Adding a second sub-form seems beyond me. Trying to follow your instructions leads me nowhere as I cannot get beyond the first step. A right click on the form gives me only options to 'open' or 'copy' nothing else - and, I now cannot get back into 'design view' to make changes to the layout for controls.
Apart from trying to discover how to get into design view, should I be creating the sub-form separately first, then embedding them into the main form?
At least if I can solve this I suppose I should be able to write the detailed tutorial on sub-forms that seems to be missing.
User avatar
kabing
Volunteer
Posts: 678
Joined: Fri Nov 23, 2007 12:05 am
Location: Midwest, USA

Re: Multiple SubForms in a Form

Post by kabing »

I don't know why you "edit" is not an option for you when you right-click on the form.
Are you using the embedded database?
What version of OpenOffice.org and what Operating System are you using?

kabing
NeoOffice (app store version) OpenOffice.org 4.1 and LibreOffice 4.3 on Mac OS X El Capitan
OpenOffice.org 4.1.2 on Windows 10 (Previously on Vista)
User avatar
LJ Bettona
Posts: 58
Joined: Fri Jan 04, 2008 12:31 pm
Location: Italy

Re: Multiple SubForms in a Form

Post by LJ Bettona »

This is exactly where I am.
I have a music database with four tables - 3 are lookup tables (artist name, format of recording, type of music) and one is not (album title).
I need to create a form for entering new albums which allows me to pick the artist, etc from the reference tables. To me that means 3 sub-forms. When I used the wizard, I could only put one sub-form on the form.
When I had created the form with one sub-form, I saved it with the option to edit the form, hoping I could find an option somewhere to create more sub-forms. I guessed I was going to end up in design view.

I now had a form based on one table that had a sub-form based on one of the reference tables. I wanted to add two more sub-forms to link to the other two reference tables. (With me so far?)

According to the Help file, I needed to use the Navigator so I opened the Navigator and clicked on Main Form which highlighted the field from the main form table. That seemed to be what the Help File said would happen.
I clicked on the form button on the design toolbar and was offered the form I was looking at and no options to add something else. No luck there.
I clicked on the main form in the navigator and clicked on 'Add Field' and was given a list of the fields on the table linked to the main form. I want fields from the other two tables not a list of keyfields that link the album table to the reference tables. I couldn't see any option to select a different table. No luck again.
I have looked in 'Getting Started with Base' and it says designing forms in Design view will be covered in the Database Guide, but I couldn't find a Database Guide in the documentation.
I have looked in the Neo Office Database Tutorial and it also only covers one sub-form created with the wizard.
Have I missed somewhere to look?
This can't be rocket science. Anyone who normalizes data will end up with a number of lookup tables that have to be referenced from an entry form. There must be a simple way to do this. What is the obvious thing I am missing? There is always something obvious that you miss.
Thanks for any help.
iMac G5, OS 10.4.11
OO 2.4, NeoOffice 3 patch 1
OOo 2.2.X on Mac OSx other + Actually NeoOffice 3 patch 1
User avatar
LJ Bettona
Posts: 58
Joined: Fri Jan 04, 2008 12:31 pm
Location: Italy

Re: Multiple SubForms in a Form

Post by LJ Bettona »

OK, I've been messing.

I've been trying right clicking in the Navigator. This allows me to add a new form but I only end up with a sub-form called Standard that has no table linked to it and no fields. So, I've deleted it.

Then I thought ' How can I get a new table linked to this form?' So, I went to the main database window, clicked on tables and dragged one of the two remaining tables I was interested in onto the form (which was open in edit mode.). I got a + sign in a green button and a very interesting window which looked like it was going to allow me to choose fields and how they were displayed! ( I got all excited!)

I selected the relevant field, selected 'display as field' and clicked OK.
**Expression is faulty** is displayed three times across my form. (That'll teach me to get excited.) Interestingly enough, when I hover the mouse over these lines, I get the full address on my hard disc drilled down to the field name I was trying to use.

Because I'm made this way, I went back and tried it with the one remaining table. This time I got the + in the green button, the window that allowed me to choose how the data would be displayed and, when I'd clicked OK, nothing, nada, niente. No error message but no field I can see on the form, either.

Nothing has been added to the Navigator, which would imply that no significant change has been made to the form. (But that could be a rash assumption on my part.)

I will now discard all of this and wait for someone who knows what they are doing to suggest a way for me to create a form that contains three sub-forms each linked to their own reference table.
Thanks.
iMac G5, OS 10.4.11
OO 2.4, NeoOffice 3 patch 1
OOo 2.2.X on Mac OSx other + Actually NeoOffice 3 patch 1
User avatar
LJ Bettona
Posts: 58
Joined: Fri Jan 04, 2008 12:31 pm
Location: Italy

Re: Multiple SubForms in a Form

Post by LJ Bettona »

Well, I'm still messin'.
Soapy, I found a way, using the Navigator to get to the point where I have a subform defined using the table I want and with the fields linked.

Try this:
Open the form you want to put a subform into in edit mode.
Open Navigator
In navigator, right click on the form you want to create a subform for (I found it easier to select rename first and give it something more sensible than 'Main Form' which meant nothing to me) and select New from the list
This should create a subform called 'Standard' which I immediately renamed into something I could recognize
Right click on the new subform and select Properties
This opens a new window with the General tab selected.
Select the Data tab
Under Content, you can choose from the tables in your database.
Under Link Master Fields you can select which field to link to the main field in the main form.

Now that's as far as I got, because I can't find a way to make the field I selected display on the form.

Here's a link to where I am now:
Image

Can someone tell me how I get the slave field ("MFormatName") that I have now linked to my master field ("MAlbumTitle") onto the form?
I've been using an ancient document called 'How to link tables using named parameters' which is based on OOo 1.x. as a guide. It references all sorts of buttons and stuff that no longer exist. It talks about drawing a box and then selecting a table and fields to display in that box. I can't even seem to find a way to draw the box so that I could see what windows come up next.

I think I'm going to stop there for now. There's only so much brick wall I want to bang against in a day.

Thanks for any help.
iMac G5, OS 10.4.11
OO 2.4, NeoOffice 3 patch 1
OOo 2.2.X on Mac OSx other + Actually NeoOffice 3 patch 1
User avatar
kabing
Volunteer
Posts: 678
Joined: Fri Nov 23, 2007 12:05 am
Location: Midwest, USA

Re: Multiple SubForms in a Form

Post by kabing »

First question: Do you want the values you select from the lookup tables to be saved in the album table? That is do you have ArtistName, format, and type fields in the Album table? If so, you don't need to to have subforms. Instead, you use list boxes. Unless, of course, you want to be able to add new data to the lookup tables on the same form. In that case I just think you need additional main forms, but I'm not sure about that.
I've been trying right clicking in the Navigator. This allows me to add a new form but I only end up with a sub-form called Standard that has no table linked to it and no fields.
You just didn't finish the process here. Once the new subform appears in the Navigator, you right click on the new sub-form name and click on the Properties option in the contextual menu. You set the table source, etc. in the Data tab.

There is a tutorial on creating a subform this way in the NeoOffice wiki; I'm afraid I'm not familiar with one written for "straight" OOo. Since NeoOffice is based on OpenOffice.org, thought, the instructions should work fine for OpenOffice.org, if you keep the following things in mind:

1) references to the command key in a Neo tutorial are the equivalent to the control key in OOo. (i.e. Command-C becomes Control-C) Note that I'm not sure if this is true with OOo for X11.
2) control-clicking is the same as right clicking
3) icons sometimes look different, as NeoOffice 2.2.2 has a custom icon set.

hope this helps a little bit.

kabing

Edit: I fixed the link formatting; also, I didn't see your last post before posting this.
Last edited by kabing on Mon Jan 21, 2008 8:20 pm, edited 1 time in total.
NeoOffice (app store version) OpenOffice.org 4.1 and LibreOffice 4.3 on Mac OS X El Capitan
OpenOffice.org 4.1.2 on Windows 10 (Previously on Vista)
User avatar
LJ Bettona
Posts: 58
Joined: Fri Jan 04, 2008 12:31 pm
Location: Italy

Re: Multiple SubForms in a Form

Post by LJ Bettona »

Hi, Kabing,
Thanks for getting back to me.
First question: Do you want the values you select from the lookup tables to be saved in the album table? That is do you have ArtistName, format, and type fields in the Album table? If so, you don't need to to have subforms.
I have the keyfields from the three reference tables in my album table and am assuming that when I select an artist, etc from the combo boxes, the ids will be saved, not the names. That's why I think I need sub-forms, but I could easily have misunderstood what I was reading in Getting Started and the NeoOffice guide.
As you can see from my previous post, I got as far as you are suggesting.

I just looked at the image and it's much too small, so here's a link to a clearer one that will show where I am now.

Image


The problem now is to get the slave field onto the form. I'll go back to Adding a subform and see where I get following that.

Thanks for the pointer.
iMac G5, OS 10.4.11
OO 2.4, NeoOffice 3 patch 1
OOo 2.2.X on Mac OSx other + Actually NeoOffice 3 patch 1
User avatar
kabing
Volunteer
Posts: 678
Joined: Fri Nov 23, 2007 12:05 am
Location: Midwest, USA

Re: Multiple SubForms in a Form

Post by kabing »

LJ Bettona wrote: I have the keyfields from the three reference tables in my album table and am assuming that when I select an artist, etc from the combo boxes, the ids will be saved, not the names. That's why I think I need sub-forms, but I could easily have misunderstood what I was reading in Getting Started and the NeoOffice guide.
Ah, so you want be able to lookup the Artist name in the Artist subform, but enter the ID number for that artist in the Album table. Is that right?

The tutorial page I linked to is designed to facilitate doing that kind of thing by hand. The subform there looks up all field trips that happen on the same day as a given picture was taken. Then the user can manually enter the correct field trip ID number (if any) into the field in the Bird Pictures table. In theory there could be more than one field trip on a given day. In that instance, the date the picture was taken is already known and entered, and forms the slave/master link. Presumably with what you are doing, you don't know the Artist ID initially: you are wanting to look it up. So I'm not sure a subform will help you here. It might work to have a series of several main forms on the same page, so you can look up the number in question. But you'd still have to enter it by hand.

It seems to me that I have read about a way to do this automatically, where you choose an Artist name, but the data saved in the field is the corresponding ID number, but I'm afraid I don't know how to do it. (If I'm right that I've read about it, it would have been in one of the OOo forums; probably oooforum.org). You might want to start a new thread titled something like "Lookup one field, enter another field's value to ask about that process.
LJ Bettona wrote:The problem now is to get the slave field onto the form. I'll go back to Adding a subform and see where I get following that.
-Make sure the subform is selected in the Form Navigator
-Turn on the Form Controls toolbar (it's not on in the screen shot you provided)
-click on the the type of control you want to add (text, radio button, etc.)
-click and drag on the form where you want the control to appear
-Right click on the control, choose "Control" and set the fields in the various tabs as needed. Specifically, the Data tab will let you choose which field it displays/writes.

kabing
NeoOffice (app store version) OpenOffice.org 4.1 and LibreOffice 4.3 on Mac OS X El Capitan
OpenOffice.org 4.1.2 on Windows 10 (Previously on Vista)
User avatar
LJ Bettona
Posts: 58
Joined: Fri Jan 04, 2008 12:31 pm
Location: Italy

Re: Multiple SubForms in a Form

Post by LJ Bettona »

Hi,
Ah, so you want be able to lookup the Artist name in the Artist subform, but enter the ID number for that artist in the Album table. Is that right?
Yea, that's the idea. The artist's name is displayed in the drop down list. The user selects the right name and it's id (the keyfield) is stored on the album record. Reduces storage space dramatically.

But - I've had an idea. Suppose I used the Artist's name as the keyfield instead of some arbitrary self generated number? Then, the name itself could be saved on the album table, and the relationship would be between artist name on both tables. It's not the best way to design a database, but it may be the way this system works best.

This makes a very small artistname table - just the name field 'cause I'm not into DOB and biog stuff, just who's performing.

If this works, then the same would hold for the other lookups - single field tables. But it would guarantee that the input is consistent and accurate. And that's the objective - both for this one and for the book list and DVD list I want to build. They're all basically the same database, just field names will be changed.

I'll work on your instructions later today and see how far I get. I'm interested in your reference to more than one main form on a form. I'll reread the NeoOffice tutorial.

Thanks for the help.
iMac G5, OS 10.4.11
OO 2.4, NeoOffice 3 patch 1
OOo 2.2.X on Mac OSx other + Actually NeoOffice 3 patch 1
User avatar
kabing
Volunteer
Posts: 678
Joined: Fri Nov 23, 2007 12:05 am
Location: Midwest, USA

Re: Multiple SubForms in a Form

Post by kabing »

LJ Bettona wrote:Hi,

But - I've had an idea. Suppose I used the Artist's name as the keyfield instead of some arbitrary self generated number? Then, the name itself could be saved on the album table, and the relationship would be between artist name on both tables. It's not the best way to design a database, but it may be the way this system works best.

This makes a very small artistname table - just the name field 'cause I'm not into DOB and biog stuff, just who's performing.

If this works, then the same would hold for the other lookups - single field tables. But it would guarantee that the input is consistent and accurate. And that's the objective - both for this one and for the book list and DVD list I want tobuild. They're all basically the same database, just field names will be changed.
That's essentially how I handled things with the Birding Database that forms the basis of the tutorial. The difference is that I still had an integer ID field for each of the tables. But in most cases the relationships between the tables are based on a field other than the primary key. You will need to create a unique constraint for the field that is to be the "one" side of the "one to many" relationship. That's covered in one of the tutorial articles. Just make sure you have a primary key field of some sort, you won't be able to edit your data.

I handled the consistent/accurate input slightly differently in the tutorials. I started out with lookup tables as you did, but then I realized that I would have to enter the data in the lookup tables first. For example, when entering a new field trip, I would have to make sure the weather type I wanted was already in the weather table before proceeding. So I opted instead to do combo boxes on the field trip form that listed for me the exsiting distinct entries in the field in question. That let me choose an existing "entry," but also let me enter a new one, and no lookup table was involved. It's not quite as safe as lookup tables in terms of guaranteeing no duplicates or mispellings, but it was sufficient for me. The process is described in the Using Combo Boxes in a Form article.
LJ Bettona wrote: I'll work on your instructions later today and see how far I get. I'm interested in your reference to more than one main form on a form. I'll reread the NeoOffice tutorial.
There's not an article in that tutorial on two main forms in a form, as I hadn't discovered that was possible then. I discovered it by accident. It's the same initial process as creating subform, only you right click on the folder that says "Forms" at the top of the form navigator instead of on the existing Main form. But I don't know how the data entry/display works if you do that, as I didn't experiment with it.

If that doesn't work, and you want to still use lookup tables instead of the combo box method I discussed above, you can add buttons to the main form that will open data entry forms for the various lookup tables to make it easier to access them for data entry. See Adding a Button to Open Another Form
LJ Bettona wrote:Thanks for the help.
You're welcome. :)
NeoOffice (app store version) OpenOffice.org 4.1 and LibreOffice 4.3 on Mac OS X El Capitan
OpenOffice.org 4.1.2 on Windows 10 (Previously on Vista)
soapyhudson
Posts: 4
Joined: Tue Jan 15, 2008 2:06 pm

Re: Multiple SubForms in a Form

Post by soapyhudson »

Hi Loretta,

Your doing a grand job so just carry on as you are. You seem to know what questions to ask - and that's half the battle! I'll retire gracefully and let you ask the right questions although I might poke my nose in if I manage to get anything running.

Your input has at least brought me up to the same point as you, thanks, David (Soapy)
User avatar
LJ Bettona
Posts: 58
Joined: Fri Jan 04, 2008 12:31 pm
Location: Italy

Re: Multiple SubForms in a Form

Post by LJ Bettona »

Hi,
My computer died and needed a new power pack. That took three weeks. So, I read the different guides and SQL for Mortals while it was gone and got some ideas. I now have a working CD database that displays the contents of the reference tables in alphabetical order and stores the ID number on the main table.
My tables are:
MArtist
MAlbumTitle
MFormat
MType

The fields in MArtistTitle that will hold the reference ID are integer fields.

There are relationships between the reference table IDs and the ID fields on MAlbumTitle.

Here's how I did it:

First - there are no sub-forms.

I created a form using all the fields from my main Album table except its ID.

Second - modify the controls for the fields you want to use reference tables for to List Boxes (See pg 25 - Getting Started with Base.)

Third - enter the following SQL in the Data section of the Properties window: (I'll use the one for artist's name so it isn't just gibberish, then explain later)

SELECT "MArtistName","MArtistID" FROM "MArtist" ORDER BY "MArtistName"

OK, translation.
"MArtistName" is the field that holds the artist's names and it's those I want displayed.
"MArtistID" is the primary, self-generated key on the MArtist table that I want stored.
"MArtist" is the table I'm getting this stuff from.
ORDER BY is the SQL command that sorts, it appears to default to ascending.
"MArtistName" is the field I want sorted.

Now, when I enter a CD, the drop downs display words, but the ID is what is stored. Minimizing the storage space.

Don't ask me how the storage happens. Probably through the relationships I built. Do you know, Lorinda?

Anyway, I'm finished here. I got what I wanted. Hope this helps you, Soapy.
iMac G5, OS 10.4.11
OO 2.4, NeoOffice 3 patch 1
OOo 2.2.X on Mac OSx other + Actually NeoOffice 3 patch 1
User avatar
kabing
Volunteer
Posts: 678
Joined: Fri Nov 23, 2007 12:05 am
Location: Midwest, USA

Re: Multiple SubForms in a Form

Post by kabing »

LJ Bettona wrote: Now, when I enter a CD, the drop downs display words, but the ID is what is stored. Minimizing the storage space.

Don't ask me how the storage happens. Probably through the relationships I built. Do you know, Lorinda?
Thanks for such a detailed response! I can't give a definitive answer about how the storage happens. I think it's just the nature of list boxes to work that way.

kabing/Lorinda
NeoOffice (app store version) OpenOffice.org 4.1 and LibreOffice 4.3 on Mac OS X El Capitan
OpenOffice.org 4.1.2 on Windows 10 (Previously on Vista)
Post Reply