Form/subform error You tried do assign a null value to a variable.

[Jorge Ribeiro]

Hello

I've two tables in DB CustomerInfo and CustomerAddress
On the first I've set a link to the second that has referencial integraty...
a Customer has (one or zero) address

I made a form for customerinfo and a subform for customeraddress

I linked both forms using AddressId, as it is specified in db relations.

This error "You tried to assign a NULL value to a variable that is not a
variant type" emerges when i ran the form and try to fill out one of the
subforms fields, after entering
all data for main form (customers info)...


Neither one of the forms has any kind of code in it.

I'm trully an Access newbie... be gentle!

best regards

Jorge

 

[John Spencer]

I suspect that you should have linked the tables on CustomerID and not AddressID.

One Customer has zero to many addresses. In your specific case (zero or one).

Your table structure should look something like:
CustomerInfo
-- CustomerID
-- LastName
-- FirstName
-- <Other Fields specific to customer>
(No AddressID field should be in this table)

CustomerAddress
-- AddressID
-- CustomerID
-- StreetAddress
-- City
-- State
-- PostalCode

You should then link the two tables on CUSTOMERID.

You could get away with storing the address information in the CustomerInfo
table if you are never going to have more than one address associated with a
customer. If you are going to store Billing Address, Shipping Address, a
correspondence address, etc. Or addresses over time, then you should
definitely have a CustomerAddress table.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Jorge Ribeiro]

Tanks John for your answer

Linking AddressTable to CustomerTable by CustomerId like you proposed would
prevent me to reuse Address table in other scenarios, like for instance
Supplier's Address. For that matther i'm kind of relutant to get customer id
into address table.

I could always create a relation table Customer <-> Address and change that
relation
to many-to-many and enforce multiplicity in business rules.

If you agree, in the last scenario, how would it work in form/subform?
If not how can i work arround this? I have a customers table, a suppliers
table,
and both can have an address...

(sorry for my english... )

 

[John Spencer]

You definitely need to link on customerId. You can do it with a many-to-many
table or you could have multiple fields for linking in the address table-not
normalized.
Addresses
-- AddressID
-- CustomerID (can be null)
-- SupplierID (can be null)
-- <fields with address details>

Although the problem then arises if you want to assign the same address to
multiple suppliers or to multiple customers you would need a many to many
table again.

I'm not sure how I would handle the linking in a form-subform scenario using a
many to many table. I would have to experiment to figure that one out; I
can't recall that I've done that.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Jorge Ribeiro]

Hello John

I made a form with customer data and inserted a subform with address data
On Current event of customer form i must check if this is a new record... if
so i must create a new record in address subform and link customer to that
record...

is a ugly workarround i know but it might work

can you tell me how can i figure out if the present customer record is a new
one?