Combobox filter

zozew

Registered User.
Local time
Today, 17:06
Joined
Nov 18, 2010
Messages
199
Im having problems with the syntax,

Can someone just point me to a simple VBA example of a combobox with list values that filter a form on the value selected in the combobox.

The combobox is on the form its suppoused to filter.

Sorry for such a simple request but im an old flashdeveloper and im going crazy trying to figure out this simple thing

thx
 
Drop the combo box onto the header section of your form, when the qizard pops up select the third option which should read "Find a record on my form based on the value I selected in my combo box", follow the wizard till the end.

The form must be bound (i.e. linked to a table or query) before you perform the above.
 
Hi thx for the quick reply,

I probably didnt explain well enough...

I want to add three options to the combobox through a value list. Three short strings that are corresponding to three possible values in a field in the table that the form is linked to.

When i choose one of these strings in the combobox, it filters all the records and just shows the records with the corresponding string in the field.

i hope this explains it.

In flash i would do something like this:

combobox.onChange(){
tempVal=Combobox.value
if(tempVal==AA){
filter AA
}else f(tempVal==BB{
filter BB
}else f(tempVal==CC){
filter CC
}
}

ofcourse the filter function would be more then just a word :)

But im having problems with the syntax...so a short example where a combobox has items in a value list and filters a form onChange (after selecting a new value) would help me with the syntax.

Note. I now have three buttons with this:

DoCmd.ApplyFilter , "CustodyClassification = 'Insular'"
Insular, City, Det are the three options available in the CustodyClassification field

Basically i want to combine the three buttons i have into a single combobox....


thx again
 
Last edited:
Have you looked into what I mentioned? The wizard will take care of the code.
 
Hi yes i did, but as i have multiple record with one of the three options i get a combobox with tons of duplicates. I just want the three options available....and that example just shows that specific record in the form and does not perform a filter in the table....

So instead of having three buttons doing the work i just want a combobox with three options that filter all records....
 
That's fine, you can get rid of the duplicates after you've set it all up.

So once that's set up, to get rid of the duplicate records, go to the property sheet of the combo box and under the Data tab, place your cursor in the Row Source property and click the elipsis button (...). Open up the property sheet for the query and set the Unique Values property to Yes. Open it up in Datasheet view to see the results, save and close.
 
and that example just shows that specific record in the form and does not perform a filter in the table....
So you want a continuous list of records based on what is selected. Just set the form's Default View property to Continuous Form.

If you want a datasheet view instead then the approach would change.
 
ok, i dont follow fully but ill try, i still dont see how it will sort the the table and update the form so it only shows the records with the string in the correct field. Cuz when i select a value in the combobox it selects that specific record and shows it in the formm....but ill try your method and see where it leads me :)
 
So you want a continuous list of records based on what is selected. Just set the form's Default View property to Continuous Form.

If you want a datasheet view instead then the approach would change.

Sorry for being confusing,

I have a table with profiles of people, they are categorized by three types (the three strings) in my form i want to filter the profiles based on these thre strings so the form shows all profiles with that type.

My form only displays one record at a time but i want the base for that form to be filtered when you search or step through one by one.

I got the functionality with the three buttons and applyFilter
DoCmd.ApplyFilter , "CustodyClassification = 'Insular"
DoCmd.ApplyFilter , "CustodyClassification = 'City"
DoCmd.ApplyFilter , "CustodyClassification = 'Det"

i just would like it to be done through a combobox....its much neater and i have a problem with space in my form....
 
Basically, if you select "Insular" in the combo box you want it to only show people or profiles that are classifield as Insular on the form right? That's exactly what I explained in my first post.

The field where the classifications have been entered must be present in the form's record source.
 
Basically, if you select "Insular" in the combo box you want it to only show people or profiles that are classifield as Insular on the form right? That's exactly what I explained in my first post.

The field where the classifications have been entered must be present in the form's record source.

I tried and got as far as saving the query, but when i run the form i get a combobox with three blank options, the query did return the unique three values though....? but how do i sort the the table with these three options if i get the combo to show them...
 
Hi again,

I did solve it with this VBA code

Private Sub Combo33_AfterUpdate()
Select Case Me!Combo33.Value
Case "Detainee"
DoCmd.ApplyFilter , "CustodyClassification = 'Detainee'"
Case "City Prisoner"
DoCmd.ApplyFilter , "CustodyClassification = 'City Prisoner'"
Case "Insular Prisoner"
DoCmd.ApplyFilter , "CustodyClassification = 'Insular Prisoner'"
Case "All Prisoners"
DoCmd.ShowAllRecords
End Select
End Sub


And i just added the strings into the value list and this code to the AfterUpdate....

Maybe i didnt explain myself well, im very new to access and the new syntax, so my biggest problem is not designing the structure its just getting the right syntax to get the right info :)

thx though for all your fast replies....
 
Hi,

