Query two tables and use as main form with sub form

[mattheww]

Hi there

This is a query and form question...
I am building a simple DB for recording pitches I make to potential clients - I have tblBusinesses (business name, details) with one to many relationship with tblContacts (contact name and details), which has a one to many relationship with tbl Pitches

I have a form for entering new Business and/or Contact.

I need a form to enter a new pitch in tblPitches with the right combination of Business and Contact searched and pre-populated, then I'll enter Pitch detail beneath that.

How do I create that second form so I can select the Business then select Contact (remember one Business might have many Contacts) to then enter the new Pitch information?

(I would have two sub forms on the first form but I don't think this is possible?

Many thanks for your help
Matthew

[Villeroy]

Get toolbar "Form Design" while in form design mode. Button #5 pops up the form navigator which shows the hierarchy of forms with all subforms and controls. Unfortunately, it does not show the controls which represent a column within a table control. Anyhow, right-click the root element "Forms" to enter a new independent form or right-click an existing form to add a subform to that form. This way you can build arbitrary complex trees of forms and subforms.

[mattheww]

Thanks for reply.

Not sure what this is: Button #5
Or this: "root element "Forms"

Could you clarify what you mean please>

[Villeroy]

Push the 5th button on that toolbar and see.

[UnklDonald418]

In the Forms area of the main database window right click on the name of the form document you created and select Edit.
The form document will open in the Design Mode
Along the bottom of the window should be the Form Design toolbar (it can be toggled on and off using the menu selection View>Toolbars>Form Design).
Hover the mouse cursor over those icons to see a little pop-up telling the name of that icon.
Beginning at the left which is Select move over to the right and select the one that displays Form Navigator (Using OO it is usually the 4th from the left or 5th when using LO).
A Form Navigator dialog will appear showing the tree structure of your form document.
At the very root is Forms. Right click on that and select New>Form to add a another top level form.
If you used the Form Design Wizard the first level under Forms will be MainForm. Right click MainForm and select New>Form to add a another SubForm.
You can also right click on the existing SubForm and select New>Form to add a form at the SubSubForm level (you can go as deep as you need)
Right click on any of those MainForm, SubForm, SubSubForm and select Properties to open the Form Properties dialog.
On the Data tab you can choose a Content type (Table, Query, SQL command) and Content (the name of an existing Table, or Query or even design a custom SQL query).


If two dialog lines appear on the Data tab, Link master fields and Link slave fields then you are on a subform level or below and you must chose the proper fields to tie this form to the one a level above, usually based on your designed Foreign Key, Primary Key relationships.

In your case it sounds like Business will be on the MainForm level and Contacts will be on the SubForm level and Pitches would likely be on a SubSubForm level. Then you could display one Business with many Contacts and for each Contact, many Pitches.

[Yumi]

Yes, I am able to create a new Form in the Form Navigator as you describe. But that form would not display in a separate window - or could it?

[UnklDonald418]

The Form Navigator allows you to add mulitple Forms (data sources) to a form document, but each form document has a single page. A form document is really a special type of Writer document with database controls so while it might be possible to add more Forms and controls than will fit on the screen the requisite scrolling can be confusing.
Rather than having everything on a single form document sometimes it is advantageous to have more than one. For instance one form document for entering/editing client data and another form document for entering/editing transactions and perhaps a third for display only, no editing allowed.
There is another approach where on one form document you make selection(s) that are saved in a Filter table. Then another form document can use what is stored in the Filter table to narrow the available data for it's Forms, giving the illusion of a MainForm in one form document and a SubForm in another.

Dialogs can have multiple pages (i.e. the Form Design Wizard or the Copy table dialog) but that is an advanced topic, requiring significant macro programming skills and knowledge of the OO API.