Creating main form with two subforms HELP

[duckster]

--

I'm trying to create a main form (based on the Client table) and two subforms (based on the two other forms). I followed instructions found, and used the wizard. The wizards steps seem to go fine up until the point AFTER i add all the 3 tables and their respective fields...when I click <next>, it goes directly to the layout step. It SKIPS the step: "how do you want to view your form? Choose subform or linked", and creates a huge long form with a bunch of fields instead, no subforms.

I have no problems at all when creating a main form with just ONE subform using the wizard (I can create a main form and subform fine if it's just with the tbl_Client and tbl_Payment ..... or just the tbl_Client and tbl_Income).

Here's a brief rundown of my tables and specs:

I have 3 tables. tbl_Client, tbl_Payment, tbl_Income.

There is a one to many relationship between the tbl_Client (one) to tbl_Payment, and there is a one to many relationship between tbl_Client to tbl_Income. I've created the relationship already, joining the ClientID of the tbl_Client to the ClientID of the tbl_Payment (and as well, joining the ClientID of the tbl_Client to the ClientID of the tbl_Income) respectively.


I followed the help instructions clearly below:
Create a form with two subforms

The main form has a one-to-many relationship with the first subform, and the first subform has a one-to-many relationship with the second subform, and the main form contains both the subform controls
In the Database window, click Forms under Objects.
Click the New button on the Database window toolbar.
In the New Form dialog box, double-click Form Wizard in the list.

In the first wizard dialog box, select the table or query for the main form. For example, suppose you want to create a Customers form that has two subforms— an Orders subform and an Order Details subform. Select the Customers table
Note It doesn't matter which table or query you choose first.

Double-click the fields you want to include from this table or query.
In the same wizard dialog box, select the table or query for the first subform. Using the same example, select the Orders table ,and then double-click the fields you want to include from this table or query.
In the same wizard dialog box, select the table or query for the second subform. Using the same example, select the Order Details table, and then double-click the fields you want to include from this table or query.
When you click Next, if you set up the relationships correctly before starting the wizard, the wizard asks which table or query you want to view by. Using the same example, to create the Customers form, click By Customers.
In the same wizard dialog box, select the Form With subform(s) option.
Follow the directions in the remaining wizard dialog boxes. When you click Finish, Microsoft Access creates a main form with two subform controls. It also creates two other forms, one for each subform.

--
Thanks in advance for your help...I've been trying to fix this for over an hour now.

--

 

[scschroeder]

Wow, those directions are a bit confusing. Here's how I would proceed. Build a query from your tblClient that contains the fields you would like to display on your "Main" form. Save your Query. Open the Forms Wizard and first build your main form off of the query you just saved. Once this is built, build a second query from your tblPayment. Save the query. Open your main form in design view. Click on the top menu to display your 'Toolbox'. In this menu, you should see a button that has the tool-tip 'Subform/Subreport'. Click this a draw a box somewhere on your main form where you would like your first subform to appear. This will open the subform wizard. Select the query you built from the tblPayment (qryPayment) as the subform record source. A few steps in, it will ask you what control on your main form links to your subform (ie. Parent to Child - Main.Client ID to Subform.ClientID). Finish the wizard and go back and format your subform to your liking. Follow the same steps for your Income data. Format the second and your finished. Now you can add new income data and/or payment data or a new client all together. The subforms will automatically populate with the ClientID identified on the main form for the new client.

 

[Pat Hartman]

The help entry is describing a hierarchial relationship such as Customer-->Order-->OrderDetails. Your relationship is NOT hierarchial. You have two independent 1-->many relationships - Client-->Payment and Client-->Income. That's why the wizard isn't working as you expect.

Make sure that the proper relationships are defined between the tables. Enforce RI and specifiy CascadeDelete. You can use the wizard to create the main form and 1 subform. You'll need to add the second subform manually. Don't forget to set the master/child links.

 

[duckster]

Great help, both of ya.

I tried the "easier" way first as Pat mentioned, and it worked like a charm.
1) Create a main fom with 1 subform (main form being the client table, subform being the payment table)
2) Create one form based on income table.
3) Open the main/subform in design view created in (1) above. Ensure "control wizard" is highlighted on the toolbox.
4) Drag the income form created in (2) directly into the design view of the main/subform.

Seems basic after you know how to do it

 

[LMCReader]

Have a similar problem

I have a very similar problem, but need help creating a main form with five subforms. Is it possible to update the database from one form where I have five different one-to-many relationships? I have one action (action item) table that is the many side. I have 5 small tables: employee, site, type (of action such as s/w, h/w), status, etc. These are all the one side. They are related properly in the db. When I create the one to many form with the wizzard, then add the 4 other subforms, I can enter data for the two forms created in the wizzard. I get an error for Site. It doesn't know what to do with it or the other tables. Does this make sense? Thank you!

 

[Pat Hartman]

Make sure that the master/child links are properly set for the subforms that you added.

 

[LMCReader]

Let me rephrase my problem

If I have a 1:M relationship in a form as a "1" table main form with the "M" table as the subform, how can I add 4 other tables that are related to the "M" subform table in a 1:M relationship; the 4 being "1" tables all related to to the subform table in the same way as the "1" main form table?
Putting a subform in the subform doesn't work: error message:
"A form with a subform object can't have its DefaultView property set to Continuous Forms. You tried to add a subform to a form in DesignView. M Access will reset the property to Single form.
Making the many table the main form doesn't work - won't let you have any subforms, only a single form.
I want ONE form to update my entire star schema database.
Linking in the table property only allows you one link, correct?

 

[Pat Hartman]

You aren't going to be able to make an updatable query that includes multiple separate 1-m relationships. Try using a form with tabs. You can use a main form and each tab page can have a separate subform. If you have intermediate tables that you want to represent as continuous, you will have to use the double-click event of some control (or a button) to open the next level down form. You could try to use subdatasheets. They're not as pretty but they expand by pressing the plus to the left of the record.

 

[LMCReader]

Thank you. I was beginning to think it was not possible unless I used some other software code like VB. I am not sure what you mean by a tab, but I will look it up.