Combo Box dilemma

maw230

somewhat competent
Local time
Yesterday, 18:25
Joined
Dec 9, 2009
Messages
522
i need to create a form that is based of off information in a table. there needs to be 7 combo boxes that will filter this information based on any combination of the selected combo box values, or if none are selected a summary of all the data.
i have the created the form with the table. i have made several attempts with the combo boxes and have managed to alter the table values when using them, but not how i would like.
my biggest concern is how to make the combo boxes function properly. i tried writing SQL statements for each box in the Row Source section of the Property view (using the Query Builder) but have had only mixed results.

this problem has been troubling me for about a week now as i cant seem to find the best way to make this work. i know it's a topic of frequent discussion when it comes to forms, so im sure someone here can help :confused::confused:

please tell me if you need additional info on the problem. thank you thank you.

edit:using Access 2007
 
Last edited:
this looks good if i were using subforms, but the data in my form comes from a single table, so i dont think subforms are necessary (?)

this code looks to be what the combo boxes use to filter the data:

Code:
Public Function fCboSearch(vCboSearch As Variant)

    If IsNull(vCboSearch) Or vCboSearch = "" Then
        fCboSearch = "*"
    Else
        fCboSearch = vCboSearch
    End If
    End Function

but im not entirely sure as my VBA knowledge is not very high. is this part of the code i need to use? i dont know where to begin.
 
I use an unbound form with a series of combo boxes and coding to display the results. The user does not have to select the combo boxes in order, as each combo box is select the records are filtered to give the values in the other remaining combo boxes etc.

If you like I could post an example within the next few days.
 
poppa, that would be great to see an example. im not even worried about the combo box criteria being filtered depending on what is selected, but it would be interesting to know how. i just want to figure out how to filter the table data first, using any combination of the combo boxes.

the only potential problem i see is that one of these combo boxes will theoretically have tens of thousands of numbers as they are different Item numbers for many different products. i dont know if that would be a problem or not when trying to filter the combo box selection choices...
 
Send me a copy of your relevant tables, remove any confidential items and list your requirements and I will try and give you some help.
 
here is a dumbed down version of the only table that the form needs to control. and here is what it should do.

this table has some 20+ fields including:
Line, Item Code, Category Subcategory, Analyst, Subcode;
sales, cost, units, etc.
where the former group need to be the criteria filters for the combo boxes.

The form will contain this table and the combo boxes simply need to filter it down in a easy to view summary of the data. if the user selects any combination of the boxes, the table will need to update with only information with that combination of criteria. if the user selects none, there should be a summary of all the data.
thanks in advance.

**well, in trying to upload the DB i get an invalid file error. Should i try posting the table in excel?
 
Send it as a zIP file include your table and form. A mdb attachment has a file limit of 785Kb
 
smurf, here is a quick version of what the form should look like with the combo boxes in the header. sorry for taking so long to get it together.
the combo boxes should be able to be used in any combination and in no order to filter the table data and return the filtered data back in the table below, preferably in datasheet view. Thanks again!
 

Attachments

Would it be possible to convert it to 2003 version as I only have access 2003 not 2007?
 
Thank You.

You require the data to be displayed as per your layout after the user selects one or more of the following combo boxes PLCD, SUBC, ITEM, and LINE.

I should have a result for you maybe Sunday after our Christmas.
 
In the attached zip file I have three examples of searching with combo boxes.

I have added a button to your form to allow the user to do a New Search this clears all the entries in the combo boxes.

frm_input is a form that uses code to display the results in a single form view in record.
frm_input_2 is a modification of a sample database created by AT Hine HotWare Ltd Email ath@hotware.co.uk. This displays the data in a datasheet view and uses the criteria of a query to display the results. Note I had problems when I selected the item combo box as the result would not display. I overcame this by updating the ITEM field by using TRIM([ITEM]).
frm_input_3 is the same as frm_input but the display is in a datasheet view.

If you need further assistance please contact me on the email address below.
 

Attachments

thank you so much smurf. ill take a look at these today and see what i can do.
 
Hi there

I have been taking a look at your example today as I am looking to do something similar with combo boxes etc.

As this is the first time I have ever used modules in Access I have a question relating to the drop_down_list module you use to find the records entered in the table.

Looking at the code you state the qry name (qry_input). If I were to use this in a different form on a different query, would I need to create another module and change the qry or is there some way of making modules generic so it can be used on multiple queries rather than re-creating modules?

The same question applies to the find_records module as well.

Thanks
 
I have been playing around again and I do not understand why the gotfocus event is being used as the same results are achieved by setting the rowsource as:

Select Distinct PLCD from qry_input where PLCD is Not Null

I cannot see that the combo boxes are refreshing themselves if one combo box is used first, so why is the got focus being used?

This is only used in input 1 and 3, not input 2.
 
try to PM poppasmurf, as he is the one who built it.
 
djphatic

I have been playing around again and I do not understand why the gotfocus event is being used as the same results are achieved by setting the rowsource as:

Select Distinct PLCD from qry_input where PLCD is Not Null

This example is NOT A CASCADING Combo box where you select the from the first combo box and the values in the second box are based on your selection in the first. This example allows the user to select a value in any box and the records are displayed, say for example you have two combo boxes the first is Gender the second is Surname in a cascading setup you will need to select Gender then Surname to display a Surname. How would you be able to display the Surname without knowing the gender? In my example you can display the surname then select gender if required.

I cannot see that the combo boxes are refreshing themselves if one combo box is used first, so why is the got focus being used?
This is only used in input 1 and 3, not input 2

Input 1 and 3 use the same code but the layout is different input 1 uses a form layout. Input 3 uses a Datasheet layout.

Input 2 uses a subform with a different method for displaying the results it uses a query "qry_input" and not a module.
 
Thanks for your response. I looked at it abit closer and I realised what was going on with the module. I didn't notice it at first.

I have been able to change the code to my own requirements though, thanks for sharing.
 

Users who are viewing this thread

Back
Top Bottom