Why can't I make a form with subform using Wizard?

D

Doug Roberson

I usually use Access to download and combine things, so I don't know much
about forms. Why would the Form Wizard allow me to make a form with subform
when one of two tables is the subform...but not when I try to switch them so
that the OTHER table is the subform instead?
 
S

Steve Schapel

Doug

Normally, the data in a subform and the data in a main form are related
one-to-many, with the 'many' side data on the subform. For example, if
you have a database with Streets and Houses, there is a one-to-many
relationship between these data elements. So you might have a main form
with Streets, and a subform which shows Houses data for each Street.
You wouldn't have it the other way around... wouldn't make sense.
 
R

Ray Turnbull

I'm trying to do something similar.
1)What if we want to show a list of ALL houses and as we
select each house, show details of the relevant street.
2) What if we want to show a list of Orders, and for each
order selected bring up details of the Customer.
3) What I want to do is to show a list of records and for
each record selected show details of that record, similar
to Microsoft Money.
 
S

Steve Schapel

Ray,

Yes, this type of thing is a very common requirement. There are a
number of approaches that can be taken to this, depending on how you
want the interface to appear.

One example is you can open a form showing the "parent" information.
Let's say you have a form with a list of Orders. On that list, visible
or invisible, will be a reference to the customer, most likely a
CustomerID or some such. So, you can have a procedure run on the Click
event of a Command Button on that form, something like this...
DoCmd.OpenForm "Customers", , , "CustomerID=" & Me.CustomerID
.... which will open a second form to display the full details of the
customer who ordered the current order.

A variation on the theme would be to have the Order form and the
Customer form both as subforms of a main unbound form. A way this is
sometimes done is to put them on two pages of a Tab Control. Then,
there can be code on the Current event of the Orders form like this...
Me.Customers.Form.RecordSource = "SELECT * FROM Customers WHERE
CustomerID=" & Me.CustomerID
 
R

Ray Turnbull

Thanks
Worked Fine

Steve Schapel said:
Ray,

Yes, this type of thing is a very common requirement. There are a
number of approaches that can be taken to this, depending on how you
want the interface to appear.

One example is you can open a form showing the "parent" information.
Let's say you have a form with a list of Orders. On that list, visible
or invisible, will be a reference to the customer, most likely a
CustomerID or some such. So, you can have a procedure run on the Click
event of a Command Button on that form, something like this...
DoCmd.OpenForm "Customers", , , "CustomerID=" & Me.CustomerID
.... which will open a second form to display the full details of the
customer who ordered the current order.

A variation on the theme would be to have the Order form and the
Customer form both as subforms of a main unbound form. A way this is
sometimes done is to put them on two pages of a Tab Control. Then,
there can be code on the Current event of the Orders form like this...
Me.Customers.Form.RecordSource = "SELECT * FROM Customers WHERE
CustomerID=" & Me.CustomerID
Click to expand...
 
Top