Can someone explain subforms?

[KjWhal]

I hate to ask such a broad question but googling just turns up links for how to use the wizard, and I don't want that.

Its my understanding that a subform functions a bit like a report, I can set the recordsource to a query and it'll display all the records returned, in the controls that I've set the control source for. Yes?

What I'm curious about implementing is the ability to add new records from within the subform itself. Allow Additions is turned on but I have nowhere to input anything.

I'd also appreciate any other general pointers regarding subforms, I learned to program and use programming IDEs before I learned Access so I don't always find Access terribly intuitive.

 

[Oldsoftboss]

Subforms are usually used to display related records.
EG:
If you display a customer in a form, the subform could be used to display items purchased etc.

It is all dependent on how your tables are set up.

 

[RuralGuy]

As Dave was saying, SubForms are often used to display the many side of a one-to-many relationship with the MainForm. Your condition with your SubForm could be caused by basing your SubForm on a NonUpdateable query. Can you change anything in the SubForm?

 

[KjWhal]

As Dave was saying, SubForms are often used to display the many side of a one-to-many relationship with the MainForm. Your condition with your SubForm could be caused by basing your SubForm on a NonUpdateable query. Can you change anything in the SubForm?

How exactly does a Form have a record? Typically what I do is create a new form and then plop down a combobox thats filled by a select query to the primary key and use that to populate everything else.

I don't understand how a select statement could be an updateable query.

 

[The_Doc_Man]

Ah, we have a more basic issue.

First, Access knows tables - but doesn't really care. Because what everything else wants (about 90% of the time, no kidding) is RECORDSOURCES. Now, a table is a valid recordsource. But so is a query.

A SELECT query can be designed to look at all the fields in a table with a particular order imposed on them; or it can show selected records based on a WHERE clause; or it can show selected FIELDS based on a SELECT that is not a SELECT * clause; or some combination of the above. A SELECT query can also compute aggregate functions such as MIN, MAX, COUNT, AVERAGE, and a few others. (Look up SQL Aggregates in Access Help for a list.)

Of all the SELECT queries included in the above, only the aggregates are not updateable. In all other cases I showed, there is a one-to-one correspondence between the cells in datasheet views and a specific field in a specific record in the underlying recordsource. And THAT is the main basis for an updateable query. Not the only basis - but the main one. The table has to be writeable to you or updates cannot happen. If there is a really complex join and you are trying to update a field on the ONE side of the ONE:MANY relationship that participates in the join, the correspondence I mentioned earlier is blurred by the fact that more than one JOINed record correlates to the same single record on the ONE side of the JOIN.

Now to the question at hand.

Sub-forms are used in cases where you have parent-child relationships between two tables such that a ONE:MANY relationship can be defined. If the relationships ARE defined, then what you have is a near trivial case to create a parent-child form.

Now, the thing about parent/child relationships is that they are based on some field that betrays/exposes the fact of relationship.

Take a simple case - purchase order with many line items. The PO Number is the likely candidate that links a master purchase order record to the many line item records for the purchases made under that PO. IF you have defined the relationship between the POMaster table and POLineItem tables, you can create a parent/child FORM to go with the parent/child tables.

OK, here's the trick. To do this, you need TWO forms. Ideally, the one you are going to use as the subform is smaller than the parent. BOTH forms must contain the field that is the link between them.

So... if the tables exist and are properly "related" (i.e. by an existing link drawn on the relationships pane), create the forms, child form first. When you create the parent and want to create the sub-form, that is just a control from the tools selection bar. Enable the wizards (if not already enabled), select the subform icon, and drag it to your form. It will ask you to select the subform. (This is why you create the sub first.) If you have defined the relationships ahead of time, it shouldn't even have to ASK you the names of the fields that are parent and child - because the relationship tells it what it needs to know.

The thing you are doing, with a combo box to populate a form, is not the more common approach. Though it isn't unheard of to do it this way. I might suggest that you could do the following: Activate the forms wizard to build a form for your table. Now go back in design mode. Remove the thing that shows the primary key. Enable the controls wizard (if not already enabled) and plotz down your selector for the primary key. The wizard SHOULD ask you what you want to do with the selection, and one of the options is to select the record associated with that key value.

OK, having done this, you can go back and just LOOK at the code involved, created by the button wizard.

What I just described to you over the last few paragraphs is how to create a bound form, quick and dirty. Trust me, though they are dumber than a box of rocks, the Access wizards can still be your friends by giving you the skeleton of something to go back and customize.

 

[KjWhal]

OK, here's the trick. To do this, you need TWO forms. Ideally, the one you are going to use as the subform is smaller than the parent. BOTH forms must contain the field that is the link between them.

So... if the tables exist and are properly "related" (i.e. by an existing link drawn on the relationships pane), create the forms, child form first. When you create the parent and want to create the sub-form, that is just a control from the tools selection bar. Enable the wizards (if not already enabled), select the subform icon, and drag it to your form. It will ask you to select the subform. (This is why you create the sub first.) If you have defined the relationships ahead of time, it shouldn't even have to ASK you the names of the fields that are parent and child - because the relationship tells it what it needs to know.

The thing you are doing, with a combo box to populate a form, is not the more common approach. Though it isn't unheard of to do it this way. I might suggest that you could do the following: Activate the forms wizard to build a form for your table. Now go back in design mode. Remove the thing that shows the primary key. Enable the controls wizard (if not already enabled) and plotz down your selector for the primary key. The wizard SHOULD ask you what you want to do with the selection, and one of the options is to select the record associated with that key value.

OK, having done this, you can go back and just LOOK at the code involved, created by the button wizard.

What I just described to you over the last few paragraphs is how to create a bound form, quick and dirty. Trust me, though they are dumber than a box of rocks, the Access wizards can still be your friends by giving you the skeleton of something to go back and customize.

Thanks for the response.

I followed your instructions, but looking through the properties and such doesn't help me understand it much better.

Is it possible to bind two forms together without running a wizard? Trying to set the link child/parent fields won't work because my forms are unbound. Also, using your example how would I change it so that it returns a lookup instead of the foreign key, I don't see any SQL to change.

Edit: I've more or less solved my problems. Thanks for the help.