Selecting mutiple choices from either a list box or combo box? 11

[Nate1749]

I have a non-normalized .csv file that we get from an external source. It's then linked to an access table. I think have queries that do a Group By to pull out unique instances for certain columns and then that populates the combo/list box's on a form. The selections made on the form is the criteria for a report that is ran. Currently everything works fine, except now I'm trying to figure out how the user can select multiple criteria.

For instance, in cboLocation there is Online, DuPage, Kane.
Currently I can only select one choise and run the report; I'm trying to figure out how I could possibly select two or all three. I changed the combo box to a list box and tried holding ctrl, but no luck.

-Nate

 

[Nate1749]

I'm trying to get the new code posted by FancyPrairie working and I'm having some troubles getting it started.

Since I'm having this activated by a button click, I can't figure out where these two lines should go.

Private Sub btnPrvRpt72_Click()
End Sub


I've tried putting them before the function starts, after it starts, ending it in the function, having it be the last thing.... a number of combinations and I just can't seem to get it started... The error message I receive is "Compile error: Expected End Sub" and goes to the line that starts Private....

-Nate

 

[FancyPrairie]

Not sure what you're trying to do. Let's back up. Create a new module and copy and paste the code for the function BuildWhereClause (shown in one of the previous posts). Then in the onclick event of your button, either open the report (as shown in that same post) or do this:

msgbox BuildWhereClause("NameOfFormThatContainsTheListBoxes"

This will display the Where Clause that the function BuildWhereClause will produce.

 

[Nate1749]

ohhhhhh, ok; I wasn't understanding that code is called upon just by putting that BuildWhereClause in there... Ok, now that I've got that I've ran into some new problems..

BTW I'm only doing one list box with the tag property for now. Also, in my tag property, I am not referncing the table, but rather the query... Is that allright? I need to reference the query because the table is linked to a non-normalised csv file (query groups unique instances).

If I don't select anything and I click "preview report" it prints the report... this is the code for the preview button

Private Sub btnPrvRpt72_Click()
DoCmd.OpenReport "rpt72", intView, , BuildWhereClause(Me)
End Sub


If I do select anything from that list box, then I receive the error
5
Invalid procedure call or argument

I click ok to that error message and then I receive another one stating the following

Run-time error '3075':
Invalid use of '.', '!', or '()'. in query expression '(!!!ERROR!!!)'.

Additionally, as I was looking at the code I noticed it uses single quotes when seperating the data for the criteria... Will it cause a problem if one of the data fields actually contains an apsotrphe? Currently they don't, but in the future in other fields they might.

I'll be playing with this for awhile, so any points in the right direction are appreciated.

-Nate

 

[Nate1749]

hmmm interesting turn of events, I now get the report to preview by changing the onclick code to this

DoCmd.OpenReport "rpt72", acPreview, BuildWhereClause(Me)

so that previews everything just fine, however, if I select something it gives me the "Invalid Procedure Call" error message I talked about in the previous post, but it doesn't give me the 2nd error (the run-time one), and then it goes to preview the report after I click ok and just shows everything....

Not sure why it solved the mystrey of the second error message, but I'll take it.... I've just got the one error message now.... by the way my tag on the box is...

Where=[qryCampus].[Student Campus],String

-Nate

 

[FancyPrairie]

Don't know. Have to look at it later this evening. But you might want to step thru the function using debug to see what it's doing.

To use debug, either put the word Stop above the line of code you want to examine. Or place the cursor on the line and press F9 (toggles break point on/off). Then execute your code. The program will pause execution when it encounters the Stop or the break point. Press Ctrl-G to display debug's immediate window. To examine a variable, either place the cursor over the variable (a tooltip will display its value) or type ?thevariableName in debug's immediate window. Press F8 to step thru the code 1 line at a time. Press F5 to continue execution until the program exits or it encounters another stop statement or break point. Good luck.

 

[Nate1749]

Wow, I never knew how to debug code, thanks!

Ok... so this has definitely helped me centralize the problem.
this is the last line of code it runs
strType = Mid(ctl.Tag, k + 1, j - k - 1)

After that line it goes straight to the error handling section.
FYI - The next line that never runs is this...
strFilter = strFilter & strAnd & strField & " In ("


Since it's the line where it is strType, I'm wondering if it's really considered a string. Like I said earlier that list box being populated by a query that groups field names. Am I declaring the tag correctly?
Where=[qryCampus].[Student Campus],String

-Nate

 

[FancyPrairie]

Debug's great isn't it?

You discovered your problem. If you put a breakpoint on the last line that runs and examine "j" it will probably equal 0. So 0-k-1 equals a negative number. Not good. In fact, if you examine ?Mid(ctl.Tag, k + 1, j - k - 1) you should receive an error. However, if you examine ?Mid(ctl.Tag, k + 1) you should get a valid result (but this assumes no comma at the end).

The problem is that the code is trying to determine where the end of the string is within the Tag property. It determines this by looking for the last comma. Therefore, your Tag property should look like this:

Where=[qryCampus].[Student Campus],String, (note the comma after string)

Now, we could fix the code to either look for the comma after String or take note that we're at the end of the line. But, I don't assume that the Tag property only contains Where=... Programmers may want to include additional info in the Tag Property. That's why I stuck with strict syntax checking.

 

[Nate1749]

Ok, the comma now makes it so I don't receive any errors, however, there is one more problem.....

Whatever I select isn't being queried out. So,
If I select Chicago & run it, I receive everything.
If I don't select anything I receive everything.
If I select Chicago & Dupage, I receive everything....

hmmmmm,,,, any clues?

 

[FancyPrairie]

Not sure, but try this. Instead of issuing the Docmd.OpenReport... command, type this instead:

Msgbox BuildWhereClause(Me)
debug.print BuildWhereClause(Me)

Now run you app like you normally would (i.e. select 1 or more items via your list box). The msgbox should display the Where clause without the word Where. Likewise, the debug.print command should display the Where clause in debugs Immediate Window.

Since your report is based on a query (I assume), open the query in design view (via the query analyzer), select SQL view, copy the where clause shown in debugs Immediate Window and place it in the SQL select statement. Then run it. If it doesn't run there, then there's your problem.

For example, suppose your query is "SELECT * FROM YourTable;" After copy the Where clause from debugs Immediate window, the query should look like this:
SELECT * FROM YourTable Where (contents of debugs Immediate window);

 

[grobermatic]

Have some posts gone missing from this thread???

I'm SURE I posted something last week about multiselect listboxes, I even read the reply... Now I can't find it anywhere.

I'm sure it was this thread ?!?!?!

Craig

 

[idd]

I too believe that some posts have gone missing.

I read a problem on this thread and responded to it it probably was grobermatics problem.

Hmmm, quite strange.

I remember the problem was about wanting to select all records and not filter when nothing is selected from the listbox and if something is selected then filter the selected.

maybe this thread is getting a bit long and a new thread needs to be started for newer questions.

Idd

 

[grobermatic]

Idd,

I started a new thread : thread702-683975


Would you mind having a look at this one to see if you could answer the problem again?

Thanks


Craig