List Box

I have a frmreferb form and frmrefurbsub form.

the sub form and the following field Supplier - Product - Product Desc -
Serial Number

I want to be able to click on the Supplier List Box and the click on the
product list box but I only want products to display which are available
from the supplier I have selected initially.

Does anyone have any ideas how I can achieve the above?

Thank you in advance.

S

Sorry I ment Combo boxes and NOT List Boxes in my post!


"SG" wrote in message
...
I have a frmreferb form and frmrefurbsub form.

the sub form and the following field Supplier - Product - Product Desc -
Serial Number

I want to be able to click on the Supplier List Box and the click on the
product list box but I only want products to display which are available
from the supplier I have selected initially.

Does anyone have any ideas how I can achieve the above?

Thank you in advance.

S

see http://www.mvps.org/access/forms/frm0028.htm.

hth


"SG" wrote in message
...
I have a frmreferb form and frmrefurbsub form.

the sub form and the following field Supplier - Product - Product Desc -
Serial Number

I want to be able to click on the Supplier List Box and the click on the
product list box but I only want products to display which are available
from the supplier I have selected initially.

Does anyone have any ideas how I can achieve the above?

Thank you in advance.

S

SG wrote:

Sorry I ment Combo boxes and NOT List Boxes in my post!

I have a frmreferb form and frmrefurbsub form.

the sub form and the following field Supplier - Product - Product Desc -
Serial Number

I want to be able to click on the Supplier List Box and the click on the
product list box but I only want products to display which are available
from the supplier I have selected initially.


Doesn't matter, the solution to both is the same ;-)

Set the product combo box's RowSource to a query that has a
criteria like this for the supplier field:
Forms!frmreferb.frmrefurbsub.Form.cboSupplier

Then, use a little code in the Supplier combo box's
AfterUpdate event procedu

Me.cboProduct = Null
Me.cboProduct.Requery

--
Marsh
MVP [MS Access]

Umm,,

I seem to be really struggling with this one....

Does it make any difference if I have the form in datasheet view and is a
subform?

All that seems to happen when I do the below is a select the supplier and I
get a runtime error and then there is nothing in the product combo list??

Do you have any detialed documentation I can follow?

Thank you

S


"Marshall Barton" wrote in message
...
SG wrote:

Sorry I ment Combo boxes and NOT List Boxes in my post!

I have a frmreferb form and frmrefurbsub form.

the sub form and the following field Supplier - Product - Product Desc -
Serial Number

I want to be able to click on the Supplier List Box and the click on the
product list box but I only want products to display which are available
from the supplier I have selected initially.


Doesn't matter, the solution to both is the same ;-)

Set the product combo box's RowSource to a query that has a
criteria like this for the supplier field:
Forms!frmreferb.frmrefurbsub.Form.cboSupplier

Then, use a little code in the Supplier combo box's
AfterUpdate event procedu

Me.cboProduct = Null
Me.cboProduct.Requery

--
Marsh
MVP [MS Access]

There are other explanations in many other newsgroup posts
that you can find by searching the Google archives.

You should have posted the error message that was displayed
because it is usually a valuable clue. However, I suspect
that your error was a type mismatch. If so, it would imply
that there is something funny in the way you have set up the
combo boxes.

Please post a Copy/Paste of both combo box's RowSource SQL
statements so I can see the fields. I also need to see both
combo box's Name, ColumnCount and BoundColumn properties and
a Copy/Paste of the actual code that you used.
--
Marsh
MVP [MS Access]


SG wrote:
Does it make any difference if I have the form in datasheet view and is a
subform?

All that seems to happen when I do the below is a select the supplier and I
get a runtime error and then there is nothing in the product combo list??

Do you have any detialed documentation I can follow?


SG wrote:
I have a frmreferb form and frmrefurbsub form.

the sub form and the following field Supplier - Product - Product Desc -
Serial Number

I want to be able to click on the Supplier List Box and the click on the
product list box but I only want products to display which are available
from the supplier I have selected initially.


"Marshall Barton" wrote
Set the product combo box's RowSource to a query that has a
criteria like this for the supplier field:
Forms!frmreferb.frmrefurbsub.Form.cboSupplier

Then, use a little code in the Supplier combo box's
AfterUpdate event procedu

Me.cboProduct = Null
Me.cboProduct.Requery

Marsh

I have removed the original code etc so I now have an frmrefurb and
frmrefurbsubform. Here are the details of the combo boxes - Thank you for
you help!


The subform is where I have the combo boxes in datasheet view I have
referbdetailID Supplier Name: ProductID and Product Description Name:

In deisgn view I have CBOSupplierName Row Source SELECT DISTINCT
tblProducts.SupplierID, tblSuppliers.SupplierName FROM tblSuppliers INNER
JOIN tblProducts ON tblSuppliers.SupplierID=tblProducts.SupplierID; Column
Count 2 Bound Column 2 Control Source SupplierName



