Show filtered form records in a combo box (1 Viewer)

[Keith Nichols]

This sounds like a cascading combo box issue but it isn't quite.

I filter my form using 2 unbound combo boxes which can be used in any order, both of which define text strings using case select. These strings are concatenated into a filter text and applied by a routine called in the after update event of either combo. All works well.

My question: How can I show all the filtered records in a third combo box so that users can easily select the record of interest from the filtered set?

I attempted to define SQL for each combination of the 2 comboboxes but it was getting ludicrously complicated so that got the heave ho.

After that, I attempted to use a recordset clone, but I couldn't get this to work:

'Clone record set for combo box rowsource
     Dim R As Recordset
     Set R = Me.RecordsetClone
     R.Bookmark = Me.Bookmark

'Populate combo box with recordset
     With Me![cboProjectList]
         .RowSource = R
         .Requery
     End With

Reading the Access help, it seems that a combo box can only be populted via query, table or SQL. Is there any way of using a cloned recordset or the me.filter to show the filtered form records in a combo or list box?

I will be very grateful for any pointers.

 

[Joebax]

If you are building filter text why cant you build a where clause instead?

 

[Joebax]

If you are building filter text why cant you build a where clause instead?

 

[Keith Nichols]

If you are building filter text why cant you build a where clause instead?

Hi Joebax,

Thanks for the response.

The 2 comboboxes that create the filter text for the form have something like 10 and 8 conditions respectively and the filter text is a combination of items in most cases. This results in about 80 possible, quite complicated filters.

I started down the path of trying to build the where clause for the first combination, the combination that form is coded to open with. Unfortunately, the result was very long and complex. I am not confident this could be split into the 2 parts of sqlText and get the concatenation right for all the different cases (by me at least ).

I figured that anything this complex looking was all but unmaintainable as well.

So, if it is possible somehow to use the recordset or the me.filter property of the form it will probably work well and be relatively simple.

If what I am asking is not possible, I will rework things to be Dependant on the results of my second combo box so I only have 8 cases to deal with, which is manageable.

Any suggestions gratefully received.

 

[Joebax]

I guess I would need to see a basic table description and the rowsource definitions of the two combo boxes to fully understand what you are trying to do.

As to your original questions. If you could somehow get the SQL text that is the source of your recordset then you could assign that as the rowsource. Then you would need to navigate to the record you wanted rather than using the bookmark.

Sorry I cant help more.

 

[Keith Nichols]

Thanks Joebax.

I am still messing about with this and wil post back with where I end up.

 

[gemma-the-husky]

you can fill an access combo box with something called a call-back function. Basically you define the columns and populate it with whatever data you want, programatically. The calling functions have to be set up in a certain particular way

Its complicated but look for an example of that.

 

[Keith Nichols]

Thanks for the feedback Gemma,

I have been working on this today and am surprised to find that it wasn't as 'impossible' as I had thought to create the SQL where clause (apolgies to Joebax - you were right .

I have managed to write sql 'where' text snippets for the case select conditions of both combo boxes that concatenate successfully into the rowsource of my target combobox.

What has surprised me is that the code I have used is far far simpler than the SQL generated by the Query design grid when trying to work out what I needed.

One small change was to substitute the PK of a related table with the relevant FK term from my main table, thus making both parts of the 'where' relate to that table without any of the fiendishly complex join lingo.

There are 3 compound conditions that I have yet to crack and get working but I think these are syntax issues that I can resolve by plugging away.

My big tip for any other brave soul trying something similar is to isolate sections of your problem and solve them individually. With 3 variables, I was getting nowhere. Once I knocked one of them out and added a text box to show the actual SQL text as concatenated I was able to get that cascade working, before doing the same for the other cascade and then concatenating the 2 together.

So, in conclusion nearly there. If I really can't get the last 3 terms sorted, I may fudge so that they aren't available or look into your suggestion of the Call-back function, although my head is spinning as it is and I don't relish the thought of a new 'complex' thing to take on board right now.

 

[Keith Nichols]

Problem solved.

I was really hoping that there was some way of using something akin to 'me.filter' but it seems not.

In the end, I have created 4 string variables that are variously defined in the case select of the 2 form filtering combo boxes which cascade into a routine to populate the target combobox. I had to use a couple of 'If then' statements in the concatenation to get the full functionality but I am very pleased to say that my 80 odd cases are all accurately reflected in the target combobox

This took me a number of hours (maybe 20) but a lot of learning was done on the way and I enjoyed solving the various issues that arose.

My tips for anyone trying the same thing are:
1) Use Fields from the same table in your SQL to avoid attempting various table joins in VBA

2) Use something like Excel to write out all the various parts of your concatenated SQL text and concatenate them there. Cut and paste various strings back into your database to refine your routines. The help here is mainly that you can see what is going on fairly clearly, one step at a time, rather than having to jump all over the VBA.

3) Every time you get one bit of code working, back the DB up The next change may stop it working altogether for no obvious reason!

4) Remark out items and use mesage boxes to give you a clue where the problem is when things screw up.

Regards,