Drop down list

[Mya48]

In my form I have a drop down lists that lists all the office supply
products. My question is: How do I get the drop down lists to display only
those products which have not been discontinued? In my main table their is a
yes/no field if a product has been dicontinued. Thanks.

 

[Jeff Boyce]

Base your drop-down list on a query that only finds the products not
discounted.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Linq Adams via AccessMonster.com]

Something like:

SELECT ProductsTable.ProductID, ProductsTable.ProductName, ProductsTable.
Discontinued
FROM ProductsTable
WHERE (((ProductsTable.Discontinued)=0));

 

[Mya48]

Thank you very much, it works great.

 

[Mya48]

Sorry to bother you again but would this work on a report as well?

 

[John W. Vinson]

Sorry to bother you again but would this work on a report as well?

Yep. Just try it <g>

 

[Mya48]

I entered this into the code but it doesn't do anything, any suggestions?

Private Sub Report_Open(Cancel As Integer)

On Error Resume Next
Me![ProductName].Controls = Me![ProductName]
Me.[ProductName].Object = Not (((Products.Discontinued) = 0))

End Sub

 

[John W. Vinson]

I entered this into the code but it doesn't do anything, any suggestions?

Private Sub Report_Open(Cancel As Integer)

On Error Resume Next
Me![ProductName].Controls = Me![ProductName]
Me.[ProductName].Object = Not (((Products.Discontinued) = 0))

This has absolutely NO relationship to anything I've seen in the thread, and
it doesn't make any sense to me. The Controls collection is a collection of
the controls (textboxes, combos, etc.) on a Form or Report.

What is this code intended to accomplish?

 

[Mya48]

All I was trying to accomplish was for my report to show only the products
that are available not discontinued. You gave me this code for the form and
it works great.

SELECT ProductsTable.ProductID, ProductsTable.ProductName, ProductsTable.

Discontinued
FROM ProductsTable
WHERE (((ProductsTable.Discontinued)=0));

but I guess I don't know where to put it on the report because there is no
row source on the properties of the field like there is on the form.

I entered this into the code but it doesn't do anything, any suggestions?

Private Sub Report_Open(Cancel As Integer)

On Error Resume Next
Me![ProductName].Controls = Me![ProductName]
Me.[ProductName].Object = Not (((Products.Discontinued) = 0))

This has absolutely NO relationship to anything I've seen in the thread, and
it doesn't make any sense to me. The Controls collection is a collection of
the controls (textboxes, combos, etc.) on a Form or Report.

What is this code intended to accomplish?

 

[John W. Vinson]

All I was trying to accomplish was for my report to show only the products
that are available not discontinued. You gave me this code for the form and
it works great.

SELECT ProductsTable.ProductID, ProductsTable.ProductName, ProductsTable.

but I guess I don't know where to put it on the report because there is no
row source on the properties of the field like there is on the form.

Use the same Query *As the recordsource for the Report*.

This has nothing to do with the properties of a field or of a control. My
suggestion is to change where the report gets its data.