ListBox Problem

I have a similar problem and I have been following the advice but to no avail. I am a novice with VBA so help would be greatly appreciated. This is my first post so I apologise if it is in the wrong place or format.

In my query I have:
and in the form I have:

Case 1 is fine. The problem is with Case 2. I am trying to select multiple values from a list box to use for the parameter mentioned above in the query to produce a report. However I keep getting the report with errors (meaning that the invigilator has not been found). (The cases are there because radio buttons are used to produce either one report or the other - I have had it working before with just single values).

re: ListBox Problem

My first step would be to stop the code before line 17 (debug or MsgBox) and take a look at what is in strWhere just before you try to use it.

re: ListBox Problem

sorry to sound like a noob but how do you do that? (I'm using Microsoft Visual Basic.)

re: ListBox Problem

You can just MsgBox the contents of the variable and see what part is malformed.
There is also an article on debugging you may want to look at.

re: ListBox Problem

Thank you for the MsgBox function that helped me see what the code is actually doing!

When i selected 2 from the list it came up with:

which means that it is not enclosing the values in ""
I think!!

Can you see, from this, what is wrong with my code?

Eternally grateful!

re: ListBox Problem

I'm not sure if that's the proper syntax for IN, as I've only seen it used with a subquery SELECT. I'll try to check it out sometime today, but maybe do some experimenting and see if you can get anything to work.

re: ListBox Problem

You're excused G_Diddy, but for reference please avoid posting your questions in existing threads in future. This (thread hijacking) is covered in our Help section - accessible from all pages.

For reference, this thread was split from (and may refer to) List Box and Parameters.

Welcome to Bytes!

re: ListBox Problem

I couldn't get IN to work that way. Here's what you can do instead:
It would be more efficient to paste the OR on the end every time through the loop, then remove it at the end using Left() or something, but this will do as an example.

re: ListBox Problem

It is indeed. I don't believe the automatic adding of quotes works within such a function though.

You (The OP) need to determine the type of the field you are comparing and ensure string or date literals are enclosed by the requisite characters (Quotes (') and Double-Quotes (") - Where and When to use them).

re: ListBox Problem

I tried your way ChipR and received the following when I used the MSGBOX function:

I modified the code to:

and this gave me:

However I now get an error saying the expression is typed incorrectly or is too complex to be evaluated.

I have also been playing round with my original code and managed to get strWhere to contain:

However the last one still has ,' after it which isn't right. I have tried so many combinations but cannot get it to work. I know that the ,' is appearing because of the loop. However without adding it in the loop it isn't added at all.

The code so far is:

Can you see where i'm going wrong at all? I am such a novice with VBA so I apologise for being a pain!!

re: ListBox Problem

If my code didn't work, then you've got some other issue.
If you still want to do it that way though, use Mid() to remove the last ,'

re: ListBox Problem

Where abouts in the code would that go mate?

re: ListBox Problem

On line 17, just replace your Mid() with the one I wrote. But looking more closely, I think change it to
Mid(strWhere, 1, Len(strWhere)-1)

So is the [Invigilator no] a number field or a text field?

re: ListBox Problem

its a number field mate with the criteria:

(n.b. combo46 is just the name, the multi select box is actually a list box i just didn't get round to renaming it in the form)

Just added your code in and now get the message box saying strWhere contains:
but then an error message saying that the expression is too complex etc as before. Without the ' ' around the numbers I get no error message but my report shows up with errors (i.e. invigilator not found). For a number field that is being passed the parameters like 1458, 1492 etc do they have to be enclosed in ' ' or not?

re: ListBox Problem

Number fields do not need to be enclosed in quotes, so I would expect the first MsgBox output you listed in post #10 to work. We'll have to look into this error about the expression being too complex. What is the exact text of the error message, and does it have an error number?
I still can't get IN to work that way, but since it's the same error, I would guess that it's the same solution.

I just noticed you have an extra quote in your string.
Since you are already putting quotes around each number, change this to:

re: ListBox Problem

The error says:

This error doesn't show when the quotes are not around the numbers though. I'm going to leave them off from now on (thanks for letting me know that by the way...i would have been forever trying to correctly place these quotes when they are not needed!!!). The only problem now is that the report still shows errors so the multiselect box must not be passing the values to the parameter correctly. I'm really not sure why. It used to work with single values, I would select the invigilators name (e.g. Anderson, David) and it would pass their id (1458) to the query which then created the report with the results on. The only thing I have done differently is change it from a combo box to a multi select (simple) list box. Would that have changed anything at all? The row source is still the same too

(n.b. when 4 were selected strWhere contained:

which looks like it is in the correct format...well to me anyway but i'm a novice!!)

re: ListBox Problem

I've found this webpage which seems really relevant but I can't make head nor tail of it. You might find it useful though

http://www.fontstuff.com/access/acctut18.htm

re: ListBox Problem

Thanks for the link. According to that, the strWhere you have looks fine. What errors do you get on the report? What do you mean by "pass their id to the query"?

re: ListBox Problem

it just says #error which is because the invigilator no has not been recognised.
in the query it has for the field Invigilator no the criteria:

[Forms]![SelectWeek]![Combo46]

which takes the value from the multi select list box for use as a parameter

so when the user selects an invigilator from the multi select list box (named Combo46) it should pass their ID to the query which is then used to produce the report. When the list box only allowed the user to select single values it worked correctly it is only since trying to change it to multiple select that it failed. I hope that helps.

re: ListBox Problem

You may not be able to reference the actual value selected in a multi-select list box that way. Notice in the code you have to reference the Combo46.ItemsSelected collection. What you could do is whenever an item is selected in the list box, use code to generate the string in another (hidden) text box, and then reference that text box in your query.

re: ListBox Problem

that sounds like a plan! how would I go about doing this though? I know how to create text boxes but I'm not entirely sure on how to generate the string for use as a reference
(eternally grateful!!)

re: ListBox Problem

Are you using that reference in the query to filter the query results, then using the strWhere to do the exact same thing? If that is the case, you just remove that condition in the query, let it return all the records, and use the where condition in the DoCmd.OpenReport to see only the ones you want.

re: ListBox Problem

YES!! that was the problem all along!! Thank you so much for all your help mate. I really appreciate it thank you!!

re: ListBox Problem

Glad to hear you got it working. I learned a little something too.

re: ListBox Problem

Just to clarify for anyone reading who gets confused by all the to-ing & fro-ing :

The best way to apply filtering to a report is to design a query (or Record Source) which returns all records, then apply the filter to the report itself. This is most easily done in the call that opens the report itself using the WhereCondition parameter.

It's possible to apply criteria to the underlying query (as the OP did in this case) only to find that flexibility later is severely reduced.

BTW Sorry that I didn't help in time guys. By the time I had caught up with the problem (Post #14 gave the clue - but wasn't specific about what type of Criteria it was working with so I missed it. It was only at post #19 I realised the OP was trying to applying the criteria within the query) Chip had already supplied the solution.

As the query was already saying "WHERE [Invigilator] = [Value from form]" the use of IN() could never work (Equivalent to [Invigilator = IN(...)). Only one equality operator can be used at a time. Either "=" OR "IN()" but not both.