Then I Have CBOProduct Row Source SELECT tblProducts.ProductID,
tblProducts.ProductName FROM tblProducts; Column Count 2 Bound Column 1
Control Source ProductID




and finally CBOProductDescription Row Source SELECT
tblProducts.ProductDescription FROM tblProducts; Column Count 1 Bound Column
1 Control Source ProductDescription.



"Marshall Barton" wrote in message
...
There are other explanations in many other newsgroup posts
that you can find by searching the Google archives.

You should have posted the error message that was displayed
because it is usually a valuable clue. However, I suspect
that your error was a type mismatch. If so, it would imply
that there is something funny in the way you have set up the
combo boxes.

Please post a Copy/Paste of both combo box's RowSource SQL
statements so I can see the fields. I also need to see both
combo box's Name, ColumnCount and BoundColumn properties and
a Copy/Paste of the actual code that you used.
--
Marsh
MVP [MS Access]


SG wrote:
Does it make any difference if I have the form in datasheet view and is a
subform?

All that seems to happen when I do the below is a select the supplier and
I
get a runtime error and then there is nothing in the product combo list??

Do you have any detialed documentation I can follow?


SG wrote:
I have a frmreferb form and frmrefurbsub form.

the sub form and the following field Supplier - Product - Product
Desc -
Serial Number

I want to be able to click on the Supplier List Box and the click on
the
product list box but I only want products to display which are
available
from the supplier I have selected initially.


"Marshall Barton" wrote
Set the product combo box's RowSource to a query that has a
criteria like this for the supplier field:
Forms!frmreferb.frmrefurbsub.Form.cboSupplier

Then, use a little code in the Supplier combo box's
AfterUpdate event procedu

Me.cboProduct = Null
Me.cboProduct.Requery

It appears that the refurbdetail TABLE is a list of products
associated with a refurb.

In a situation like that, unless you are doing something
weird with the product description, tblreferbdetail only
needs two fields:
referbdetailID (Autonumber? primary key)
ProductID (Long Foreign key)

You can include SupplierID in there if you think it will
help somewhere else, but a query can get the SupplierID from
the product table using the ProductID. OTTH, you do not
want the product name, description or serial number fields
in tblreferbdetail as they would duplicate the information
in the product table.

The table could, of course, have other fields for data
specific to the refurbdetail (e.g. refubdate, work estimate.
etc).

Given all that, the subform would have (at least):

txtreferbdetailID
Visible No
ControlSource referbdetailID

CBOSupplier:
Row Source:
SELECT SupplierID, SupplierName
FROM tblSuppliers
Column Count 2
Bound Column 1
ColumnWidths 0;
Control Source SupplierID

CBOProduct:
Row Source:
SELECT ProductID, ProductName,
ProductDescription, ProductSerialNum
FROM tblProducts
WHERE SupplierID =
Forms!frmrefurb.frmrefurbsubform.Form.CBOSupplier
Column Count 4
Bound Column 1
ColumnWidths 0;;0;0
Control Source ProductID

txtProductDescr:
ControlSource =CBOProduct.Column(2)

txtSerialNum
ControlSource =CBOProduct.Column(3)
--
Marsh
MVP [MS Access]


SG wrote:
I have removed the original code etc so I now have an frmrefurb and
frmrefurbsubform. Here are the details of the combo boxes

The subform is where I have the combo boxes in datasheet view I have
referbdetailID
Supplier Name
ProductID
Product Description Name

CBOSupplierName
Row Source:
SELECT DISTINCT tblProducts.SupplierID,
tblSuppliers.SupplierName
FROM tblSuppliers INNER JOIN tblProducts
ON tblSuppliers.SupplierID=tblProducts.SupplierID;
Column Count 2
Bound Column 2
Control Source SupplierName

CBOProduct
Row Source:
SELECT tblProducts.ProductID, tblProducts.ProductName
FROM tblProducts;
Column Count 2
Bound Column 1
Control Source ProductID

CBOProductDescription
Row Source:
SELECT tblProducts.ProductDescription
FROM tblProducts;
Column Count 1
Bound Column 1
Control Source ProductDescription


"Marshall Barton" wrote
There are other explanations in many other newsgroup posts
that you can find by searching the Google archives.

You should have posted the error message that was displayed
because it is usually a valuable clue. However, I suspect
that your error was a type mismatch. If so, it would imply
that there is something funny in the way you have set up the
combo boxes.

Please post a Copy/Paste of both combo box's RowSource SQL
statements so I can see the fields. I also need to see both
combo box's Name, ColumnCount and BoundColumn properties and
a Copy/Paste of the actual code that you used.


SG wrote:
Does it make any difference if I have the form in datasheet view and is a
subform?

