Subform that reverses master/child relationship?

[Phil M]

I have two tables: Persons and Households, in a many-to-one relationship (many persons in one household). Persons has householdID as FK. The problem is that I
want my form to use Persons as the master. That is b/c the user interface is entirely person-oriented -- searching, creating new records, etc. is done person
by person, not household by household.

But when I try to set up the form with this reversed relationship, I get a "You tried to assign a Null value to a variable that is not a Variant data type"
error (upon entering data in the subform). I tried reversing the link fields, but access didn't seem to like that. Any thoughts would be much appreciated!

[Scott McDaniel]

Unless I'm missing something, you have no need to use a subform. If you base
your form on the Persons table and include a combo that is bound to
Persons.HouseholdID (and then use your Households table as the .RowSource of
the combo), that would allow you to add choose the household to which each
person belongs.
--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

"Phil M" <sz************@yahoo.com> wrote in message
news:11*************@news.rcn.com...

I have two tables: Persons and Households, in a many-to-one relationship (many persons in one household). Persons has householdID as FK. The problem
is that I want my form to use Persons as the master. That is b/c the user interface is entirely person-oriented -- searching, creating new records, etc. is done
person by person, not household by household.

But when I try to set up the form with this reversed relationship, I get a "You tried to assign a Null value to a variable that is not a Variant data
type" error (upon entering data in the subform). I tried reversing the link fields, but access didn't seem to like that. Any thoughts would be much
appreciated!

[Phil M]

Yes, that works if the household already exists, but I'd like to be able to create a new household on the same form, if possible. In other words, the user
doesn't need to know that "household" is normalized in the table model.

On Mon, 5 Jan 2004 17:58:54 -0500, "Scott McDaniel" <scott@NOSPAM__thedatabaseplace.net> wrote:

Unless I'm missing something, you have no need to use a subform. If you base
your form on the Persons table and include a combo that is bound to
Persons.HouseholdID (and then use your Households table as the .RowSource of
the combo), that would allow you to add choose the household to which each
person belongs.
--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

"Phil M" <sz************@yahoo.com> wrote in message
news:11*************@news.rcn.com...

I have two tables: Persons and Households, in a many-to-one relationship

(many persons in one household). Persons has householdID as FK. The problem
is that I

want my form to use Persons as the master. That is b/c the user interface

is entirely person-oriented -- searching, creating new records, etc. is done
person

by person, not household by household.

But when I try to set up the form with this reversed relationship, I get a

"You tried to assign a Null value to a variable that is not a Variant data
type"

error (upon entering data in the subform). I tried reversing the link

fields, but access didn't seem to like that. Any thoughts would be much
appreciated!

[Jack MacDonald]

Two things come to mind:
1. I built a 5-level hierarchy (company, region, division, location,
person), and encountered the same problem as you, only more so.
Solution there was to force the user to start at the top (not
particularly elegant, as you have mentioned.)

2. Combo boxes (i.e., the one used to select the correct Household)
have a NotInList event that can be used to open a new form. If the
user tries to assign the person to a non-existant household, then your
program could append the new household to the appropriate table, open
the appropriate household form (modally??) and requery the combo box
when the user finishes entering the household information. If the
household does not require any information besides its name, then you
would not need to open the form.

I would be curious to learn whether there is a more elegant solution.
On Tue, 06 Jan 2004 00:37:26 GMT, Phil M <sz************@yahoo.com>
wrote:

Yes, that works if the household already exists, but I'd like to be able to create a new household on the same form, if possible. In other words, the user
doesn't need to know that "household" is normalized in the table model.

On Mon, 5 Jan 2004 17:58:54 -0500, "Scott McDaniel" <scott@NOSPAM__thedatabaseplace.net> wrote:

Unless I'm missing something, you have no need to use a subform. If you base
your form on the Persons table and include a combo that is bound to
Persons.HouseholdID (and then use your Households table as the .RowSource of
the combo), that would allow you to add choose the household to which each
person belongs.
--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

