Form Filters In VBA To Improve UI

[martie_access]

Good Morning,

I am just getting back into using access after 17 years so am very rusty....but I am suprised I'm stuck on this.

What I am trying to achieve is that when a user clicks on an image (being used as a command button) in a form it will open up a multiple items form filtering one field with a specific parameter...the user finds what they want....close the form down.....clicks on another image....same form loads with a different parameter. This is to enable them to browser components in a storage location by category in a more visual way (Rather than just a combo box).

This is what I have:

1) A blank form called "Frm_Comp_Cat_Images". This form contains various images of components e.g. capacitors - these are my command buttons so one is called "CmdCapacitors"
2) A multiple items form called "Frm_Browse_Components. This form is populated from a query called QryCompBD (was origonally gettin data direct from the table but was trying to figure out the filter). The field i want to filter from within this query is ComponentCategory.

What I want to achieve:
When clicking CmdCapacitors I want Frm_Browse_Components to open (pop up) and pass the filter text "Capacitors" into field "CompoentCategory" and display all components of that category....when the user has finished then can close this form and then click another image which will pass a different filter text into the same form.


Any Thoughts?

 

[CJ_London]

you use the where parameter for docmd.openform something like this in the click event of CmdCapacitors

docmd.openform "Frm_Browse_Components",,,"CompoentCategory='Capacitors'"

 

[martie_access]

docmd.openform "Frm_Browse_Components",,,"CompoentCategory='Capacitors'"

Hi CJ,

Thanks for this. I've tried this ans get a runtime error '3434' = Data type mismatch in criteria expression. The field "ComponentCategory" in "Tbl_ComponentDetails" is a lookup list contained in "Tbl_ComponentCategories" therefore making the field classed as a number (i guess it stores the ID).

The syntax looks right from other google searches.

 

[martie_access]

Hi CJ,

Thanks for this. I've tried this ans get a runtime error '3434' = Data type mismatch in criteria expression. The field "ComponentCategory" in "Tbl_ComponentDetails" is a lookup list contained in "Tbl_ComponentCategories" therefore making the field classed as a number (i guess it stores the ID).

The syntax looks right from other google searches.

Apologies hit post too quickly.....ive just proved that very point by entering a number....which then passes into the other table so this code works. Thanks.

How would I look up a text value in one table and return its ID?

 

[CJ_London]

dlookup("ID","myTable", "TextField='textvalue')

 

[martie_access]

CJ - Brill

All sorted with the below code. My god - I used to know this stuff!!! Back to school for me. Thanks for your prompt response!

Dim Category As Integer
Category = DLookup("ID", "Tbl_ComponentCategories", "Category = 'Capacitors'")
DoCmd.OpenForm "Frm_Browse_Components", , , "ComponentCategory=" & Category

 

[CJ_London]

you can simplify your code

DoCmd.OpenForm "Frm_Browse_Components", , , "ComponentCategory=" & DLookup("ID", "Tbl_ComponentCategories", "Category = 'Capacitors'")