Subform disappears when I try to add a new record to main form

[saschup]

I apologize, but I'm still inexperienced so bear that in mind. I've looked through the forums and tried some things, but still no luck.



I'm running Access 2010 with an ODBC connection to Sql Server 2008 R2. I have a form for contacts with an address subform. As I scroll through the records in the form, the correct address gets attached to the right person. However, when I go to add a new record, the address part of the subform disappears. In other words, there is no form in the subform. If I go back a record, the form itself and the data return. Previously, I had the problem where the form would show up, but I couldn't add any new data. Does anyone have any ideas?

Here is a pared down version of my tables.

tblContacts - ContactID, PK, int; lastname,nvarchar(50);firstname,nvarchar(50), etc….
tblAddress - AddressID,PK, int; ContactID, FK, AddressLine1,AddressLine2,City, etc...
tluAddressType - AddressTypeID,PK,int; Address Description, nvarchar(25)

For the subform, the source object is sfrmContactAddress - which looks like this in SQL View

SELECT tblAddress.ContactID AS tblAddress_ContactID, tblAddress.AddressLine1, tblAddress.AddressLine2, tblAddress.City, tblAddress.AddressTypeID AS tblAddress_AddressTypeID, tblContacts.ContactID AS tblContacts_ContactID, tluAddressType.AddressTypeID AS tluAddressType_AddressTypeID, tluAddressType.AddressDescription, tblAddress.AddressID, tblContacts.LastName, tblContacts.Firstname
FROM tblContacts INNER JOIN (tluAddressType INNER JOIN tblAddress ON tluAddressType.[AddressTypeID] = tblAddress.[AddressTypeID]) ON tblContacts.[ContactID] = tblAddress.[ContactID];

My subform container links are shown like this:

Link Master fields tblContacts.ContactID
Link Child Fields - tblAddress_ContactID

Truly, any ideas are greatly appreciated!

[June7]

There is no reason for the subform RecordSource to include join to tblContacts, especially with an INNER JOIN. INNER requires data in both tables for a record to show. Remove tblContacts. Also, the INNER JOIN on tluAddressType is probably an issue.

Just make the subform RecordSource simply: tblAddress

[saschup]

Thanks J - I'm sure you were shaking your head and saying, "So off-base -- sad". You were right, I didn't need all those extra joins/queries.

I changed the RecordSource to be simply tblAddress, but then it wasn't linking to the main form correctly even though I had the Master and Child Links set correctly. I ended up scrapping the subform and starting over with a wizard. The RecordSource is now tblAddress and it works correctly.

However, I still want to give the user the ability to add several types of addresses. For example, each contact might have a physical address and a different mailing address. That's why I used the tluAddressType. But now I think I see an easier way to do it -- that is using a "list" and not a lookup table. I think that will work. At any rate, you helped me solve my first issue! Thanks.

[June7]

The combobox for address type can still draw values for the RowSource list from table, just don't use the ID field. This allows greater flexibility if you want to add new types as opposed to using a ValueList in the RowSource.

[saschup]

Great point -- I didn't even think of that. Thanks!