Error In Subform When Adding Record

[rdeleon]

I have a main form bound to a "Contact" table and a subform bound to an "Address" table. The subform is linked to the main form via an AddressID field where Contact.AddressID = Address.AddressID. All seems to work well, except when I have a situation where the Contact table has a null AddressID field. When I try to add an address in the subform I receive a "You tried to assign a Null value to a variable that is not a Variant data type". The AddressID field is datatype Number.

Here are some of the property values on the subform:

AllowEdits, Deletions, Additions = Yes
DefaultView = SingleForm
NavigationButtons = No

I have not used subforms very often. Any ideas?

Rene'

 

[batteam]

How are Address ID's assigned (entered) in the Contact table? Why are some there, then on another occassion there's a Null? Seems like the AddressID in the Contact table should be an AutoNumber, or if it comes from someplace else, then you should ensure its there before you attempt to add a new Address record. "xxxxID's" are usually Primary Keys and thus should (can) never be Null.

Hope this helps.

 

[rdeleon]

The Contact Table has an AddressID field (not primary key, foreign key) that links to the Address table via Address.AddressID (primary key on Address table).

Some Contacts have addresses, some don't,hence the null values in Contact.AddressID where the address is not known.

I have made some headway. I got the form/subform updating and adding addresses but I still have a problem. I am getting the following error message: "You can't assign a value to this object" when I first input into the subform text fields. Once I select the OK button on the error message, it seems to work fine. I think it has something to do with the linking between the main/subform, but I haven't stumbled on the solution.

Anyway, if you can think of anything to look at, I would appreciate it.

Rene'

 

[rdeleon]

I think I figured it out. The error is coming from the fact that my subform link (childlink) is an autonumber (PK on Address table). I don't really like my solution, but it works.

I added a new field AddressID2 to the Address table and copied the AddressID to the AddressID2 field. I then used AddressID2 as the childlink in the subform. Of course I have to make sure I update AddressID2 everytime a new record is added to keep it in sync.

Let me know if this approach goes against any standard db practices. Although I play around alot with databases, I do not consider myself a programmer.

Rene'

 

[lespaul]

Since you say:

Some Contacts have addresses, some don't,hence the null values in Contact.AddressID where the address is not known.

I would expect a set up like:

Contacts
ContactID (PK - Autonumber)
FirstName
Lastname


Addresses
AddressID (Autonumber)
ContactID (FK - Long Integer)
Street1
Street2
CIty
State
ZipCode

You shouldn't have ADDRESSID in the Contact table, you should have CONTACTID in the address table.

HTH

Leslie

 

[batteam]

Since you have a situation where a contact might not have an address, but every address has a contact, I agree with lespaul. If you will always have contacts, but not necessarily always have an associated address with each, then use a ContactID column as your relationship column to your address table. Good luck.

 

[rdeleon]

I would agree with you if I only had the Contact and Address tables, but the problem is that it appears the Contact table was added later (I didn't create the db). Here is the whole picture:

Company Table:
CompanyID
(company specific fields)

Address Table:
AddressID
CompanyID
(Address specific fields)

Contact Table
ContactID
AddressID
(Contact specific fields)

Scenarios:
1. Contacts can only have one address

2. Company can have multiple addresses.

3. Company can have multiple contacts on a particular address

4. Contact has an address, address is linked to Company

5. Contact has an address, but is not affiliated with a company

It is the last situation that causes confusion, because if a contact was forced to have a company, we would not need a contact/address relation.

Anyway, it might be klugey, but it's working right now.

Rene'