Parent/child/grandchild from a single table

[dkperez]

I'm building a simple Access application... And I need to build a simple form:

My table has an owner, imagename, quantity, and printsize (and a bunch of other fields that don't matter)

On my form, I need to select or enter an owner. THEN, I need to select or enter an imagename, then specify a quantity and printsize....
There may be multiple "orders" consisting of a quantity and printsize...

I tried creating an owner table, image table, and order table, and building this as a parent/child/grandchild, but it looks ugly and cumbersome...



What's the easiest way to do this simply?

[RuralGuy]

It sounds like two tables to me with a 1:m relationship. tblOwner, tblPrints That calls for a mainForm and one SubForm in ContinuousForm mode.

[ajetrumpet]

It sounds like two tables to me with a 1:m relationship. tblOwner, tblPrints That calls for a mainForm and one SubForm in ContinuousForm mode.

not only this, but what kind of business are we talking about here?

Hey Allan, did you see my long post a while back about the business drivers and the relation of them to databases? That was the best explanation that I've ever given!

[dkperez]

Let me be more specific (all these are simplified for example purposes)...

I have a Printprice table with "printsize" and "price"
I have an Owner table with a single text column "ownername"
I have an Order table that's a child of Owner related on "ownername", with
ownername, imagename, quantity, printsize
Order is ALSO a child of Printprice related on "printsize"

So, very simple - person says "I'm John Smith. For image 7 I want 3 8x10 prints".

I want to be able to:
Open the Order form. The main form just has "ownername" from Owner. If John Smith is in the table, select him from the combo box. If not, type in "John Smith" in the combo box for insertion in Owner.
Go to the child form "OrderSubform"
Enter the imagename, quantity, and select printsize from a combo box.
Once selected, I want an unbound "price" field to load so I can see how much they owe.

This seems like it should be trivial, done a thousand times a day..... I'm having a couple problems:
I want the owner in a combo box. But, when I do this, and move to the subform when there are already Order records I get an error about violating a primary key... It turns out Order is attempting to INSERT a new record for an owner that's already in the table. Since "ownername" is primary key, it fails. WHY, if the record already exists in the table, is Access attempting to insert a record? HOW do I make it insert if the record ISN'T there and leave it alone if the record is?

I want to populate the unbound "price" field with a value from the "PrintPrice" table based on printsize in the table being equal to printsize on my subform. (yes, I KNOW the real price will be a calculation using quantity, but for right now I just want a value from a table). I put an unbound field on the subform and used
"select Printprice.price from Printprice where Printprice.printsize = Forms!Order!OrderSubform.Form!printsize;
I get an error in design view telling me there's no such field in the field list. I presume something is named wrong, but I don't see any errors.


John Smith has 3 images for which he's previously ordered. I'd LIKE to populate a combo box for ONLY his images that previously have orders when the owner is selected. NOT show every line of every order for every image belonging to John Smith - Just populate the combo box. THEN, when image 7 is selected, show the current orders. If image 7 hasn't been ordered, enter it in the combo box, and continue... I don't want to have to create an "Image" table and be forced to do a parent/child/grandchild form...

I know if I went back far enough, about 5 years ago I'd have had no problem doing this (used to use Access a lot, but have been doing Oracle and web stuff since), so I'm rusty and hitting stupid problems...

Ideas...

[dkperez]

I've continued wasting my time on this, and my main problem appears to have NOTHING to do with master/detail forms...

I have the Owner table. It has ONE column - name, text, and it's the primary key (ignore that this is NOT how it'll actually be. For today, it's fine)...

Create a form with the wizard or autoform.... I get a form with one field on it. I can use the record selectors to move through whatever records are in the table.

Do a format/Change to, and change the field on the form to a combo box. Can still use the record selectors, but the combo box doesn't populate... SO, for Row Source, put a simple query - "select name from owner"

Great! Combo box is not populated and I can select a value.....

BUT, AND HERE'S THE PROBLEM - as SOON as I open the combo box and move to a row other than the original one, Access wants to WRITE whatever value I'm on to the table....... NOT a new value, or even a changed value, the value...... Which violates the primary key and causes things to fail......... This CAN'T be standard behavior so I've gotta be doing something wrong....... What?

[RuralGuy]

You can change a ForeignKey field in a table but when you try and change the PrimaryKey field you will have the problem you experienced.

[dkperez]

Maybe I'm missing something here.....

Field on form... Displayed in a combo box. NOT changing that I know of. Just displaying a list. Select a value from the list, which is data that ALREADY exists in the table.

So, I shouldn't be having Access try to write a new record to the table. Shouldn't matter if its the primary key field or not.

If this is the case, how do you EVER create a master/subform form where you display details of a master record? Every time you select a value in the combo box on the master form and try to edit the subform details you get the error about changing the primary key. Doesn't make any sense...

Given the trivial example I referenced in the previous post, there HAS to be a way to display master records so you can change/add/delete details.

HOW?

[RuralGuy]

You changed a bound TextBox to a bound ComboBox. It has a ControlSource.

[dkperez]

Um, mayhap I'm having an extraordinarily dumb day, but, so? Yep, I changed a field to a combo box... And this is causing the problem because?

Lemme ask differently...... How DO, I create a master/detail using the field from the master table in a combo box, and NOT have it complain when I get various details? I believe (and I can try this again just for giggles), I've already tried the master with an unbound combo box that used a query to get the records from the master table. How SHOULD I be doing this?

[RuralGuy]

The basic method is to set up the LinkChild/MasterFields properties of the SubFormControl properly and then add a ComboBox to your MainForm and select Option #3 from the wizard.

[Access_Blaster]

Hey Allan, did you see my long post a while back about the business drivers and the relation of them to databases? That was the best explanation that I've ever given!

Subtle, but a bit understated.