insert record - linked tables, combobox on a subform

[wdsnews]

I'm struggling with something that was pretty easy with WinParadox. Suppose
I have three tables; People, Phone, and PhoneType. On my People form, I
want a subform in which the user is able to add more phone numbers to the
Phone table. I want the user to see two fields; the first field allows a
phone number, and the second field allows a 'phone type' lookup from the
PhoneType table, that results in placing the PhoneTypeID in the new Phone
record. However, all my attempts with the wizards leaves me with a subform
in which efforts to add a phone number in the Phone table result in adding a
record to the PhoneType table instead.

There is a one-to-many relationship from People to Phone, and a one-to-one
relationship from Phone to PhoneType, (i.e. PhoneType has 8 records of two
fields; '1, Home Phone', '2, Work Phone', '3, Mobile Phone', etc.) There
are three obvious key fields linking the tables; PeopleID, PhoneID, and
PhoneTypeID. The Phone table has four fields; 'PhoneID[Key]', 'PeopleID',
'PhoneTypeID', and 'PhoneNo'.

How can I address this or what am I not understanding?

 

[Jackie L]

Your main form should be based off table People (or a query based off that
table). The subform should be based off table Phone (or a query with that
table).

Create the two forms above and while in the main form, click on the icon for
inserting a subform. Your child and master field links on the subform should
be PeopleID.

The two fields on your subform, Phone and PhoneType both need to be from the
Phone table but the field PhoneType can be a combo box based off the Phone
Type table. It should have its control source the field PhoneType.

Hope this helps. I do question the one-to-one relationship you described
between Phone and PhoneType (It sounds more like a one to many). A one to
one relationship should not exist. If it is truly one-to-one, then that
information should be in the same table.

Jackie

 

[wdsnews]

Jackie,

Thank you for your help. You're right about my one-to-one faux paux.

Ok. ...got it. Here's what I didn't understand. I didn't realize you
could just layout the fields you need on a subform, and then simply choose
datasheet view to automatically organize them into a table. Each field can
be uniquely defined with a source, combobox, drop-down, etc.

I like it. Thanks for your help.

Your main form should be based off table People (or a query based off that
table). The subform should be based off table Phone (or a query with that
table).

Create the two forms above and while in the main form, click on the icon
for
inserting a subform. Your child and master field links on the subform
should
be PeopleID.

The two fields on your subform, Phone and PhoneType both need to be from
the
Phone table but the field PhoneType can be a combo box based off the Phone
Type table. It should have its control source the field PhoneType.

Hope this helps. I do question the one-to-one relationship you described
between Phone and PhoneType (It sounds more like a one to many). A one to
one relationship should not exist. If it is truly one-to-one, then that
information should be in the same table.

Jackie

I'm struggling with something that was pretty easy with WinParadox.
Suppose
I have three tables; People, Phone, and PhoneType. On my People form, I
want a subform in which the user is able to add more phone numbers to the
Phone table. I want the user to see two fields; the first field allows a
phone number, and the second field allows a 'phone type' lookup from the
PhoneType table, that results in placing the PhoneTypeID in the new Phone
record. However, all my attempts with the wizards leaves me with a
subform
in which efforts to add a phone number in the Phone table result in
adding a
record to the PhoneType table instead.

There is a one-to-many relationship from People to Phone, and a
one-to-one
relationship from Phone to PhoneType, (i.e. PhoneType has 8 records of
two
fields; '1, Home Phone', '2, Work Phone', '3, Mobile Phone', etc.) There
are three obvious key fields linking the tables; PeopleID, PhoneID, and
PhoneTypeID. The Phone table has four fields; 'PhoneID[Key]',
'PeopleID',
'PhoneTypeID', and 'PhoneNo'.

How can I address this or what am I not understanding?