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
Medium Priority
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.
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!


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 1500 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…
Suggested Courses

770 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