Hi all,

I have made a query that is the source for a report.
On a form I have 2 combo's (Gender and distance) and an Option group (Selection of the years) that act as a filter for this report.

This setup works OK.

My question starts when I want to add an (extended) listbox.
I would like to be able to select a (couple of) countries before the report opens.

How can I incorporate this in the strLinkCriteria
The field I would like to use is CountryID

Hope somebody can help

Thanks
Hein

You'll have to iterate through the listbox selections and build a custom WHERE clause when opening the report. I just posted a sample of something similar for someone. I'll try to locate it. But, I'm sure thate are others here who will be able to point you to samples before I am able.

Check out this example from the code archive.

HTH.

Sounds good Bob
Thanks in advance
Hein

OK William
Thanks for your sample
I need some time to chew on this

So far my approach was completely different
My strLinkCriteria looks like:

strLinkCriteria = "Gender = " & me.cmbGender & "and " & Distance = ' " & me.cmbDistance & " ' and seasonID >= " & intStartYear & " and seasonID <= " & intLastYear

I thought I could add the listbox in the same way, with some alterations.

I will get back to you tomorrow (If I may)

BTW
How can I add the option <<All>> to the listbox and make it work in this setup?


Thanks
Hein

To add ALL as an option you would need to add it to the rowsource of the listbox as a Union Query. So take for example if you present rowsource looks like:

SELECT ID, fNAME, lNAME FROM tblPeople WHERE [RegionID]=6

You would add:

UNION SELECT "*" as ID, "ALL" as fNAME, "" as LNAME FROM tblPeople

Then when testing the listbox, you want to first test for the All selection before doing anything else.

HTH.

Hi William

I have studied the example you have posted and tried to change it to my situation

I now have code that works sometimes.
Sometimes Access doesn't change to the new selection and returns the previous one (I made sure that the "previous one" was closed.

And sometimes I get run time error 3075:
Syntax error (missing operator) in query expression '(([CountryID] = *))'.
Especially the first time I open the form and try to make a selection this error occurs

(As you can see I've managed to add the "All" to the listbox, thanks to your suggestions.)

This example is with "*", it also appears when I choose other countries.

The code I've made is as follows:

'General declaration

Option Compare Database
Dim vWhereCountry As Variant


Determining which countries should be included in the report



Can you tell from this if there is too much code, or if code is missing?

Hope to hear from yoy

Thanks in advance
Hein

Edited by: Hein on Tue Oct 9 15:07:15 EDT 2007.

First thing you need to do is add Option Explicit to just under the Option Compare Database Line.

For the first part of the code:

If "*" is selected, just exit the sub (i.e. don't set up criteria for the field in the query.

What I would do is this:



For opening the report, you don't need the vWhere part if you are only using the once list box. If you are using multiple list box, you would assemble the entire vWhere statement there.

HTH.

Hi William,
Thanks for your response

With your additions the "all" selections works.
However, it now stays "all", no matter what other countries I select.

And maybe I am permitted another question. One of the purposes for me to figure this out is a learning proces.
Most of the code I understand, except dor the line
"
strTempItem = "(" & Left(strTempItem, Len(strTempItem) - 4) & ")"
"

Especialy the -4 puzzles me
What is the reason for this?


Thanks in advance
Hein

To get rid of the all so you can select other countries, you need to clear the list box first. I have code somewhere to do this, but off hand I can't remember where it is. I'll look for it and get it too you as soon as possible (however it'll probably be tomorrow).

As far the the strTempItem, let me see if I can break this down:

The parenthesis are to enclose the OR Statement within the WHERE clause of the SQL string that is created. This is required when you will have more criteria already established when you want multiple criteria for each data point.

If you look at the code where the strTempItem is originally created, you will see that it is adding " OR " at the end after it takes the selected value from the listbox (which is four characters long). So, if you select three countries you will end up with:

[CountryID]=1 OR [CountryID]=2 OR [CountryID]=3 OR

The Left is telling Access to only take the number of characters/spaces specified starting from the left. I specify the length I want by telling Access to count the total number of characters/spaces (using the Len Function) and then subtract four. This chops off the extra OR.

HTH.

I get it, it is just to get rid of the " or "

Hope you can find the code you mentioned.
There's no rush. I live in The Netherlands, and it's getting kinda late, so I quit for the night.

See you tomorrow than.

Thanks and good night
Hein

Well, considering how late in the day it is you're probably off to bed, but here is the code to clear all selections from a list box.

Hi William,
No I'm not off to bed (yet)

Question: where should I put the code.
I have tried every place in the if - then - else section but apparently this is not the right place.

Any other place int the lstCountries_afterUpdate section doesn't seem right to me.
Should it be in an entirely new section??

Hein

What exactly is the issue,

is the user clicking all, then clicking another criteria and you want the all to go away? Or do you want to clear the criteria selection after the report is run. If the later, the code would go on the event that you open your report just after you open it. If the former, I don't think the code I provided will do that. I will play around with it today and see if I can figure a way to do that.

The problem now is that no matter what country I choose, or not even a country at all!, I still get the complete list.

In the first setup you gave me the outcome of the selections was most of the times OK. But sometimes I had to make the selction more than once because somehow, even if I changed the selected countries, it came up with the old selection.

BTW: I appreciate the trouble you're going through to teach me the "ropes"

Thanks
Hein