combo applying filter

[awesomet]

Hello ...

Need help in applying a filter to a form. The info is sitting in a sub form (dataview) and I have a few various drop down boxes that I have with a few value lists in them and would now like to set up a filter after update action in them...

I am here at the moment with no idea:

Private Sub section_Change()

Dim Section As String

DoCmd.ApplyFilter , "section = [section]"


End Sub

 

[misscrf]

What combo boxes are on your main form? Do any or all of them make up a unique connection to the subform? It is ideal to drop in the subform and Access should link the 2 forms together based on their unique identifiers that connect each other. If you want them to connect differently I would try something like this ( sorry I am horrible with coming up with code on the fly)

afterupdate comboname()

forms!formname!subformname!requery

Were does the data come for your combo boxes? Did you supply them with the lists, or do they come from an underlying table. What is their row/source? This makes a difference to how they will be able to speak to the subform.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[awesomet]

The combo boxes contain values of a control source of the sub form if that makes sense....

IE: drop down box is a list of companies and I would like to filter the sub form by the name of the selected company in the drop down box....

The drop down boxes have values in them that appear in a control source in the sub form...

Hope Clarity is better...

 

[misscrf]

ok, but now we have to clarify other stuff lol. The main form, is it unbound, or does it have a control source? How is the subform set up? Does it look like a datasheet? I don't know exactly what data you are looking at, but if filtering, that might be ideal. The form needs to at least be set to continuous if not datasheet, and should cycle all records.

The you just need an after update to requery the subform where combo = company field on subform.

Another way, might be to make the subform control source, a query. Then make the fields group by, and make the company field a where, ( you'll need 2 fields probably, 1 to show in the report and 1 for the where ) and put the combo control in the criteria field. If you need an example, let me know and I will create on for you.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

here you go:

http://www.geocities.com/misscrf/combofilter.mdb

It is all on one form.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[awesomet]

Hi Misscrf

Thank you..the subform is set up as a subform datasheet.

You mentioned datasheet being ideal and requerying the subform where combo= comany field on subform...? Can you then help with the following where condition:

Forms![awesome_home]![section1].AfterUpdate = Forms![awesome_home]![awesome subform].Form![Section]

Or am I totally on the wrong track with the expression ?

 

[misscrf]

why are you referencing section1 and section? what are those?

Shouldn't it be the combo = companyname field on the subform?

The syntax for that will be tricky I think, why? cause the combo is actually grabbing the number of the company identifier but you are trying to match that up to the text of the company name. It might be select * from table where combo.companyname = companyname

It should be easier than this I am thinking, so I would check around for samples - google combo filter subform.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

found this bit of info off another site:
Forms![frm_Main].[frm_Subform].Form.Requery you also
need Forms![frm_Main].[frm_Subform].Combo.requery. Requerying
a form
does NOT requery any contained combos or lsitboxes.

Phil,
That was the answer. Thanks very much.
Barry

--------------------------
found this too:

erroldou
Hi,


I have a combobox on form (frmSearchRecord) to select a name. I select a name and click the 'search' button which opens a new form (frmMembers) which is filtered to the record(s) with that name. Using the access wizard, one obtains the following code:

----------------
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmMembers"

stLinkCriteria = "[Surname]=" & "'" & Me![Combo3] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "frmSearchRecord"
----------------

I've now added a second combo box to my search form, so that I can choose from another list (cities). When I click 'search', I'd like the (frmMembers) form which opens to be filtered by both the surname and city that I selected. How can I change the above code to make this work. I've tried various tactics, but nothing works as yet.

Thanks, Errol.



--------------------------------------------------------------------------------

Ian Mac
You could filter the second part:


Dim stDocName As String
Dim stLinkCriteria As String
Dim stFilter As String


stDocName = "Form1"
stLinkCriteria = "[Surname]= '" & Me![Combo3] & "'"
stFilter = "[City] = '" & Me![Combo4] & "'"

DoCmd.OpenForm stDocName, , stFilter, stLinkCriteria


What may be even easier is designing a Query with Criteria built in

i.e.

Forms![frm_MainForm]![Combo3] for the Surname
Forms![frm_MainForm]![Combo4] for the City

Then base you second form on this Query.

Hope this helps,

--------------------------------------------------------------------------------

Newman
The best way is by using this procedure in a module:
Public Sub AttacherTexte(ByRef TexteInitial As String, ByVal NouveauTexte As String, ByVal Separateur As String)
If TexteInitial = "" Then
TexteInitial = NouveauTexte
Else
TexteInitial = TexteInitial & Separateur & NouveauTexte
End If
End Sub
And calling it from your Search button procedure using these lines:
If not isnull(Combo3) then AttacherTexte stLinkCriteria, "[Surname]=" & "'" & Me![Combo3] & "'", " and "
If not isnull(Combo4) then AttacherTexte stLinkCriteria, "[City]=" & "'" & Me![Combo4] & "'", " and "
This way, you will only have to add a line for every other combobox you may want to add in the future.

--------------------------------------------------------------------------------

erroldou
Thanks for the help - It looks like I got it working ok now.

Cheers,
Errol



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[awesomet]

ouch even after all that I am still not getting it...

OK no new form I want it to stay in the same form...

have this now:

Private Sub section1_Change()
Dim section1 As String

DoCmd.ApplyFilter , "section1 = 'Section'"

End Sub

section1 is the combo name
section is the field name / control source in the sub form...

any closer...?

Thank you

 

[misscrf]

You can have it stay in the form, this is just an example of it working in a different way...

did you get that to work?

I tried this :

Forms![frmmain]![companysubform].RecordSource = "SELECT * FROM company " _
& "WHERE [comanyname] ='" & Me![Combo0] & "'"

Forms![frmmain]![companysubform].Requery

End Sub



but it says that I can't use that method ( recordsource) that way. Keep trying.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[awesomet]

Noep , nothing yet...resorted to the opening a new form idea and displaying the results from the combo...

Dim stDocName As String
Dim stFilter As String

stDocName = "awesome_view"
stFilter = "[Section] = '" & Me![section1] & "'"
DoCmd.OpenForm stDocName, , stFilter

However still something is wrong ???