Access 2010:  Link subform history to current record when ID numbers are different

mbmartin0409
mbmartin0409 used Ask the Experts™
on
In my access DB i have a subform from a history file of actions.  The primary key (callID) is linked to an old ID number (OldID) contained in a table, tblInquiryFile.  The current record in the input form comes from a new ID number in that table,  [ID].  currently, the subform history shows all records in the history table.  I only want it to show those where there is a match between the oldIDs Can anyone help with language that would return to the subform the current record and history for that record where the oldIDS match?  I'm guessing it would require some kind of selection based on a combination of multiple id fields.  help?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
In design view, select the subform container. Make sure you select the container and not in the subform itself. In the properties window you should see these 2 properties: Link Master Fields and Link Child Fields. See my attached image. In your db, do these have data?
MS-Access-2010-subform-link-fiel.png

Author

Commented:
Hi Lee - Yes - there is a lot of data. 18,000 records in tblInqiryFile (what the current form should be returning and is on the main tab of the form and 65000 call history records (from tblcalls) for those in the subform.  All calls are currently showing in the subform.

Author

Commented:
Responding again --- You definitely pointed me in the right direction!  --- I  successfully linked the call table to the current record on the form using the master and child fields.   BUT --- since those linked ID's are "old ones" - and not new ones, I can't add a new call record to the file.  I'm getting the following error: "Index or primary key cannot contain a null value" --- this is happening, i think, because to show this history i'm linking the current record of the primary input form to the subform via "oldInquiryID" from the underlying primary table with the "OldInquiryID" from the subform underlying table "tblcallhistory" .... those numbers are just numbers.  the New ID, [ID] in tblInquiryFIle is the primary key of that table and [callID] is the primary key of the subform table, tblcallhistory.  

Now what? :-)

You've been helpful to this point immensely --- hopefully we can resolve this!
Michael.
Commented:
Sorry my response is slow - busy today.

So I can better understand your db structure for these 2 tables, can you post a sanitized (no confidential data) copy of your db? It sounds as if you're not using the correct PK-FK ids for your table and form/subform relationships.

Author

Commented:
It's ok --- you pointed me to the right place last night.  It's all working now!!   --- well mostly :-) ---    Now i'm I'm on a new path to find out if I can use a button on my Access Form to Open Outlook Calendar when an appointment is made..........  

thanks for your help!!
Michael

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial