Filter records based on value on combobox - MSAccess

[May2000]

I need to filter records based on the the text value of a combo box and as the value in combo box changes, the filtered records need to be updated accordingly. Can somebody share ideas on how to proceed and perhaphs give a sample codes for me to work on.

Your help is very much appreciated.

Thanks in advance,

May2000

 

[MajP]

Here is an example I just posted.

http://www.4shared.com/document/a-mEoboo/SubForms.html

The easiest is to use a subform. Requires no code.

 

[MajP]

I should have mentioned, look at the
linkMasterFields
linkChildFields
properties of the subform control

 

[May2000]

Thanks MajP.

I will thy this, hope that this will work in my case. Thanks.

May2000

 

[MajP]

This method is the easiest because it requires no code.

The second method is to build a filter string based off the value of the combobox and filter the form.

The third method is to build a filter string and use it in a query string. Use the query string as a new recordsource of the form.

 

[May2000]

It seems that sub form will not work for me. What I am trying to do is show/filter all records based on the value of the combo box. The "value" in combo box could appear in any of the fields of the table. I guess I did not state this clearly before. Thanks.

May2000

 

[MajP]

Do you really want to filter on "all" fields or some subset of fields. To me that does not make any sense. First I have to guess your table is not properly designed. Why would you have the same type of information in multiple fields? I would not expects "last Name" information to possibly appear in any field, or "ship date" information to appear in any field. Unless possibly the main form is built of a xtab query. Second do you really want to look for string informaition in a numeric or boolean field or vice versa?

What you are asking can be done, but first can you describe this with a little more detail? Can you describe the fields in your table and the data in the combo. Maybe there is a better design then really searching all the fields.

 

[May2000]

My form has has an option group with 5 options (Manufacturer, Category, Sub Category, Product Family and Special) an unbound combo box which values is coming from different sources (see Relationship attached) depending on the selection made on the option group (e.g. if option 1 is selected (Manufacturer, the combo box will show selection of "manufacturer" - see attached graphics).

The table has fields (see relationship - attached).

What I want to happened is that when:

Option1 (Manufacturer is selected the Combo box will show the list of "Manufacturer" (This part I've already done with the help from Tek-Tips, Thank you!) and then Filter the data based on the selected "Manufacturer"

And if

Otion 2 is selected (Category), Combo box will show list of Category choices and Filter the data based on selected Category.. an so on...

I hope this will help you to help me!

Thanks,

May2000

 

[MajP]

OK that makes way more sense. That is not the same as searching every field aimlessly as you described. In the future take the time to craft a good question to save you and responders time.

Your link is no good. But this is the general ID. It is untested, but the logic should be correct

1) Never Ever make any object names in Access with spaces
"Product Family" should be "ProductFamily"
This only gets you into trouble.
2) sql strings need to resolve like this
ProductID = 123
ProductName = 'Some Name'
ProductDate = #12/1/2010#
I wrapped all your field values to assume they are strings. But if they are number fields then
& getQuotes(cmbo)
would simply be
& cmbo

private sub cmboFilter_afterUpdate()
  dim strFilter as string
  'clearing the filter may not be required
  'but I do this out of habit
  Me.Filter = ""
  Me.FilterOn = False
  strFilter = getFilter
  if not strFilter = "" then
    Me.Filter = strFilter
    Me.FilterOn = True
  end if
end sub

private Function getFilter() as string
  dim opt as access.optiongroup
  dim cmbo as access.cmbo

  set cmbo = me.cmboFilter
  set opt = me.yourOptiongroupname

  if isnull(cmbo) then exit function
  select case Opt.value
   case 1
     getFilter = "Manufacturer = " & getQuotes(cmbo) 
   case 2
     getFilter = "Category = " & getQuotes(cmbo)
   case 3
     getFilter = "[Sub Category] = " & getQuotes(cmbo)
   case 4
     getFilter = "[Product Family] = " & getQuotes(cmbo)
   case 5
     getFilter = "Special = " & getQuotes(cmbo)
  end select
end function

Public Function getQuotes(str As Variant) As String
  getQuotes = "'" & str & "'"
End Function