Where clause and event help

jhartford · October 1st, 2008, 06:40 AM

My listbox 1 with multi select set to extend. I Use the loop to extract multiple items selected in listbox 1 to a string which will form a WHERE clause to build a SQL string for the rowsource of listbox 2. I'm having trouble to figure out how to add the individual selected items into my SQL statement

The where clause should look like: where company = A or company = B or company = C ......, depending on how many item selected by user.

Code:

Dim strWhere as string
Dim i as integer
For i = 0 To ListTest.ListCount - 1 
    If ListTest.Selected(litem) = True Then 
        strWhere = strWhere & ListTest.ItemData(i) 'company name  
    End If 
Next

I tried whatever I can think but none of them works yet.

Another question is which event to use. Using an update event with a multi select listbox might cause problems- specifically the code firing before the user has made all selections. Click event on a command button should work but I really don't like to add another button to my form

Thanks in advance

Old Pedant · October 1st, 2008, 02:52 PM

The where clause should look like: where company = A or company = B or company = C ......, depending on how many item selected by user.

So then why didn't you do that in your code???

Code:

Dim strWhere as string
strWhere = " WHERE 0=1 " ' sneaky trick
Dim i as integer
For i = 0 To ListTest.ListCount - 1 
    If ListTest.Selected(litem) = True Then 
        strWhere = strWhere & " OR company = '" ListTest.ItemData(i) & "'"
    End If 
Next

But you could also do it using IN( ) which would be more efficient.

Code:

Dim strWhere as string
strWhere = " WHERE company IN ('*?*'"
Dim i as integer
For i = 0 To ListTest.ListCount - 1 
    If ListTest.Selected(litem) = True Then 
        strWhere = strWhere & ",'" ListTest.ItemData(i) & "'"
    End If 
Next 
strWhere = strWhere & ") "

You see it?

In the first case you end up with
WHERE 0=1 OR company='A' OR company='X'
in the second case you end up with
WHERE company IN ('*?*','A','X')

(I'm assuming the '*?*' is an impossible company, so it doesn't affect the results of the IN ( ), of course.)

The big reason to use IN( ) is so that you don't have to worry about complex parentheses if there will be other conditions in your WHERE.

jhartford · October 1st, 2008, 09:23 PM

Old Pedant,

This really helps. Thank you very much.

I have another question: Is there any way to set up a passwork to prevent user to view the code? As I know the answer is no. So I try to save my project.accdb to project.accde file, but received the following error msg:

This error is usually associated with compiling a large database into an MDE file. Because of the method used to compile the database, a considerable number of TableID references are created for each table. The Access database engine can only create a maximum of 2048 open TableIDs at one time. Exporting a database as an MDE potentially can exceed this limit if the database has a large number of objects (table, macro, form, report, etc).

I don't really understand.

Appreciate your help.

Old Pedant · October 2nd, 2008, 12:57 AM

Yes, you can lock an Access "project" so that others can't change it. This will also prevent novice users from seeing the code, but hackers can always get to the code if they really want to.

I have no idea what the message you are quoting is all about. It sounds like you tried to *export* the project to an MDE (Microsoft Desktop Engine--a miniature version of SQL Server that is now very obsolete, supplanted by SQL Server Express) and that is what failed. Don't know why that would be associated with "protection".