Using Combo Box to Filter Form Data

[TommyIndigo]

I have a customer inquiry form that is read-only. My client wants to be able to basically filter by selection using combo boxes (and not Access' filter by selection buttons). Unfortunately, he specifically wants to do this with ONE form (not have a separate form to gather parameters). Also, he wants every field to have this functionality (otherwise, I'd try a form/subform solution).

I'm attempting to have the After Update event perform the following:
1. store filter string based on new value in NAME field
2. Undo the name field, so it doesn't change the current record
3. Re-open the form, using the new filter

Since I am dynamically changing the FILTER property on the form, I am successfully able to see that the string propagated correctly. However, the filter never finds any records...which makes no sense to me.

Here is my code:
Private Sub Name_AfterUpdate()
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmInquiryMain"
stLinkCriteria = "[Name]=" & "'" & Me![Name] & "'"
Me.Undo
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

If anyone has any suggestions, it would be MOST appreciated!!

Thanks,
Tom

 

[cmmrfrds]

This is kind of a guess, but if you are opening an already open form It may not go through the onopen event. Try closing the form and then opening.

me.close
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[sudhirkr]

Hi Tommy,

I would like to carry forward what our dear friend cmmrfrds just mentioned. Open and close the form not in that form but in a different place(preferrably call a module in your form where the close and open events for the form can be coded)..

hope this resolves your problem..

Sudhir.

 

[TommyIndigo]

That sounded like a good idea, but it didn't work. I was able to have the module function do the same thing, but it returned the exact same result. What could be happening here??? Much thanks for the ideas!

 

[sudhirkr]

Tommy,

see what criteria you are using for loading the form and see whether the relevant values(used for your form load) are getting lost while closing and opening..

if it is so, store those values in public or global variables before closing the form and open again using the stored values...

the relevant value for example, may be the value you choose in one of the comboboxes in your form....

Hope this works...

cheers,
Sudhir.

 

[RemyS]

TommyIndigo,

I personally prefer to use Query criteria than filters on forms (only using Filters for rapid ad hoc tests)

The simplest method is to create a query on the table, and use this as the Data Record Source for the form.
In the form create all the combo boxes for each field with an On Got Focus event selecting the distinct Field values from the Query (to be able to filter within filter) for that combo boxs' RowSource, and an AfterUpdate event to requery the form.

Remember to include a reset button, to reset all the comboboxes values to a default "" (or a descriptive text, which requires an Iif clause in the Querys' criteria) and requery the form again. Or you could include a reset button for each combo box.

This makes the filtering very versatile and simple.
It does require the form to be editable, so you would have to lock the relavent text boxes. though enabling them still allows copying from the form.


Hope this helps & good luck
Remy Still new to DB's and enjoying learning day by day

 

[RemyS]

Forgot to mention to include the combobox values as criteria in the query. Still new to DB's and enjoying learning day by day

 

[WildHare]

Tommy - you should be able to do this with much less fuss than re-opening and closing the form.

1) Write a very small function, placed in a standard module with the following code:
---------------[ cut here ]-------------------

Public Sub SetFilter(fieldName as control)

On Error Resume Next
Set frm = Screen.ActiveForm

With frm
.Filter = FieldName + " = ' " & Screen.ActiveControl.Value & " ' "
.FilterOn = True
.Requery
End With

Set frm = Nothing
End Sub
-----------------------------------------

When you want to apply a filter, as in the After Update even of your combos, call this function thusly:

Sub CboName_AfterUpdate
Me.FilterOn = FALSE
Call SetFilter(me!Name)
End sub

IOW, with the name of the actual field control you want to filter on passed to the function. This is a bastardization of a little demo I have on my site that allows you to filter and sort on any values on a continuous form - you might find it useful.

The above works for standard text values. You'll need to make slight changes for numbers and dates in the ".FILTER = " line. I actually have three separate functions - setFilter, SetFilterN and setFilterD

For numbers, just take OUT the single quotes you see in the ".Filter = " line.

For DATES, substitute HASH MARKS # for the single quotes

Putting these little functions in a standard tools module allows you to add sorting and filtering functionality very easily to any form in any database you do. As I said, I altered what's on my site, but I think it should still work with the field name call.











78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[TommyIndigo]

Thanks everyone for the great advice! As usual, the answer was right before my eyes, but I went down a rathole of exploring more complex solutions.

My problem was that the NAME combo box's bound column was the primary Key ID for the customer's table (and NOT the name of the customer which is visible in the combo box). This means my filter was looking for a customer NAME with the selected customer's NUMBER, which of course returned no records.

I did use one of WildHare's concepts to not re-open the form, but rather simply reset the filter. This was a cleaner strategy. I understand the other suggestions to do this via queries, but since I had the filtering structure already setup I went that route.

Thanks again for all the replies...they really helped!!