All that seems to happen when I do the below is a select the supplier and
I
get a runtime error and then there is nothing in the product combo list??

Do you have any detialed documentation I can follow?


SG wrote:
I have a frmreferb form and frmrefurbsub form.

the sub form and the following field Supplier - Product - Product
Desc -
Serial Number

I want to be able to click on the Supplier List Box and the click on
the
product list box but I only want products to display which are
available
from the supplier I have selected initially.


"Marshall Barton" wrote
Set the product combo box's RowSource to a query that has a
criteria like this for the supplier field:
Forms!frmreferb.frmrefurbsub.Form.cboSupplier

Then, use a little code in the Supplier combo box's
AfterUpdate event procedu

Me.cboProduct = Null
Me.cboProduct.Requery

I have done what you have suggested and have two issues which is most like
down to something I have done When I access the page whihc may I add has a
tab control I get the following message Enter Value
Forms!frmrefurb.tblReferbDetails Subform.Form.CBOSupplier ??

The second issue I have is I can select the supplier and then try and select
the product whihc displays the correct product but will not allow me to
select it? No message appears?

Any Ideas?


"Marshall Barton" wrote in message
...
It appears that the refurbdetail TABLE is a list of products
associated with a refurb.

In a situation like that, unless you are doing something
weird with the product description, tblreferbdetail only
needs two fields:
referbdetailID (Autonumber? primary key)
ProductID (Long Foreign key)

You can include SupplierID in there if you think it will
help somewhere else, but a query can get the SupplierID from
the product table using the ProductID. OTTH, you do not
want the product name, description or serial number fields
in tblreferbdetail as they would duplicate the information
in the product table.

The table could, of course, have other fields for data
specific to the refurbdetail (e.g. refubdate, work estimate.
etc).

Given all that, the subform would have (at least):

txtreferbdetailID
Visible No
ControlSource referbdetailID

CBOSupplier:
Row Source:
SELECT SupplierID, SupplierName
FROM tblSuppliers
Column Count 2
Bound Column 1
ColumnWidths 0;
Control Source SupplierID

CBOProduct:
Row Source:
SELECT ProductID, ProductName,
ProductDescription, ProductSerialNum
FROM tblProducts
WHERE SupplierID =
Forms!frmrefurb.frmrefurbsubform.Form.CBOSupplier
Column Count 4
Bound Column 1
ColumnWidths 0;;0;0
Control Source ProductID

txtProductDescr:
ControlSource =CBOProduct.Column(2)

txtSerialNum
ControlSource =CBOProduct.Column(3)
--
Marsh
MVP [MS Access]


SG wrote:
I have removed the original code etc so I now have an frmrefurb and
frmrefurbsubform. Here are the details of the combo boxes

The subform is where I have the combo boxes in datasheet view I have
referbdetailID
Supplier Name
ProductID
Product Description Name

CBOSupplierName
Row Source:
SELECT DISTINCT tblProducts.SupplierID,
tblSuppliers.SupplierName
FROM tblSuppliers INNER JOIN tblProducts
ON tblSuppliers.SupplierID=tblProducts.SupplierID;
Column Count 2
Bound Column 2
Control Source SupplierName

CBOProduct
Row Source:
SELECT tblProducts.ProductID, tblProducts.ProductName
FROM tblProducts;
Column Count 2
Bound Column 1
Control Source ProductID

CBOProductDescription
Row Source:
SELECT tblProducts.ProductDescription
FROM tblProducts;
Column Count 1
Bound Column 1
Control Source ProductDescription


"Marshall Barton" wrote
There are other explanations in many other newsgroup posts
that you can find by searching the Google archives.

You should have posted the error message that was displayed
because it is usually a valuable clue. However, I suspect
that your error was a type mismatch. If so, it would imply
that there is something funny in the way you have set up the
combo boxes.

Please post a Copy/Paste of both combo box's RowSource SQL
statements so I can see the fields. I also need to see both
combo box's Name, ColumnCount and BoundColumn properties and
a Copy/Paste of the actual code that you used.


SG wrote:
Does it make any difference if I have the form in datasheet view and is
a
subform?

All that seems to happen when I do the below is a select the supplier
and
I
get a runtime error and then there is nothing in the product combo
list??

Do you have any detialed documentation I can follow?


SG wrote:
I have a frmreferb form and frmrefurbsub form.

the sub form and the following field Supplier - Product - Product
Desc -
Serial Number

I want to be able to click on the Supplier List Box and the click on
the
product list box but I only want products to display which are
available
from the supplier I have selected initially.


"Marshall Barton" wrote
Set the product combo box's RowSource to a query that has a
criteria like this for the supplier field:
Forms!frmreferb.frmrefurbsub.Form.cboSupplier

Then, use a little code in the Supplier combo box's
AfterUpdate event procedu

