Combo Box Filter

[Richie27]

Hi all,



I am using a combobox filter on a mainform which has a subform.

I need to populate a combobox filter on a subform based on what is selected on a filter combobox on my mainform but no matter what I try I can't seem to get this to work.

I can populate the combobox filter on the subform using a normal combobox on that same form but can't populate the combobox filter on the subform using the filter on the main form.

Is what I am trying to do even possible?

I am aware that there is different syntax used to access a control on a subform and am 100% sure my syntax is perfect.

Thanks in advance,
Richie

[R_Badger]

Let me get this clear:

Definitions
You have a form (Frm_Main) with a subform (Frm_Main_Sub).
You have a combo box (Cbo_Main) located on Frm_Main
You have a combo box (Cbo_Sub) located on Frm_Main_Sub

Situation One
You wish to use Cbo_Main to filter the results of Frm_Main_Sub

Situation Two
Are you trying to replicate a filter across Frm_Main and Frm_Main_Sub

Situation Three
Are you trying to limit the options within Cbo_Sub based on the selection of Cbo_Main

Situation Four
Are you trying to change all records on your subform so that Cbo_Sub based to a new value dictated by Cbo_Main

Sorry but I can't be sure of what you are say, populating a filter by populating a filter doesnt make much sense.

Are any of these what you are trying to achieve?

[Richie27]

situation 3. Wow, fast reply!

Sorry if I was unclear - just been looking all day and my head is fried.

cbo Main is not a normal combo though. It is one you use to filter a form rather than store data.

[R_Badger]

I know the feeling of being fried believe me!

The solution is relatively simple in practice, however to write it (as things usually are) its a little complex

The trick is, you want to requery Cbo_sub rowsource every time you update Cbo_main.

You then set the rowsource of Cbo_sub to a query to suit your needs, utilising Cbo_main as the where condition.

To requery the rowsource of Cbo_Sub you need to use visual basic (put it in the on_change event on Cbo_main, right click properties, click in the white box next to on change, then the button with the three dots.)

the Vb would be something like
Private Sub Cbo_Main_Change()
Me!Frm_Main_Sub.Form!Cbo_sub.requery
End Sub

The top and bottom lines should be automatically generated for you.

Hope that makes sense!

[Richie27]

Hey thanks! I will give that a lash. Thanks for the swift reply!

I had decided to hang up my brain for the day but will give your suggestion a crack and let you know!

[Richie27]

Well it worked pretty well!! That's great. You just saved me a whole lotta brain sizzling and added another very useful tool to my arsenal!!

Any tips for learning VBA concepts, useful resources?

Thanks a million!!

[R_Badger]

I taught myself by looking up through the vba language reference, but (showing my age a little), I had a background in basic from an old BBC model B so it was probably a little easier.
The Access 2003 visual basic reference is available as a help file download (i had a quick look for the 2007 one, but that seems to be hosted on MSDN online)
http://www.microsoft.com/download/en...ng=en&id=19494

Another suggestion is take a look in the code repository here, and reverse engineer the code yourself, see what it does and then you should hopefully be able to understand how it does it.

Last but not least post back, if you have problems you can always just ask! There are people here who are far better at VB than I am!