filling in the fields...

[jetson]

Ok folks, I've had enough with experimentation. And as much as I've stared at the code in the NorthWind sample db - I still can't get it right.

Parts table
Fields:
PartId (autonumber - no dupes)
PartName (text)
Price (currency)
Unit (text)

Master table and associated form called - Arrangements
Fields:
ArrangeID (autonumber - no dupes)
ArrangeName (text)

Sub table and associated subform called - ArrangeDetails
Fields:
ArrangeID (numeric - link to master for detail records - dupes OK)
PartID (numeric - link to Parts table)
PartName (text - extracted from Parts table)
Quantity (numeric - defaults to 1, manually changed)
Price (currency - extracted from Parts table)
Unit (text - extracted from Parts table)

So I've got a master form based on Arrangements, with a SubForm from ArrangeDetails. All I want to do is drop down a ComboBox in the ArrangeDetails subform (using the PartName field), user selects a part, and the detail record is filled in automagically using the fields from the Parts table.

Sorry for the lengthy post - and thanks in advance to anyone willing to read it!

 

[Oldsoftboss]

Refer to my post here: http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=40628

It should be a good start.

Your form should be based on a query.

I did exactly the same - for months and months, then posted a similar question here and bingo, it all became clear.

Dave

 

[Oldsoftboss]

Sorry I may not have read yor post clearly enough. You may need a junction table to make a many to many relationship. Having never used one I am probably not the one to give advice. Search Access help for many to many.

Will play around and get back.

Dave

 

[jetson]

normalization?

Maybe my table design approach is flawed? My thought was this:

Have a table of unique parts to serve as the lookup table for the arrangement details. Parts have a unique Id and can be maintained separately from the other tables as necessary.

Now, I guess I'm breaking the rules a bit if I store the parts fields values in the arrangement details table - data redundancy at it's finest. I want the end user to see the data from the parts fields actually fill in the matching fields from arrangement details. So if I could make that happen behind the scenes using a query, that would be cool.

Ultimately - arrangement details table would only need to store the PartID.

I'll take another crack at this. I love this board - thanks for the thoughts...

 

[Oldsoftboss]

Have a look at the join table called “arrangementparts” and the relationship of the three tables. Also in the query “PartsQry”, when open in design view, if you right click some where in the area that the tables are and select properties you will see a field called “output to all fields” Select No, this then restricts the query to your selected fields.
Notice there are 2 UnitPrice fields, one in the “parts” table and one in the “arrangementparts” In an after update event on the parts sub form a Dlookup picks the price from the parts table and inserts it into the “arrangementparts” This is done so the price can be edited without upsetting the prices in every other record.
Hope it leads you to a better understanding, and a solution
Dave

 

[jetson]

Thank you Oldsoftboss...

I can use this.