X Y Plane Dynamic Form

[tjcinnamon]

I have a complex problem (I think).

I have a table of tblPurchases: ItemID, Item, Units, Price, FileNumber. (1 primary key and 1 foreign)
I have a 2nd table of tblMeasures: MeasureID, Measure, FileNumber. (1 primary key and 1 foreign)
My Main table is a Table consisting of FileNumbers. These tables are related by there file numbers.

I have a 3rd table(tblAssignment) that consists of MeasureID, MeasureQuantity, ItemID, FileNumber (3 foreign keys and a field)

If I have:

tblPurchases
ItemID, Item, Units, Price, FileNumber.
001-----Tape---2-----4.99------101
002-----Nails----5-----1.20-----101
003-----2x4-----3-----9.90-----101
... ------ ...-----..-----.....------...

tblMeasures
MeasureID, Measure, FileNumber
100-------------Fan---------101
101-------------Attic--------101
102-----------Furnace------101
...-------------.........------ ....

What I want is a form that will allow the user to associate the Purchases with the Measures.

So what I'm looking for is a Dynamic X Y Plane Form

FileNumber 101
----------Fan------ Attic------ Furnace-- ........ (up to 20 on x plane) Measures
Tape-----1---------- 1------------0-------
Nails-----3-----------0------------2-------
2x4------0-----------2------------1--------
.....
(up to 40 on y plane)
Items

The tblAssignment would then contain:

ItemID, MeasureQuantity, MeasureID, FileNumber.
001-------------1-----------------100------------101_____1 Tape in fan
001-------------1-----------------101------------101_____1 Tape in attic
002-------------3-----------------100------------101_____3 Nails in fan
002-------------2-----------------102------------101_____2 Nails in Furnace
003-------------2-----------------101------------101_____2 2x4 in Attic
003-------------1-----------------102------------101_____1 2x4 in Furnace


As a sidenote not really pertaining to my question:
I have a Form with 3 subforms the main form is just the FileNumber in a text box.
I have 2 display continuous subforms to let the user know what Items (Prices, unit, ect.), and Measures are available.
The 3rd is a datasheet subform (where the magic should happen) with the fields:
Item, MeasureQuantity, Measure, FileNumber, ItemID, MeasureID.
I have them select the Item, MeasureQuanity, and Measure. The Item and Measure Combo Boxes have auto populate the ItemID, and MeasureID fields. I get a error saying it can't save the record, plus this method of entry is too laborious for the end user with the amount of assignments.

 

[Scottyk537]

First of all.......
Why would you need a table tblAssignment? It seems like this is just duplicating data. Link the two tables and create a query that will display info like tblAssignment would do.
Put another field in your tblMeasures that links to the ItemID in your tblPurchases.
You will get better help on this site if you first setup your database correctly. Your other issue might be related to this.