Full Version: Foreign Key on Linked subform not filled in from main form
UtterAccess Forums > Microsoft® Access > Access Forms
GnR
I have created a new database and I am having some issues with my main form and adding 2 subforms that have 2 subforms on their subform. Essentially it is:
CODE
Inquiry->InquirerSubform ->AddressSubform
                               \->PhoneNumbersSubform
           \->ContactSubform ->AddressSubform
                                \->PhoneNumbersSubform

The form is currently a bound form that I would like to use for data entry. I would like the user to be able to enter all the information on one tabbed form.
Inquiry to Inquirer is a one to many relationship with one Inquirer for each Inquiry. Inquiry to Contacts is a many to many relationship as each inquiry can have many contacts and contacts can be related to many inquiries.
My tables look like this:
tblInquiry
------------------
pkInquiryID
fkInquirerID
...misc related Inquiry info...
tblInquirer
------------------
pkInquirerID
Name
MiddleName
LastName
...etc...
tblInquiryContacts
-----------------------
fkInquiryID
fkContactID
Role
tblContacts
----------------
pkContactID
Name
MiddleName
LastName
...etc...
tblPhoneNumbers
---------------------
pkPhoneNumberID
fkContactID
PhoneType
PhoneNumber
tblAddresses
-------------------
pkAddressID
fkContactID
Address
City
...etc...
I have created the subforms and they work excellent for inputting contacts and their addresses and phone numbers. They relate correctly when they are used standalone.
When I add the subforms to the main form as a subfrom control, my fkInquirerID is not updated in tblInquiry when I enter a new record or try to update an existing record. I can enter the information but the field is not filled in so its unrelated. The same thing happens with the tblInquiryContacts. fkContactID is not updated from the form with the new ContactID as a new contact is created.
Writing this out has just given me an idea that the ContactID or InquirerID is not being committed to their tables as I type it into my form, and I need to code it to committ before leaving the subform. I will investigate that and report back. If you have any other ideas please express them. Thank you.
GnR
This problem has been solved by the Microsoft Access Wizard. I rebuilt the forms from scratch using the wizard. It turns out I was missing some inner joins in the underlying queries. Those inner joins are not created when buiding the forms manually. From now on I'll start with the wizard and edit those forms as needed.
JVanKirk
grnlive,
id you build relationships in the Relationship window?
Owouldn't use the form wizard to build sa form, as personal preference, I just don't like it. If you add a subform to a parent form though, usually there will be a wizard there to step through which will pick the join for you or allow you to specify another, most times, the default is right if you built your relationships.
Just my 2 pennies worth.
Also, Welcome to UA!! Posted and resolved all before anyone could welcome you...COOL!!
Have a great day!!
GnR
I initally had all the relationships set out in the relationship window. The subform was building correctly with the right related fields, but the problem was my query on the main form. I put "Inquirer" into a subform on the main form and I was wrong doing that. Using the form wizard just setup an inner join and layed out the Inquirer table fields on the main form. I was not doing that. After that was setup correctly the rest started to work as advertised.
agree the form wizard builds ugly forms and I end up having to redo them completely also. But it was a good way to diagnose my problem.
Thank you for the warm welcome. I have been researching many problems with these forums for a couple months and its been terrific.
Thank you UA!
JVanKirk
gnr...yes, I will give you that the wizard will setup everythng for you so you can tear it apart and see how tihngs should be built and then change them so they are visually appealing. Very glad you got it working, AND know what was wrong!!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.