Link Child/Master Field Property

[TheInsider]

Hello,
This problem seems to occur in both Access 97 and 2000, so I assume that I am missing something here. I have a form which contains a Tab control. The forms recordset is simply a client table. The Tab control has 3 pages/tabs. On page 1 I use the fields from the form's recordsource. Pages 2 and 3 contain subforms. Page 2's subform takes orders and pertains to the orders table. Page 3's subform works similar, only with fields from yet another table. Link Child/Master fields are set to a unique user-defined alpha-numeric string value (which I'll call UniqueID here) - meaning that this is a primary key in the client table, but not an AutoNumber. The relationship is one client to many orders with cascade update and delete. The main form has a recordsource of type dynaset. When I create a new record and enter data into the first tab, thus entering information for the form's recordsource fields, when I click the next tab to enter an order (into the subform), I get a message that the Orders.UniqueID field cannot contain a NULL value. I would expect Access to cascade the value over from the Client table to the Orders table since I have assigned Child/Master link fields. This of course is not happening. Has anyone encountered this? I make it sound more complicated than it is. Basically, Access is not automatically forwarding the master field's value to the subform's child field. I tried entering only the tab 1 information, then closing and returning to the current record. The error message still comes up when I try to enter info on page 2 or 3.
Thanks in advance, Rob Marriott
rob@career-connections.net

 

[JerryDennison]

Have you made sure you haven't misspelled either the master or child field's name?

 

[TheInsider]

Hi,
No, I haven't misspelled the field names. Infact I have even tried using the subform wizard to set this up and it still behaves in this manner!

For simplicity sake, I called the above case's primary key "UniqueID", I should have called this "ClientID", it sounds missleading when I say Orders.UniqueID because there can be many ClientID's in the Orders table. I simply wanted to make it clear that "ClientID" was a primary key in the "Client" table, but not of type AutoNumber. If the field was of type AutoNumber then I could understand this problem, because Access doesn't assign the AutoNumber until after you leave the current/new record. I have a tendancy to over-complicate my questions.

However, in this case I assign the "ClientID" on page 1, which instantly assigns a value to Client.ClientID, then I go to page 2's subform and I cannot enter a record because Orders.ClientID is said to be NULL. Even when I close the form and then return to the current record, I cannot enter an order record. If someone would be willing to take a look at the database, I would be very appreciative - or if you have any more suggestions that would be great.
Thanks, Rob Marriott
rob@career-connections.net

 

[TheInsider]

I also removed every relationship in the database and I still get this error! So I know it is not my design. Hasn't anyone ever created a subform with linked child and master fields and had a problem? Rob Marriott
rob@career-connections.net

 

[TheInsider]

Solved my problem. If anyone cares here's my solution. I had to change the form's recordsource query from:

SELECT tblClients.*, tblReferrals.*, tblEquity.*
FROM (tblClients LEFT JOIN tblEquity ON tblClients.fldSocialInsuranceNumber = tblEquity.fldSocialInsuranceNumber) LEFT JOIN tblReferrals ON tblClients.fldSocialInsuranceNumber = tblReferrals.fldSocialInsuranceNumber
ORDER BY tblClients.fldLastName

to:

SELECT tblClients.*, tblReferrals.fldEBCOffice, tblReferrals.fldArea, tblReferrals.fldReferralDate, tblReferrals.fldAcceptanceDate, tblReferrals.fldEligibilityConfirmed, tblReferrals.fldParticipantStatusID, tblReferrals.fldTCName, tblReferrals.fldReferringAgencyID, tblReferrals.fldReferralID, tblReferrals.fldReferralContact, tblReferrals.fldContactTelephone, tblReferrals.fldPlacementConsultant, tblReferrals.fldComments, tblReferrals.fldAgency, tblReferrals.fldRegion, tblEquity.fldAboriginal, tblEquity.fldDisabled, tblEquity.fldMinority, tblEquity.fldSingleParent, tblEquity.fldEIReachback, tblEquity.fldAgency, tblEquity.fldRegion
FROM (tblClients LEFT JOIN tblEquity ON tblClients.fldSocialInsuranceNumber = tblEquity.fldSocialInsuranceNumber) LEFT JOIN tblReferrals ON tblClients.fldSocialInsuranceNumber = tblReferrals.fldSocialInsuranceNumber
ORDER BY tblClients.fldLastName;

To save you the eye-strain... The first query selects * (All) from the tblClients, tblReferrals, and tblEquity tables. Naturally each has a "fldSocialInsuranceNumber" field - tblClients' primary key. I changed the query to select * from tblClients and everything except the fldSocialInsuranceNumber from the other two tables. This changes the Link Master Field property from "tblClients.fldSocialInsuranceNumber" to just "fldSocialInsuranceNumber". And for some reason this makes a difference to Access! If anyone can give me an explanation; I'm very interested.
Thanks, Rob Marriott
rob@career-connections.net