Master/detail form problem....

[dkperez]

I apologize in advance for such a question, but apparently the brain cell that knew how to do this has died...........

I'm trying to create a basic parent/child form. When I do, my child link field doesn't appear to inherit the value from the parent......



parent table - Customer (name text(50))
child table - Order (name text(50), quantity (number))

Create a form with the form wizard, tell it to use Customer, select all the fields (Name), then select Order, tell it to use all the fields (Name, Quantity), tell it to make it a subform... SO, I get a customer form with Name, and an Order subform with Name and Quantity.

I THOUGHT by doing this as a master/detail "Name" on the subform wouldn't be visible, and would AUTOMATICALLY get the Name value from the parent. It appears that neither of these is currently true...
Which means I've GOT to be doing something wrong or forgetting something... What have I got goofed up?

I have other, previously created master/detail forms that work fine. I've looked at a couple, and I'm absolutely NOT figuring out how the relationship is being handled...

So, go ahead and laugh, but tell me the what I've forgotten...

[Accidental DBA]

As far as linking the two forms, open your properties window, and make sure your are editing your subform. Then use the master/child property to set which field is the link. Also you'll want to check the relationships to make sure you've defined the relationship between the field in the first place. Hope This helps.

[ssanfu]

First off, "Name" is a reserved word in Access and shouldn't be used for object names. Plus, it is not descriptive. "Name" of what??

In the Parent/Child (main form/ sub form) relationship, the field in the parent table should be the primary key (PK), linking to a field of the same type in the child table, the foreign key (FK).

Usually, these are of type long integer. Using a text type field (ie a name) is OK if the names are unique. So the problem begins if you have two John Smiths. Which order belongs to which John smith??


What are your fields in the two tables?

[dkperez]

Um, just to clarify..... I've been doing database design for the last 25 years. Started with hierarchical systems (dinosaurs), and have created mission critical and data warehouse systems in Oracle for a long time.....

My fieldnames here are PURELY for illustration. Of course the "name" is CustomerName, and so on.... So, please don't get hung up on minutiae.

I have created a 1-n relationship between Customer and Order. Normally, this will be a 0-n, but when I created it that way it STILL didn't load the child field...

And, YES, in THIS SPECIFIC CASE, my customer is based on name. There won't be many, and I'll MAKE them unique. So, yes, "name" is the primary key...

For Order, the primary is Name and Product so that'll ALSO be unique...

My problem is that the information in the linking field of the parent is NOT automatically populating in the child form..... Which has to be SOMETHING incredibly stupid 'cause I've been using Access since 1995 and I've got tons of other master/detail forms that work fine.....

But for the last 3 months, I can't get a master/detail form to work correctly... I'm shaking my head 'cause as soon as I know what it is, I'm going to be convinced I'm the dumbest human that ever lived...

[ssanfu]

I didn't mean to stomp on your toes. Usually when someone uses "Name" or "Date" in their example, it indicates they usually don't have much experience with databases.


I tried creating a main/sub form several ways. I did find an auto-join switch. When I unchecked it, I had to manually set the joins for the subform.

TOOLS -> OPTIONS -> Tables/Queries tab

In the Query Design area, make sure that "Enable Auto Join" is checked.
This is the only thing that I can find that might affect the subform connection to the main form.

[dkperez]

Today, I resorted to the nuclear option...... Or maybe just the brute force and massive ignorance (BFMI) option......

I've had both Office 2003 and 2007 on this box for a while. I hated 2007 when I installed it, but figured I should persevere. After I YEAR I REALLY HATED IT. But again, persevered. After some even longer time I decided I'm NEVER going to like it.....

So, today, 2007 got removed. AND 2003 got removed. EVERYBODY got removed........ Then I re-installed 2003 from the CD......

I now have a form that works fine... The foreign key from the child knows who the parent is, and it's stopped whining!

I have NO idea which clobbering fixed it, but at the moment it's fixed so I don't care......

Thanks for the ideas, y'all.......