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?

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...?
Who is Participating?
mbizupConnect With a Mentor Commented:

I lost track of this question.  Where do you stand with it?
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.
littleking4376Author Commented:
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!
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.


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.
littleking4376Author Commented:
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.
littleking4376Author Commented:
...and here's the form sample...
littleking4376Author Commented:
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?
littleking4376Author Commented:
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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.