Filtering a subform from a combo box?

[NewShoes]

Hey all,

Probably an easy question for the more advanced people on here! I have a subform that I would like to filter based on a combo box stored on the main form, just above the subform.

I've googled this but most answers seem to suggest using VBA. Not being that familiar with VBA I was wondering if this is possible in any other way?

Many thanks,
-NS

 

[vbaInet]

If you're using older versions of Access, 2003 and below, you can do so using the comb box wizard. Drop a combo box onto your form and select the 3rd option, then follow the instructions.

 

[NewShoes]

If you're using older versions of Access, 2003 and below, you can do so using the comb box wizard. Drop a combo box onto your form and select the 3rd option, then follow the instructions.

Oh no! I'm using Access 2007

 

[vbaInet]

The code isn't too difficult:

dim rst as DAO.Recordset

set rst = Me.[COLOR=Red][B]Subformname[/B][/COLOR].form.recordsetclone
rst.findfirst "[[B][COLOR=Red]ID[/COLOR][/B]]=" & Nz([COLOR=Red][B]combobox1[/B][/COLOR].value, 0)
if not rst.nomatch then
     me.[COLOR=Red][B]subformname[/B][/COLOR].form.bookmark = rst.bookmark
end if

set rst = nothing

I've highlighted the bits you need to change. Put that in the After Update event of the combo box.

By the way, is the ID field in the first column of the combo box? And is that the bound column?

 

[NewShoes]

The code isn't too difficult:

dim rst as DAO.Recordset

set rst = Me.[COLOR=Red][B]Subformname[/B][/COLOR].form.recordsetclone
rst.findfirst "[[B][COLOR=Red]ID[/COLOR][/B]]=" & Nz([COLOR=Red][B]combobox1[/B][/COLOR].value, 0)
if not rst.nomatch then
     me.[COLOR=Red][B]subformname[/B][/COLOR].form.bookmark = rst.bookmark
end if

set rst = nothing

I've highlighted the bits you need to change. Put that in the After Update event of the combo box.

By the way, is the ID field in the first column of the combo box? And is that the bound column?

Many thanks for this! The combo box will be area codes however, I assume I can just put the ID as the first column and hide it? ...also I'm not too sure on the bound bit....sorry, I'm still learning Access really.

 

[vbaInet]

So in the Area Codes table, what is the primary key there? If the ID you're referring to is the Primary Key in the Area Codes table then you can include it as the first column and hide it. Use the wizard for that. The wizard will also take the first column as your bound column so don't worry about that.

Is the ID a Number datatype?

 

[NewShoes]

So in the Area Codes table, what is the primary key there? If the ID you're referring to is the Primary Key in the Area Codes table then you can include it as the first column and hide it. Use the wizard for that. The wizard will also take the first column as your bound column so don't worry about that.

Is the ID a Number datatype?

You got it. The ID is an Autonumber.

 

[vbaInet]

So everything is set then. As long as the post code ID is also a field in your subform's record source it should work after you select an item in the combo. Give it a whirl.

 

[NewShoes]

So everything is set then. As long as the post code ID is also a field in your subform's record source it should work after you select an item in the combo. Give it a whirl.

Excellent, I will truy this on Monday and let u know. Just out of interest, what is the Nz bit in the vba code?

Thanks,
-NS

 

[vbaInet]

It simply substitutes Null values for whatever you put in the second argument. In your case I put 0 because it's a number (that will never be in your autonumber field but isn't Null because Null isn't a number).

Have a read:

http://www.techonthenet.com/access/functions/advanced/nz.php

 

[NewShoes]

It simply substitutes Null values for whatever you put in the second argument. In your case I put 0 because it's a number (that will never be in your autonumber field but isn't Null because Null isn't a number).

Have a read:

http://www.techonthenet.com/access/functions/advanced/nz.php

Great, I'll give it a try and let you know

 

[vbaInet]

Happy coding

 

[NewShoes]

This worked great, thanks!

 

[vbaInet]

You're welcome.

 

[NewShoes]

Hi,

Just as a follow up to this. I have just (stupidly) realised that this doesn't do exactly as needed. It would like the combobox to actually FILTER the subform, not simply find the first record. i.e. If I select ABC from the combobox, I would like to see just the records with ABC.

Hope you can help

-NS

 

[vbaInet]

Is the main form unbound?

 

[NewShoes]

Is the main form unbound?

I guess so. How would I find this out? .....It's basically just a simple form that has the subform in it and a combo box.

Thanks,
-NS

 

[vbaInet]

By bound I mean is the main form linked to any table or query. You will find this in the Record Source property of the form itself.

It's not crucial, I just wanted to know.

Has the combo box got the ID that you want to filter the subform by? And is it the first column in the combo box?

 

[NewShoes]

By bound I mean is the main form linked to any table or query. You will find this in the Record Source property of the form itself.

It's not crucial, I just wanted to know.

Has the combo box got the ID that you want to filter the subform by? And is it the first column in the combo box?

Oh right - yes, it's unbound (no record source on the main form). The combo box has the ID I want to filter in it and it is the first column in the combo box. The code you posted earlier works but I just need to filter rather than find the first. I did try to change FindFirst to Filter but got an error!.

Cheers,
NS

 

[vbaInet]

That's fine. This is what you do:

1. Click the the subform control once and in the Link Master Fields property (under the Data tab of the Property Sheet) type in the name of your combo box. Don't click the button that will open up a pop-up, just type it in.
2. In the Link Child Fields property type in the name of the ID field.

That's it.