Predefined Two-dimensional Subforms?

[Grahame]

I realise this may be a bit of a long shot but if anyone can help me,
I'd be most grateful.

My main form has a dropdown for products A, B, C etc.

In the subform I should like to predefine the row headings Small,
Medium and Large, so that the user is presented with the following
subform datasheet:

Subform datasheet:

Height Width Length
Small
Medium
Large

The user should not have to enter the Small, Medium and Large record
headings, only fill in the dimensions for each size.

Is this possible?

The reason I wish to do this is to cut down on the user's inputting,
otherwise they have to enter Small, Medium and Large for each product
before entering the dimensions.

Many thanks

Grahame

 

[Guest]

This would not be that difficult. The question is, what does the underlying
recordset for the subform look like? Are the Small, Medium, Large values in
your table, or are they individual rows in a table?

Post back with the table layout of the recordset and we can help with a way
to accomplish this.

 

[Grahame]

Originally, I'd intended to have a Size table with Small, Medium and
Large as the records:

ID Size
1 Small
2 Medium
3 Large

plus a Product table:

ID Product
1 A
2 B
3 C

and then create a junction table to deal with the many-to-many
relationships.

I was intending to add the dimensions to the Size table, however, I
really want the user to initially set up the size catogories themselves
(or for me to precode them) and therefore choose to keep this table to
be as simple as possible and not include dimensions, which I am
intending to add to the junction table somehow!

The idea was to then access the the subform for each product by
displaying all the size options at once leaving the user to add just
the dimension for all the size options in one go.

Thank you for your help.

Grahame

 

[Guest]

I would suggest 3 tables for this.
Your product table It should have a unique primary key. For example
purposes, I will call it ProductID

Then a Sizes table that contains only two fields. An Autonumber primary
key and a description of the size:
tblSize
SizeID
SizeDescription
SizeHeight
SizeLength
SizeWidth

Then for the recordset to support your form, create a query that joins the 3
tables and includes the fields you need. As to the form, I would suggest you
use a Combo Box for the size. It's row source should be tblSize. They way I
would do it would be to make the Combo box unbound, but create an invisible
Text Box bound to SizeId. The Combo box would be 2 columns, one for each
field with only the description visible. In the After Update event of the
combo, put the value of the SizeID column in the invisible text box.

To make this work, this will need to be a sub form to the form where you put
in product information because there will be multiple sizes for a product.

If you need more help or clarification on this, post back.
And a Product Size table that will tie them all together and contain the
values for the sizes:
tblProductSize
ProductSizeID (Autonumber Primary Key)
ProductID (foreign key to tblProduct)
SizeID (Foreign key to tblSize)

 

[Grahame]

Klatuu

I've tried what you have very kindly suggested but unfortunately with
little success.

My tables are thus:

SizeID SizeDescription
1 SizeHeight
2 SizeLength
3 SizeWidth

ProductID ProductDescription
1 Product A
2 Product B
3 Product C

and junction table:
ProductSizeID (auto, primary)
ProductID (foreign, number)
SizeID (foreign, number)
Small
Medium
Large

I'm aiming in the main form to have ProductID as a dropdown (rather
than the SizeID) showing product descriptions: Product A, Product B,
Product C etc

and in the subform a predefined matrix:

Height Width Length
Small x x x
Medium x x x
Large x x x

where the user only has to specify dimensions 'x' and with Small,
Medium and Large already listed for the user (rather than having to add
the record for themselves and also restricted to the size description
set up in the Size table.

I've tried what you've suggested using SizeID as the dropdown but still
have the problem that Product A, B, C etc (rather than Small, Medium
and Large) have to be input as records in the subform. Unfortunately,
I'm not quite sure where the hidden Combo comes into play?

Taking your example using the product as the dropdown, I get the
following subform headings and the cursor under SizeDescription waiting
for input:

SizeDescription Small Medium
Large

If you could provide a little more clarification, I'd be very grateful.

Thanks

Grahame