Issue with foreign key field of subform

[csh2013]

I have a database that's tracking funds owed by customers and there is some erratic behavior occurring. To put this in a sort of real-world scenario, imagine I'm paying bills on behalf of people and those people, in turn, owe me money. So, I might list John Doe owes me for Netflix, Comcast, Amazon, etc. So right now there is a table called "Collections" that contains the list of names of the people that owe money (John Doe in my example). In that table, the customer name is the only field and it's also the primary key. It has a one-to-many relationship set to a Customer Name field in a table called "Company". That table lists all the bills (Netflix, Comcast, etc.).

Everything works until I create a form on the "Collections" table and try to link the "Company" information. I have it properly tied with the Master and Child fields, but every time I try to add a new bill in the subform, Access is populating the foreign key field with the words "Customer Name". I thought something must have been tied incorrectly since it's putting the words "Customer Name" in the Customer Name field, but when I re-created the form, it's now populating the word "Collections" in the Customer Name field. I've double-checked the Collections table just to make sure records for "Customer Name" and "Collections" don't exist and they truly don't.

I've tried setting up the subform both with a form in datasheet view and also directly to the "Company" table and neither work. If anyone has any guesses I'm at a complete loss. If anyone needs to see the db, I can provide a copy once I remove the information from it.

Also, just a note. I've posted in these forums before and I get a lot of responses about poorly constructed databases and poor naming conventions. I'd like everyone to know I agree. Unfortunately, I work with databases that were constructed by people not very familiar with the product. So I'm attaching a copy of the database below, but please understand I'm already in agreement with you.

Thanks!

 

[MarkK]

I think you need some work on the tables, for instance, a table called Company should contain fields that define the Company only. To have a field called CustomerName in the Company table makes no sense. The company needs to be able to exist and be defined apart from any customer. If there are customers in the system, there should be a table called Customer.

And by the same reasoning, InvoiceNumber, ExpenseType, and Date do not make sense as dimensions of a Company.

You might want to google "entity relationship model" to understand better how to define entities in a data system, and how to relate them to each other. Also, closely related to that are the concepts of "database normalization." If you gain a greater understanding of these topics your work with databases will get much easier.

 

[csh2013]

I understand this is not constructed well. I noted that in my question. I did not build it. My issue is that I have a bunch of projects going on right now, so I don't have time to reconstruct it from scratch so that it's done properly. I was just trying to get the issue at hand resolved so that it functions for the individual trying to use it until I get a few things off my plate. Are you indicating you believe the issue to be related to the construct?

 

[MarkK]

Check out...
https://support.office.com/en-us/ar...mary-key-d61c9f21-accc-4566-ba3a-ea9bcffc06cf

 

[plog]

Are you indicating you believe the issue to be related to the construct?

Yes. In your initial post you tried to head off admonishment for your poorly named fields by saying you agreed but just didn't have time to fix them. Well, its both time for that admonishment and to find time to fix them.

1. You shouldn't use special characters in names. That means spaces ([Expense Types]) as well. It makes coding harder in queries and building forms.

2. You shouldn't use reserved words as names. Here's a list: https://support.office.com/en-us/ar...-symbols-e33eb3a9-8baa-4335-9f57-da237c63eabe

The issue you are having directly relates to #2. Your master/child relationship in that form uses [Name] and [Customer Name]. When I changed [Name] to [CollectionsName] I was able to get your form to work.

This is why you name your fields/tables properly--because it saves time in the long run.

 

[csh2013]

Actually, my initial post tried to head off admonishment because I didn't create the database and I'm just trying to get someone else's stuff to work. I see what you're saying with the reserved names though. It's something I didn't give any consideration to and it does resolve the problem. Thanks for pointing that out, plog!