[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


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.
Independent Software Vendors: 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!


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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

650 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