Forms coding

[Alex]

Hi,

I am trying to design a form for our uniform orders database.

Relevant tables:
Customers
Orders (orderID, customerID, order date)
OrderDetail (orderDetailID, orderID, productID, size, quantity).
Sizes (sizeID, size)
Product (productID, product, colour)
SizesProductLinkTable (linkID, productID, sizeID).

So...for each product (e.g. shirt), there are only certain sizes that it is
available in. Hence the SizesProductLink table.

There is also a query called SizeProductLink which joins SizesProductLink
table with Sizes table so can see the actual sizes name, rather than just ID,
in the form.

The problem I am having revolves around the order subform:

Essentially, there is a main form (Orders Form) which has customer name and
order number.

Then there is a subform (OrderDetails) which shows which products are being
ordered.

The difficulty:
I can set the forms up without a problem except when I get to the size
field. I currently have it set up as a combo box from a query with the
following rowsource: SELECT [SizeProductLink].Size FROM [SizeProductLink]
ORDER BY [SizeProductLink shows sizes].Size;

I need to somehow program this field so that it only shows those sizes that
are valid for the product being ordered.

The subform looks like this:
Product ID Size Quantity

Is there a way to do this? So that the continuous subform updates the sizes
field depending upon the productID entered?

Any help is much appreciated,
From a very frustrated user.

 

[Steve Schapel]

Alex,

I don't know what is the purpose of the sizeID field. Without knowing
the nature of the data in the Sizes table, I can't be sure, but it seems
more complicated than it needs to be. Isn't each entry in the Size
field unique? If so, I would just have the one field (size) in the
Sizes table. On the other hand, if you are going to use the sizeID
idea, then logically it should be sizeID and not size in the OrderDetail
table.

Anyway, working with what you've got, try it like this... Set the Row
Source of the Size combobox on the OrderDetail subform to...
SELECT Size FROM Sizes
Then, on the Enter event of the combobox, put code like this...
With Me.Size
.RowSource = "SELECT Size FROM SizeProductLink WHERE ProductID=" &
Me.ProductID
.Requery
.DropDown
End With
(assumed that ProductID is a number)
And on the Exit event...
Me.Size.RowSource = "SELECT Size FROM Sizes"

 

[Alex]

Thank you so much Steve. It worked like a charm.

Alex.

Alex,

I don't know what is the purpose of the sizeID field. Without knowing
the nature of the data in the Sizes table, I can't be sure, but it seems
more complicated than it needs to be. Isn't each entry in the Size
field unique? If so, I would just have the one field (size) in the
Sizes table. On the other hand, if you are going to use the sizeID
idea, then logically it should be sizeID and not size in the OrderDetail
table.

Anyway, working with what you've got, try it like this... Set the Row
Source of the Size combobox on the OrderDetail subform to...
SELECT Size FROM Sizes
Then, on the Enter event of the combobox, put code like this...
With Me.Size
.RowSource = "SELECT Size FROM SizeProductLink WHERE ProductID=" &
Me.ProductID
.Requery
.DropDown
End With
(assumed that ProductID is a number)
And on the Exit event...
Me.Size.RowSource = "SELECT Size FROM Sizes"

--
Steve Schapel, Microsoft Access MVP

Hi,

I am trying to design a form for our uniform orders database.

Relevant tables:
Customers
Orders (orderID, customerID, order date)
OrderDetail (orderDetailID, orderID, productID, size, quantity).
Sizes (sizeID, size)
Product (productID, product, colour)
SizesProductLinkTable (linkID, productID, sizeID).

So...for each product (e.g. shirt), there are only certain sizes that it is
available in. Hence the SizesProductLink table.

There is also a query called SizeProductLink which joins SizesProductLink
table with Sizes table so can see the actual sizes name, rather than just ID,
in the form.

The problem I am having revolves around the order subform:

Essentially, there is a main form (Orders Form) which has customer name and
order number.

Then there is a subform (OrderDetails) which shows which products are being
ordered.

The difficulty:
I can set the forms up without a problem except when I get to the size
field. I currently have it set up as a combo box from a query with the
following rowsource: SELECT [SizeProductLink].Size FROM [SizeProductLink]
ORDER BY [SizeProductLink shows sizes].Size;

I need to somehow program this field so that it only shows those sizes that
are valid for the product being ordered.

The subform looks like this:
Product ID Size Quantity

Is there a way to do this? So that the continuous subform updates the sizes
field depending upon the productID entered?

Any help is much appreciated,
From a very frustrated user.