In a subform how do I get the field that is the link to the main form to automatically fill with its matching value from the parent form?

Posted on 2008-09-29
Last Modified: 2013-11-28
I have a form, ParcelDetail, that has a subform, TitleChain, which lists all of the RecordingNum for the current ParcelNum on the main form. From here, I have a button that will open another form, TitleChainView, for me to view the documents and their details of all the documents listed in that subform on the previous form.

So, I want to list the following fields from my DocInfo table for every RecordingNum listed in the [Title Chain:] subform of the form ParcelDetail: RecordingNum, Instrument, DocumentCategory, DocumentDate, RecordingDate, Grantor, Grantee, LegalDescription. The following query, used as a filter on my table do display that set of documents from DocInfo returns the correct number of records...

SELECT CurrentParcelTitleChainQuery.RecordingNum, CurrentParcelTitleChainQuery.Instrument, CurrentParcelTitleChainQuery.DocumentCategory, CurrentParcelTitleChainQuery.DocumentDate, CurrentParcelTitleChainQuery.RecordingDate, CurrentParcelTitleChainQuery.Grantor, CurrentParcelTitleChainQuery.Grantee, CurrentParcelTitleChainQuery.LegalDescription, CurrentParcelTitleChainQuery.ParcelNum
FROM CurrentParcelTitleChainQuery;

...but it only shows information for documents that have already been entered. If it hasn't been entered, I can't get it to carry the RecordingNum, which is the linking value to the [Title Chain:] Subform and drop it into its related RecordingNum field.

I have another issue related to this, in that I have a field on a form that I want to enter the recording number, click a button, and go to a form to edit the document related to that number. However, if I haven't already added that particular document, I want the form that pops up to automatically carry the number that I searched on into the appropriate field of the new record that it creates upon opening the form. I suspect that these are related issues, and that it has something to do with using an inner or outer join...?
Question by:littleking4376
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
LVL 61

Expert Comment

ID: 22601976
What is the datatype of RecordingNum?

Ideally you should define your master table's primary key to be an autonumber field rather than a user entered field.  The advantages are:

- No worry about handling error messages from duplicates being entered
- No data entry errors to worry about.
- The child records will automatically fill with an autonumber if the form and subform are linked on this field, with no extra effort on the developer's part.

That said, if you must link on a user-entered field you need to explicitly populate the foreign key in the subform.

This is done with code in the Current Event:

If Me.NewRecord = true then me.recordingnum = me.parent.recordingnum

You second issue should really be addressed in a seperate thread.

Author Comment

ID: 22608196
The RecordingNum is a text field, as it can contain numbers and/or alpha characters. I think I understand what you're saying about the autonumber, but I'm not sure how I can apply something like that to my existing structure. I'm not afraid to play around with it, though, because I would ultimately like to have all of my tables cascade update all related fields throughout if it is possible. I have accomplished basic functionality with the relationships and structure that I currently have, but I still run into areas of difficulty when I try to expand it a little further or try to add a new feature. I realize that some base table changes may be necessary, but am unsure of how to proceed. I posted a question with a shot of my tables and relationships once, but after a long time I finally got one response from someone who just wanted to say that my question was too broad for this forum. I have fine-tuned it a little since then, but I still haven't accomplished the solid updateable relationships that I want. The advantages that you list above are what I've been striving for all along. I've learned a lot of SQL and VBA programming in the process, but I feel from what you're saying that the weakness of my database lies in the foundation, which is the tables. If this is so, then my question won't really be relevant, so shall I post my tables here also for review? Why not...  (I added a screenshot and some brief descriptions of all the tables. Feel free to review or not for this question, I will probably use it for future issues, too.)

I'll ask the other question elsewhere....   :)

Thanks in advance for any time you spend on this, I really appreciate it!
LVL 61

Expert Comment

ID: 22611215

I'll take a look at this tomorrow night (US East Coast), and see if I've got any general suggestions.  In the meantime, would you be able to post the actual .mdb file?  If you are storing it in A2K7 format, you can upload .accdb files by changing the file extension to .txt or one of the other allowable extensions.
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.


Author Comment

ID: 22618705
Okay, you have already inspired me to start a new test database, with the tables all build to have a primary key, and nearly all of the relationships are cascade updateable. The only issue seems to be related to the original question. Whenever I have a table that is linked to the main table by an inbetween table (necessary for many to many relationship), even if I include all fields from both tables, it doesn't prefill them automatically. Basically, I'm saying that I have the same problem, but that it has evolved slightly due to the changes in the table structure.

I was going to add my new test database for you to take a look ate, but it is too small. If I have time later, maybe I'll zip it and add it to the mix. For now though, I'm adding a shot of the relationships as I have made the improvements. Also, there is a shot of the form where I want everything displayed, and some notes to hopefully help illustrate my issue. I am essentially having link up problems with the DocInfo table, and the Accounts table. It would be nice to show the related information from these two tables on the same form also, but the problem is that they seem too far removed. If we can solve this issue, it should work for links to both tables.

Author Comment

ID: 22618727
...and here's the form sample...
LVL 61

Accepted Solution

mbizup earned 500 total points
ID: 22846445

I lost track of this question.  Where do you stand with it?

Author Comment

ID: 22980633
Sorry, I didn't mean to leave this unattended for so long, but I've been unable to work on that database for some time until only earlier this morning. It has evolved so much since I initially answered the question that it's no longer relevant. Is there a way I can just award the points for the attempt and drop the question?

Author Closing Comment

ID: 31501370
some of your comments did help me overall with the way I rebuilt my tables and I've gotten a lot farther with it, but it works differently enough now that the original question doesn't even apply. Thanks!

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question