"Phil M" <sz************@yahoo.com> wrote in message
news:11*************@news.rcn.com...

> I have two tables: Persons and Households, in a many-to-one relationship

(many persons in one household). Persons has householdID as FK. The problem
is that I

> want my form to use Persons as the master. That is b/c the user interface

is entirely person-oriented -- searching, creating new records, etc. is done
person

> by person, not household by household.
>
> But when I try to set up the form with this reversed relationship, I get a

"You tried to assign a Null value to a variable that is not a Variant data
type"

> error (upon entering data in the subform). I tried reversing the link

fields, but access didn't seem to like that. Any thoughts would be much
appreciated!

>
>

[NB]

> 2. Combo boxes (i.e., the one used to select the correct Household)

have a NotInList event that can be used to open a new form. If the
user tries to assign the person to a non-existant household, then your
program could append the new household to the appropriate table, open
the appropriate household form (modally??) and requery the combo box
when the user finishes entering the household information. If the
household does not require any information besides its name, then you
would not need to open the form.

This is the right approach. In one of my apps, there is a similar
scenario: a patient may have several admittance to hospital, but most
operations are per admittance. So the form are based on admittance
table, and user just pulls the patient from the patient table to
assign to that admittance. If the patient is new then a pop up form
will allow user to add new patient.

NB

[Phil M]

I would be curious to learn whether there is a more elegant solution.

Well, from a UI standpoint, it would be nicer to keep all the fields on one page -- I think I could do this if I were willing to undefine the FK
relationship and manage it manually in VB code. But I'm not sure it's worth the effort. Also considered denormalizing households and putting all the fields
into Persons... Then the UI becomes really simple.

Thanks to everyone who responded for their thoughts!

On Tue, 06 Jan 2004 00:37:26 GMT, Phil M <sz************@yahoo.com>
wrote:

Yes, that works if the household already exists, but I'd like to be able to create a new household on the same form, if possible. In other words, the userdoesn't need to know that "household" is normalized in the table model.

On Mon, 5 Jan 2004 17:58:54 -0500, "Scott McDaniel" <scott@NOSPAM__thedatabaseplace.net> wrote:

Unless I'm missing something, you have no need to use a subform. If you base
your form on the Persons table and include a combo that is bound to
Persons.HouseholdID (and then use your Households table as the .RowSource of
the combo), that would allow you to add choose the household to which each
person belongs.
--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

"Phil M" <sz************@yahoo.com> wrote in message
news:11*************@news.rcn.com...
> I have two tables: Persons and Households, in a many-to-one relationship
(many persons in one household). Persons has householdID as FK. The problem
is that I
> want my form to use Persons as the master. That is b/c the user interface
is entirely person-oriented -- searching, creating new records, etc. is done
person
> by person, not household by household.
>
> But when I try to set up the form with this reversed relationship, I get a
"You tried to assign a Null value to a variable that is not a Variant data
type"
> error (upon entering data in the subform). I tried reversing the link
fields, but access didn't seem to like that. Any thoughts would be much
appreciated!
>
>

[Janross]

Hi, Phil;

My solution has been to use the combo box to choose household, and if it isn't
on the list, give them a button (or you could use the notInList property) to
pop up a dialog to create the household (then don't forget to refresh the combo
after you close the dialog!). It works and it's pretty intuitive to the user.
I wouldn't go the denormalized route--will come back to bite you later.

Good luck.

Jan

I have two tables: Persons and Households, in a many-to-one relationship
(many persons in one household). Persons has householdID as FK. The problem
is that I
want my form to use Persons as the master. That is b/c the user interface is
entirely person-oriented -- searching, creating new records, etc. is done
person
by person, not household by household.

But when I try to set up the form with this reversed relationship, I get a
"You tried to assign a Null value to a variable that is not a Variant data
type"
error (upon entering data in the subform). I tried reversing the link fields,
but access didn't seem to like that. Any thoughts would be much appreciated!

Jan Stempel
Stempel Consulting