Filter a form based on combo box

[echorley]

On my switchboard, I have a combo box where I select a team to keep football statistics for. I then click a command button to open what type of statistics to input (offense, defense or special teams).

When I arrive at the new form, I would like to have it filtered so that only the records for the team I have selected in the switchboard show up.

I have scoured the board and tried a lot of ideas, but nothing seems to work.

Thanks for any help.

 

[echorley]

Got it!

Dim Opponent As String

Opponent = Forms![Switchboard Items]![Opponent]
DoCmd.ApplyFilter , "[Opponent] LIKE '" & Opponent & "*'"

I have to admit, I do not know what the "*" wildcard stands for (extra columns maybe?), but this filtered my forms successfuly based on a combo box selection.

 

[richardplr]

Re: Got it!

Dim Opponent As String

Opponent = Forms![Switchboard Items]![Opponent]
DoCmd.ApplyFilter , "[Opponent] LIKE '" & Opponent & "*'"

I have to admit, I do not know what the "*" wildcard stands for (extra columns maybe?), but this filtered my forms successfuly based on a combo box selection.

Hi,

I think I have the same problem but I don't know VBA code. can u show me physical example and upload here.

Tks

 

[Calvin]

echorley,

I have to admit, I do not know what the "*" wildcard stands for

The "*" wildcard is just that, notice that your using a LIKE command in your filter statment, your filter will return everything that starts with what you are passing it from the opponents combo box on your switchboard form.
Because your using a combo box to control the filter data your probably not seeing the full effect of the LIKE command.

I don't think you actually need to use LIKE and it would be faster if you used the following:

DoCmd.ApplyFilter , "[Opponent] ='" & Opponent & "'"

but because you are still actually loading all of the records then filtering what you need if you used something like the following instead might be more what your looking for:

insert this in your form load or open event

Opponent = Forms![Switchboard Items]![Opponent]
me.rowsource = "SELECT * FROM MyTable WHERE [Opponent] ='" & Opponent & "'"

this way you will only load the records you want from the begining and will not need to use a filter later.

 

[MSAccessRookie]

Where and how to insert the VBA code

This concept looks like something that I could use as well on my problem with finding the next record in my WorkOrders Form. My question is regarding where and how to insert the VBA code to do the job. Do I insert it as an event (For instance "After Update" or "On Change") behind the Combo Box, or is there another way to do this? Also, won't I need to do a requery of the data for the WorkOrders Form before I do the serarch?

Thanks in advance to all who reply

 

[MSAccessRookie]

Looks like I found it after all. I modified each of the affected fields so that they would apply a filter to the form after they were updated. See examples below for researchers and institutions:

DoCmd.ApplyFilter Me.Form.Filter, "((Lookup_ResearcherID.Expr1=""" + [FindPerson] + """) AND (qryWorkOrders.InvoiceNumber Is Not Null))"

DoCmd.ApplyFilter Me.Form.Filter, ((Lookup_Institution__ID.Institution=""" + [FindInstitution] + """)"

Thanks again for everyone's assistance