Me.cboProduct = Null
Me.cboProduct.Requery

Are you sure that the subform **control** is named
tblReferbDetails Subform
Even if that is the control's name, it would require [ ]
around it because of the space.
Forms!frmrefurb.[tblReferbDetails Subform].Form.CBOSupplier

If the combo box won't let you select an entry in the list,
I think it might be because its control source is not
updatable. Either the revord source query is not updatable,
the combo box is bound to something funny in the subform's
record source or you might have an = sign in front of the
field name or ???. If you can't spot the problem, please
post details about the combo box so I have some clues to
work with.

Do the text box's display the description and serial number?

Did you remember the two lines of code in the supplier combo
box's AfterUpdate event procedure?
--
Marsh
MVP [MS Access]


SG wrote:
I have done what you have suggested and have two issues which is most like
down to something I have done When I access the page whihc may I add has a
tab control I get the following message Enter Value
Forms!frmrefurb.tblReferbDetails Subform.Form.CBOSupplier ??

The second issue I have is I can select the supplier and then try and select
the product whihc displays the correct product but will not allow me to
select it? No message appears?


"Marshall Barton" wrote
It appears that the refurbdetail TABLE is a list of products
associated with a refurb.

In a situation like that, unless you are doing something
weird with the product description, tblreferbdetail only
needs two fields:
referbdetailID (Autonumber? primary key)
ProductID (Long Foreign key)

You can include SupplierID in there if you think it will
help somewhere else, but a query can get the SupplierID from
the product table using the ProductID. OTTH, you do not
want the product name, description or serial number fields
in tblreferbdetail as they would duplicate the information
in the product table.

The table could, of course, have other fields for data
specific to the refurbdetail (e.g. refubdate, work estimate.
etc).

Given all that, the subform would have (at least):

txtreferbdetailID
Visible No
ControlSource referbdetailID

CBOSupplier:
Row Source:
SELECT SupplierID, SupplierName
FROM tblSuppliers
Column Count 2
Bound Column 1
ColumnWidths 0;
Control Source SupplierID

CBOProduct:
Row Source:
SELECT ProductID, ProductName,
ProductDescription, ProductSerialNum
FROM tblProducts
WHERE SupplierID =
Forms!frmrefurb.frmrefurbsubform.Form.CBOSupplier
Column Count 4
Bound Column 1
ColumnWidths 0;;0;0
Control Source ProductID

txtProductDescr:
ControlSource =CBOProduct.Column(2)

txtSerialNum
ControlSource =CBOProduct.Column(3)


SG wrote:
I have removed the original code etc so I now have an frmrefurb and
frmrefurbsubform. Here are the details of the combo boxes

The subform is where I have the combo boxes in datasheet view I have
referbdetailID
Supplier Name
ProductID
Product Description Name

CBOSupplierName
Row Source:
SELECT DISTINCT tblProducts.SupplierID,
tblSuppliers.SupplierName
FROM tblSuppliers INNER JOIN tblProducts
ON tblSuppliers.SupplierID=tblProducts.SupplierID;
Column Count 2
Bound Column 2
Control Source SupplierName

CBOProduct
Row Source:
SELECT tblProducts.ProductID, tblProducts.ProductName
FROM tblProducts;
Column Count 2
Bound Column 1
Control Source ProductID

CBOProductDescription
Row Source:
SELECT tblProducts.ProductDescription
FROM tblProducts;
Column Count 1
Bound Column 1
Control Source ProductDescription


"Marshall Barton" wrote
There are other explanations in many other newsgroup posts
that you can find by searching the Google archives.

You should have posted the error message that was displayed
because it is usually a valuable clue. However, I suspect
that your error was a type mismatch. If so, it would imply
that there is something funny in the way you have set up the
combo boxes.

Please post a Copy/Paste of both combo box's RowSource SQL
statements so I can see the fields. I also need to see both
combo box's Name, ColumnCount and BoundColumn properties and
a Copy/Paste of the actual code that you used.


SG wrote:
Does it make any difference if I have the form in datasheet view and is
a
subform?

All that seems to happen when I do the below is a select the supplier
and
I
get a runtime error and then there is nothing in the product combo
list??

Do you have any detialed documentation I can follow?


SG wrote:
I have a frmreferb form and frmrefurbsub form.

the sub form and the following field Supplier - Product - Product
Desc -
Serial Number

I want to be able to click on the Supplier List Box and the click on
the
product list box but I only want products to display which are
available
from the supplier I have selected initially.


"Marshall Barton" wrote
Set the product combo box's RowSource to a query that has a
criteria like this for the supplier field:
Forms!frmreferb.frmrefurbsub.Form.cboSupplier

Then, use a little code in the Supplier combo box's
AfterUpdate event procedu

Me.cboProduct = Null
Me.cboProduct.Requery