Filtering on a Form

[JamesThurber]

I have a database with 2 tables.

Parts
OrigPartNum(Primary Key)
Comments

XRefParts
OrigPartNum
PartNum
Comments

I have a query that searches both the Parts and XRefParts tables looking for matching string information that is inputted at runtime. The query looks in the Parts.OrigPartNum field as well as the XRefParts.PartNum fields for the information. My query works great and returns the information that I need. My problem is this:

I have a form based on the Parts table. I have a button on the form that runs an applyfilter macro based on my query. When I click on the button I get my runtime prompt for a value and then I get a prompt for the XrefParts.PartNum value. The only thing that I can think of is that it doesn't find the value because it is not part of the underlying form's table. The tables are linked in the query and I have also linked them in the Relationships view. I can not figure this one out. The mdb file is attached as a zip file to this post. Any help would be appreciated. Thank you.

James Thurber

 

[KingKong]

James,

I tried to take a look at the db you've enclosed, but couldn't open the file. Which version of Access do you use (A97,A2000,..)?

Grtz,

Peter.

 

[JamesThurber]

I use Access 2002 but the database should be in a 2000 format. I have attached a 97 version as well if that is what you need. Thank you for at least trying.

James Thurber

 

[KingKong]

Try this....

James,

Couldn't figure it out myself....
...but I think I found something!

Try this link:

http://support.microsoft.com/defaul...port/kb/articles/q166/6/34.asp&NoWebContent=1

It's an example of Filter by Form.

Grtz,

Peter.

 

[JamesThurber]

Got It

Thank you Peter. I actually wound up talking to someone locally and after discussing it I just used the recordsource option to change the form's recordsource from the table to the query, thus applying sort of a 'filter' (making sure that I had all the fields added in the query that were on the form). I then just added a button that would reset the recordsource from the query to the table and thus remove the 'filter'. It seems to be working pretty well so far. Going to test it some more before bringing it up on the network. Again, thank you for looking into this. That download will actually help in another db I am working on.

James