I understood you quite alright and I know that you can use (or write) vba to achieve the desired outcome but I didn't feel it was necessary for you to write the code yourself if the system does it for you. There are actually several ways of doing it. What I proposed to you is a much more effective way of filtering your records because it uses the form's Recordset. A more direct method (in comparison to the ApplyFilter method) would be to use the form's Filter method which is Me.Filter, then turn it on using Me.FilterOn = True

When you have time, try out the method I proposed again.
 
Another alternative to a combo box providing you are only ever going to have a few options, and you have room on your form is to use an option group.

Ideally you would not have the string in your field but have a foreign key, say 1 to 4 linked to your child table which holds the primary key and the description. Then on the on click of the option group use your Select Case statement annd the Me.Filter method explained earlier.

Private Sub OptionGroup_OnClick()

Select case Me.OptionGroup

....
....
...

End Select
 
Hi,

I understood you quite alright and I know that you can use (or write) vba to achieve the desired outcome but I didn't feel it was necessary for you to write the code yourself if the system does it for you. There are actually several ways of doing it. What I proposed to you is a much more effective way of filtering your records because it uses the form's Recordset. A more direct method (in comparison to the ApplyFilter method) would be to use the form's Filter method which is Me.Filter, then turn it on using Me.FilterOn = True

When you have time, try out the method I proposed again.

I think i will try your method again, i know there are always many ways of solving a problem and through coding it at least feels like you have more control of whats going on.

So lets say this works out, i can filter the recordset of the form with the 3 types. If i then want to filter again according to any other field, like commitDate or eyeColor etc will the previous filterring be reset?? I did soem quick testing by using the right clcik filter options straight in the fields after i choose type and it did some wird things...


thx again
 
Another alternative to a combo box providing you are only ever going to have a few options, and you have room on your form is to use an option group.

Ideally you would not have the string in your field but have a foreign key, say 1 to 4 linked to your child table which holds the primary key and the description. Then on the on click of the option group use your Select Case statement annd the Me.Filter method explained earlier.

Private Sub OptionGroup_OnClick()

Select case Me.OptionGroup

....
....
...

End Select


Yeah i did consider that but a combobox did seem like the least intrusive and most expandable solution, i might get more types(strings) and that would mess up the layout :P

Thx for the suggestion though
 
I think i will try your method again, i know there are always many ways of solving a problem and through coding it at least feels like you have more control of whats going on.

So lets say this works out, i can filter the recordset of the form with the 3 types. If i then want to filter again according to any other field, like commitDate or eyeColor etc will the previous filterring be reset?? I did soem quick testing by using the right clcik filter options straight in the fields after i choose type and it did some wird things...


thx again
I've just had a rethink of what I was advising and I just realised it wouldn't do what you want :eek: Apologies!! I was actually quite busy at work so wasn't focused there:)

Use this:
Code:
    If Len(Me.Combo33.Value & "") <> 0 And Me.Combo33.ListIndex > -1 And _
       Me.Combo33.Value <> "All Prisoners" Then
       
        Me.Filter = "CustodyClassification = '" & Me.Combo33.Value & "'"
        Me.FilterOn = True
    Else
        Me.FilterOn = False
    End If

If you want to add more conditions then you simply add an AND or OR in the condition. Remember to check if they contain text.
 
Thx again it works perfect. I decided to start naming my object a little more descriptive :)

Private Sub TypeCombo_AfterUpdate()
If Len(Me.TypeCombo.Value & "") <> 0 And Me.TypeCombo.ListIndex > -1 And Me.TypeCombo.Value <> "All Prisoners" Then
Me.Filter = "CustodyClassification = '" & Me.TypeCombo.Value & "'"
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End Sub


But just a quicky....you mentioned that this is "better" then using the applyFilter....is it faster or does it have any other benefits?

thx again for the speedy replies
 
ApplyFilter is the general function that can be used for the active (relevant) object, but Me.Filter is a direct method. So as a programmer, you know the benefit of calling a function or operation directly.

Here's a link re naming conventions in Access:

http://www.acc-technology.com/namconv.htm

For example you could prefix the name of your combo box with cmb, the name would be cmbTypeCombo
 
For example you could prefix the name of your combo box with cmb, the name would be cmbTypeCombo

Thx for the link. ill start reprogramming my brain the access way, btw i checked the page and "cmb" is not used only "cmd" command button, "[FONT=arial, helvetica]cbo" is the comboBox :cool:

thx again, you've really helped me, i'm starting to understand the syntax even if its only the beginning

p.s Im building a jail logistics system as my first project in Access 2007 so its kinda fun and complex. You don't happen to know how to mailmerge images from the attachment field into word..?? I spent a few days trying to figure that one out and no one really knows how. No problem with the text data that's done i would just like to get the inmates photo at the same time instead of manually adding it to the created word doc.
[/FONT]
 

Users who are viewing this thread

Back
Top Bottom