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.
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.


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

Suggested Solutions

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

726 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