Creating subForms

[KavJack]

Basic question here.
I have a main form Projects based on tblProjects.
I have a main form Clients based on tblClients.
How do I make a subForm for the clients in the form Projects ?
Client_Contact is the common field.
I know nothing about subForms

 

[grande]

Go to your list of Forms in the Database Window (the thing with Forms, Queries, Modules, etc along the left side).

Click New.

Use "Form Wizards". Click Next.

Select the Projects table from the drop-down list and select all the fields you want.

Select the Clients table from the drop-down list, and select all the fields you want. Click Next.

Make sure "Sub-Form" is selected at the bottom.

Follow the wizard home.

Hope this helps!

 

[KavJack]

No, it doesn't !!
My Form Wizard NEVER offers me the choice of selecting "Sub-Form". No mention of "Sub-Form" anywhere ! That's my problem. I am running Windows XP Microsoft Office 2003.

Make sure "Sub-Form" is selected at the bottom.
NO Chance of performing this operation.
What's wrong here.
That's why searching for this on the internet did not help me.

 

[AppStaff]

I wouldnt worry to much about the wizard. Sub forms are pretty easy. Try this.

1. Create your sub form. Try to make sure that your controls and fields related to your main form match exactly.

2. open your main form in design mode and reduce the screen size so that you can see the database window.

3 Drag the sub form object from the database window to the location on the Main form you wish it to be.

4. save your main form design.

As long as your related control names match and are normalized you shouldnt have any problems. If they dont match you can manually change the link child link parent properties on the sub form object to link them correctly.

You may wish to also change the sub form to be a different view datasheet, continuous forms and form are your options.
HTH

 

[KavJack]

Your explanation is inadequate.
How do I perform step 1 ?
You blithely say :
1. Create your sub form. Try to make sure that your controls and fields related to your main form match exactly.

HOW ?????

 

[TheAceMan1]

How are ya KavJack . . . . .

[blue][purple]I know nothing about subForms . . .[/purple]

No, it doesn't !!
What's wrong here.
That's why searching for this on the internet did not help me . . .

Your explanation is inadequate.
How do I perform step 1 ?
You blithely say :[/blue]

[purple]Your battering is making you unworthy of help![/purple] [purple]No wonder help for you is turning out the way it is![/purple] If you decide to continue with this demeanor . . . be aware . . . you can be [red]Red Flaged! . . .[/red]

If you are as the demeanor of your posts presents (which I don't want to believe) a great many will pass you by as well . . .

Now . . . you've indicate that the two tables are not directly related in your relationships window (as if there both children to a parent table). Otherwise you should've gotton the subForm indicator suggested by [blue]grande[/blue]. [blue]By any chance is this the case?[/blue]

See Ya! . . . . . .

 

[AppStaff]

I don't see how my response is inadequate. I certainly could have worded it better or clarified steps. A subform is no different than a main form in its creation. The only difference are the steps I described later in the process which tie the subform to the main form.

Since you already created a main form I am assuming you understand how to create a form. When I say match fields and controls I mean that the Names for the Keyed fields must match for access to set those parent child links in the properties for you. If Client_Contact is the field that matches in both forms make sure it is spelled that way in both underlying tables and the controls on both forms.

If you don't understand something just ask. I don't need you to "review" my attempt to help you.

 

[willir]

KavJack

Let's step back a bit, and take a deep breath.

I hear frustration from one side, and assumptions on the other.

First, lets verify that the tables are defined correctly. From the menu, select "View" -> "Database Objects" -> "Tables". We want to open tblclients in "design more". Click once on tblClients. The click on the "Design" button at the top of the database window (not on the menu bar). You should see tblClients displayed with the fields that were created with the table. What I want to see is the "primary key" -- there will be a key icon image to the left of the field. From what you provided, the Client_Contact field should be your primay field. Note the Data_Type. Is it numeric or text? Is Client_Contact the primary key in tblclients?

Now look at the bottom half of the design window -- the title bar will read "Field peroperties". Take a look "Field Size". Is it a Long Integer or a number representing the number of text characters?

Close this table, and open tblProjects in design mode. Again, look at the Client_Contact field. Does the data type match what you saw in tblClients? Does the field size match too? If not, then correct Client_Contact in either tblProject or tblClient so they match. I will talk about Client_Contact later.

Second, verify your relationships. From the menu, "Tools" -> "Relationships" If you did not create any relationships, there will be no tables on the GUI graphical user interface. To add your two tables, click on the menu "Relationships" -> "Show table". Double click on tblProjects and tblCients. Select "Client_Contact" field from tblClients and drag and drop it on to Client_Contact on the tblProjects. A window, "Edit Relationship" will pop up. Click on the button "Enforce referential integrity". Click OK, and Close the "Edit relationship" window and then close the Relationship window -- save when prompted.

Third, create the two forms. frmProjects will be your main form and sbfrmClients will the subform.

From the menu, select "View" -> "Database objects" -> "Forms". Click on the "New" button at the top of the Database window. Select "Form wizard" and choose tblProjects from the pick list / combo box on the bottom half of the "New Form" dialog window, and click Ok. The wizard will "fire". On the next screen add all fields from tblProject to the form by clicking on the ">>" double right arrow. On the next screen, choose "Columnar". On the next screen, choose a design template. On the last screen change the name of the form to "frmProjects".

Repeat the above using tblClients. This time call the form sbfrmClients.

Note: You may have to modify the form and subform to meet some cosmeticly pleasing design. Tick, tick, tick. done, good.

Last step. Open the frmProjects in design mode. To do this, click once on the frmProjects in the database window and click on the "Design" button at the top of the database window. Move the form to the side (right or left, up or down, you are going to need some space). Now click once on the sbfrmClients in the database window and drag it to where you want to place it in the frmProjects. You will / you can tweak the location and size of the subform to suit your needs.

Save the form. When you create a Project record, and then create a client, or select a client, the records in each table will match.

Note:
By creating your relationships first, Access will automatically link the main form and the subform together with what is called LinkMaster and LinkChild fields.

When working with forms, it is very useful to have the Property window open. From the menu, select "View" -> "Properties". Explore. Select objects on the form and look at the values in the Property window.

Often, the subform will created as a "contineous form". Using the form wizard, choose "Tabular" instead of "Columnar".

Last note:
I am curious about your design. You suggest that one project has many clients...

How do I make a subForm for the clients in the form Projects ? ... Client_Contact is the common field.

Client_Contact sounds like a primary field in tblClients. A primary key uniquely identifies a record and is used to link to other related tables. There are three types of relationships where one-to-many is the most common. For example, one classroom has many students. So using the student / classroom example, here is what I don't understand. If I were to create a form / subform, I would choose to create a main form based on the Classroom, and the subform would be based on the Students. I bring up a Classroom record and look at all the students registered for the classroom. Here, StudentID would be the primary key on the student table, and ClassRoomID would be the primary key on the classroom table. The student is on the "many" side, so the student table would include the ClassRoomID. So when I look at a classroom record, Access would find all the students with the same ClassRoomID.

You seem to / may have done it the other way around.

Please consider reading the following documentation...

Fundamentals of Relational Database Design
Download document
Read on-line (HTML)

Micro$oft's answer...
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

Also...
Introduction to Relational Databases - Part 1: Theoretical Foundation (15 seconds)

AND lastly, an on-line tutorial for you...
step-by-step guides to using databases (geek girls)

Richard