Link to home
Start Free TrialLog in
Avatar of mmgordon84
mmgordon84

asked on

Subform updates checkbox in subtable for each customer, but CustomerID disappears and relationship is lost

I have a database subform that allows me to check a box in the subform that I have completed Action X for customer X on the main form. The (sub)table linked to the subform shows the checkbox as checked, but the CustomerID is not populated.  I have a relationship established between the CustomerIDs in both main and (sub) tables. When I return to the Main and Subform, the check doesn't appear even though I used the form a minute ago to populate the sub(table).  I need help reestablishing the relationship with the two CustomerIDs. I have already tried breaking and reestablishing the relationships.

Thanks in advance for your help!
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

mmgordon84,

If this is a true MainForm/Subform setup, if you open the main form, it will display the Customer and all their records in the subform. So when you say "CustomerID is not populated" I am confused.

Isn't the customer displayed ("Populated") when you open the form?

Here is what might be happening:
If you open the Main form and click the         >*    (New Record) button on the MAIN form, then immediately go into the subform and type a new record(in the subform).

Then the CustomerID will NOT appear!

Is this what is happening to you?

If so, you should always find the Correct Customer first, THEN enter a new record in the subform.

Also you should not need the CustomerID in the subform. It is implied from the main form.

Let me know if this helps.
Make sure to lock the CustomerID in the subform and in the main form.  They should not be editable.  If CustomerID on the main form is editable, you don't have a Primary Key established in your main form's underlying table.  That could cause strange stuff.  If you have a link between the two forms there should never be a blank CustomerID field.  Check your subform properties to see if you have the subform in Data Entry mode.  It should not be.  One last thing....you should have a relationship set up in the Relationships window.  You have a one to many relationship that should be properly linked and set up for referential integrity so that the relationship will never get messed up.
Avatar of mmgordon84
mmgordon84

ASKER

Boag2000:
From your comments I apparently do not have a "true" form/subform setup since the data does not appear when I open the form and subform (after just entering the data via the same form). I am using the subform to enter both see previously entered data and add data into a (sub) table linked as a child to the parent by PtID.  WHen I enter data, such as checking a checkbox, this action appears in the underlying table, but it is not linked to anyone: the CustomerID field is blank in the underlying table.  The ID is not in the subform, since as you state, the relationship is implied from the main form, but there must be something wrong somewhere with the relationship since the entered data doesn't reappear on the subform later on (although the information is in the table strangley unlinked to the CustomerID). My other subtables work fine and I cannot detect a difference.

Arji:
I cannot lock the ID since the form is used to both view and add customers.  
What is the importance of marking both IDs in both tables as primary keys? I cannot do this for the subtable as there will be multiple entries.
How do I check if subform is in dataentry mode? I cannot discover why my other forms work and this one does not.
I have tried selecting referential integrity, but this also has not helped.

I will reexamine your comments tonight. Do you have any other ideas?  Thanks so much for your willingness to help!
SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
mmgordon84,

So what was the final outcome?