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!
6  1862 
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.Househo ldID (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!
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__ thedatabaseplac e.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.Househo ldID (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!
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__ thedatabaseplac e.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.Househo ldID (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! >
>
> 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
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__ thedatabaseplac e.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.Househo ldID (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!
>
>
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Ray |
last post by:
I have a main form with a subform. The main form has a combo box which
lists all the clients which in turn are displayed in the subform.
The subform is bound to the combo box and all work well. however, I
would like to be able to add a new client in the subform but this is,
of course bound to the combo box. Can anyone tell me if there is a way
to enter a new client in the subform with out receiving an error
message?
TIA - Ray
|
by: Alienz |
last post by:
Hey all. if anyone is REALLY bored please help moi. I am new to
access stuff and am probably missing something obvious here.
I have 2 tables that are linked via "number" in the relationship area.
The relationship is many to many. One is a master form and the other
I dragged in to be a subform. I also created a child/master link
based on the common "number" field. So, it's a generic subform pretty
much.
|
by: Sabra D via AccessMonster.com |
last post by:
I have a db with 3 tables, owner, lessee, and tract, tract is the main table
and has two lookup fields to find the owner and lessee info.
My problem - i have a form with the owner info on top and the tract info
(including lessee) in a subform below. When i change the lessee on the form,
it changes all of them in the entire database. I looked but couldn't find
anything about this on your site. I'm at a loss, i'm sure it's something i
set...
|
by: kkrizl |
last post by:
I have a form that displays general information about an alarm permit
location. There's a subform that shows detailed information about
burglar alarms that have gone off at the location. When a new alarm is
entered on the subform, I want to print a report that shows the general
information and the alarms that were entered.
The record source for the form is the Mailing List table (key is Alarm
#).
The record source for the subform is the...
|
by: tlyczko |
last post by:
I am working on an audits database.
The main table, Audits, has an AuditID primary key.
Another table, 1:1 relationship, ProgramAudits, has AuditID as a
foreign key.
I have a master/main form for Audits data, and a child/sub form for
ProgramAudits data.
I put a subform for ProgramAudits onto the Audits table form, and the
following code in the OnCurrent event to grab the AuditID field from
| | |
by: John |
last post by:
I have two tables in a 1:M relationship- the parent has 5 fields in the
primary key and the child 6 (these are actually pretty far downstream in
a complicated ER model, but the problem is between these two). I set up
the relationship with no problem- referential integrity and left outer
join. I have created two forms- one for the parent and one for the
child table. I want to import the child table's form as a subform on
the parent...
|
by: Ecohouse |
last post by:
have a main form with two tabs, and each tab has a subform. The first
tab's subform refers to the mainform (this works). The second tab's
subform refers to the identity key of the first tab's subform.
I can't get the second subform to show the proper records. I have
used the child and masterlink fields. I have set the recordsource of
the second subform to include the identity key field on the first
subform.
This an accounting...
|
by: Megalog |
last post by:
Hey guys.. my turn to ask a question:
I'm having a weird issue with a form I've reworked. This form has a combo box, which when used is changing the recordsource of a subform. This subform has no Master/Child relationship set to the main form. Specifically the problem seems to be that everytime the recordsource of the subform is changed, the subform object itself tries to set a default Master/Child value (both fields fill with the "ID"...
|
by: jonceramic |
last post by:
Hi,
I have a 2 subform form, which uses the selection of a row in subform
1 to show a set of rows in subform 2.
I have my queries pointing directly to fields on my form to choose
what data is shown.
However, when I first populated this form with my subforms, I was dumb
(apparently) and had it accidentally pick "List_ID" as my child/master
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
