I have combed the forum about filtering by selection but have not been able to make this work. I have and unbound cbo box "CatFilter" in the header that I want to select and filter on the value in a field in the form that is also a cbo box "CategoryID" (CategoryID is the Bound column of the combo and CategoryName is the viewed column). The form is based the Products Tbl and the cbo field is a qry of the Category Tbl. The code that I got from and Access book is below and does not seem to work. I get an error on the Me.Filter= line. Any help would be appreciated

TbLPartyProducts
ProductID PK
CategoryID FK
ProductName FLD

TblProductCategory
CategoryID PK
CategoryName FLD

Private Sub CatFilter_AfterUpdate()
Me.FilterOn = True
Me.Filter = "CategoryID = " & Me.CatFilter
End Sub

me.filter

THEN me.FilterOn

here is a link for you to read:

Filter a form

is CategoryID ON your form? It can be hidden

Thanks so much for your help...I got the order right now...thanks. I now get an error that says "The specified field '[CategoryID] could refer to more than one table list in the FROM clause of you SQL statement"

The field I am filtering on is [CategoryID] of a cbobox on the form and the unbound cbo in the header is also has [CategoryID] as the bound colum in the row source.

I read your link on filtering but I do not know where "code behind the form" goes and it seemed like more than what I needed...I am a novice at this.

You are very helpful though and I appreciate it very much

Roger

Hi Roger,

you should base each form on just one table -- if you just want to DISPLAY fields from another table, there are much better ways to do this than to include them in the RecordSet of the form.

If you want to FILL more than one table, you should use a subform

Now to the code... coding is not hard if you want to learn it. At first, things may seem daunting, but give yourself a little background...

on learning programming, here is a link for you: download the first 3 chapters of VBA book (rest not written yet) in this post (3 toward end, link to 1&2 is referenced in beginning)

Becoming a better Access Programmer

Thanks for the link on VB...most helpful.

As far as the form I am using it for inputing products and viewing them too. It is just easier to filter them by their category when I am using it instead of scrolling through all of them. I am using just one tbl for the products, the category tbl is just a lookup tbl to get the categories from for the products.

By the way this is a major project and I might want to seek more of your assistance on the whole thing. I want to get it right. I know there are some mistakes and approaches that may not be the best but I am working on it.

Roger

you're welcome

post your db and specify the formname you are working on -- I'll look at it

I was just wondering if you had a chance to look at this

can you please convert to Access2000 format?

Tools, database utilities, convert ...

Here is the 2k format...thank you so much

Hi Roger,

here it is back

you have your form ordered by: Lookup_CategoryID.CategoryName

this is not in your recordset...

I put it in...

SELECT TblPartyProducts.*, TblProductCategory.CategoryName
FROM TblProductCategory RIGHT JOIN TblPartyProducts
ON TblProductCategory.CategoryID = TblPartyProducts.CategoryID;



form OnLoad -->
=SortMe()

form AfterDeleteConfirm -->
=SortMe()

also put a module in there, crystal_code

combobox MouseUp -->
=DropMe()

from my library

added column to catfilter combo to show how many products are in the category:

RowSource -->


ColumnCount --> 3
columnWidths --> 0";1";0.5"
ListRows --> 24
ListWidth --> 1.5

Crystal,

Thank you so much...I learn tons from you and your posts. I am a newbie and I am very grateful for your help.

Roger

you're welcome, Roger happy to help ... thanks