Multiple List box to a query 8

[Crystalguru]

I know this topic is out here a hundred times and trust me I have printed them ALL. But I just can't get it to work. If someone could please help!

This is what I have:
Names of objects are in ()

A form(FindPerson) with a list box(LST) of Software Skills(sft_software). The properties on the list box are set to Multi Select = Simple. This works fine. I can choose multiple software skills.

I would like to choose multiple software skills, click a button, run a query to find Candidates(Query-FindCandidates) that have those skills. I know I need code for this.

Since I am new to VB. Step by step would be great!

Any help is appreciated.

 

[FancyPrairie]

The following code builds the "Where" clause for your SQL statement.

    Dim strSQL as string
    Dim strYourSQLSelectFromStatement as string
    Dim strSQLWhere as string
    dim varItem as variant
   
    if (YourListBox.ItemsSelected.Count = 0) then Exit Function

   strYourSQLSelectFromStatement = "Select * From YourTable"
   strSQLWhere = vbNullString

   For Each varItem In YourListBox.ItemsSelected
       strSQLWhere = strSQLWhere & "sft_software='" & YourListBox.column(0,varItem) & "' OR "
   Next varItem

   strSQLWhere = len(strSQLWhere)-4  'Get rid of last OR

   strSQL = strYourSQLSelectFromStatement & strSQLWhere

This should give you some ideas of how to do it

 

[Crystalguru]

Ah FancyPrairie, just the person I was looking for! You helped me last time and I didn't continue the thread...

Do I put this code behind the OnClick of the button or somewhere on the form? I am stuck here. I cannot figure out where things go...

1.) Create Form
2.) List box that searches Software Skills, with multiple select set to Simple
3.) Button that runs Query-FindCandidates (which generates all sorts of new questions, but later for that.)

Like I've said before, I am a Crystal Report writer, so be kind....

 

[FancyPrairie]

Sorry, I haven't got back to you sooner, I didn't notice this post til this morning. After the user has selected the criteria (i.e. items in list box, etc.), then have them select a command button. On the OnClick event of the button is where you put the code to build your query statement. The code in my previous post will build the SQL statement for you. Now you can create a query based on the SQL string you created. This is how you do it (This code was created for my Access 95 databases, but still works in Access 2000. However, may be a better way of doing it in Access 2000)

Dim dbs As DAO.Database 'CurrentDb
Dim qdf As DAO.QueryDef 'Used to save the query that's created for the report


On Error Goto ErrHandler

INSERT CODE FROM MY PREVIOUS POST HERE

Set dbs = CurrentDb
dbs.QueryDefs.Delete "YourQueryName"
Set qdf = dbs.CreateQueryDef("YourQueryName&quot
strSQL = vbNullString
DoEvents

ExitProcedure:
Exit Sub

ErrHandler:

If (Err.Number = 3265) Then 'IFT, tryed to delete a query that did not exist.

Resume Next

Else

Msgbox err.number & err.description
Resume ExitProcedure
End if

End Sub

 

[FancyPrairie]

OOPS! Just as I hit the submit button, realized my code was wrong. The CreateQueryDef line should look like this:

Set qdf = dbs.CreateQueryDef("YourQueryName", strSQL)

 

[Crystalguru]

Ok. I am sorta getting it. You are being very kind!

Here's my code:
Private Sub Command5_Click()
'Dim dbs As DAO.Database
'Dim qdf As DAO.QueryDef

On Error GoTo ErrHandler

Dim strSQL As String
Dim strYourSQLSLEectFromStatement As String
Dim strSLQWhere As String
Dim varItem As Variant
If (Lst.ItemsSelected.Count = 0) Then Exit Sub

strYourSQLSelectFromStatement = "select asc_uid from asc_profile, sft_software,j_sft_asc table where asc_uid=j_asc_uid and j_sft_uid=sft_uid"
strSQLWhere = vbNullString

For Each varItem In Lst.ItemsSelected
strSLQWhere = strSQLWhere & "sft_software='" & Lst.Column(0, varItem) & "'OR"
Next varItem
strSQLWhere = Len(strSQLWhere) - 4 'get rid of last or
strSQL = strYourSQLSelectStatement & strSQLWhere

Set dbs = CurrentDb
dbs.QueryDefs.Delete "Find Candidate"
Set qdf = dbs.CreateQueryDef("Find Candidate", strSQL)
strSQL = vbNullString

DoEvents

ExitProcedure:
Exit Sub

ErrHandler:
If (Err.Number = 3265) Then 'IFT, tryed to delete a query that did not exist
Resume Next
Else
MsgBox Err.Number & Err.Description
Resume ExitProcedure

End If

End Sub

******************************************************
Had to ' out the two first Dim statements, it kept erroring on them. When I ' them out, it works but I get another error. "#3129 Invalid SQL Statement expected delete, insert, procedure, select, update". What have I done wrong?

The line:
strSQLWhere =strSQLWhere & "sft_software='"
SFT_SOFTWARE is the multiple list box table. Is this correct?

Is my query correct? Am I supposed to create a query called Find Candidates in the query design? How do the values from the list box get to the query? In order to find my candidates I have to link the Software table to the Candidate table by a join table (J_SFT_ASC Table). Can I still do this?

Thanks again

 

[FancyPrairie]

Hey, good to hear from you. Again, sorry I didn't get back to you sooner.

1. The best way to do what you want is to create a query via the query builder, and select some fields for your WHERE clause. Once you've got it working like you want it via the query builder, change the query builder to SQL view (rather than Design view) and highlight and copy the string and then paste the string in your module. Now strip out the Where clause, because the program (created in prev post) will build the Where clause. Doing this you can see how the SQL statement is suppose to look (via the Query Builder) and how it looks after you've created it (via your code). This way you don't have to worry about how your joins are supposed to look etc. because the query builder did it all for you.

For example: replace what's in red with the SQL string you copied from the query builder (without the Where clause)
strYourSQLSelectFromStatement = "select asc_uid from asc_profile, sft_software,j_sft_asc table where asc_uid=j_asc_uid and j_sft_uid=sft_uid"


2. Since we're using an Access 95 technique, we have to specify it in the Dim statement via DAO. However, in order for Access 2000 to recognize DAO you have to reference it. So, when you're in your code, select TOOLS|REFERENCES and make sure that "Microsoft DAO 3.6 Object Library" is checked. Once done, uncomment your Dim statements.

3. Well, in strSQLWhere =strSQLWhere & "sft_software='" I assume that "sft_software" is the field that contains the items you are looking for, not the table. You need to reference the field. For example, suppose I had a personnel table (tblPersonnel) and each person is assigned an ID (lngID), my list box would contain 2 columns (column(0) = lngID and column(1) = strFullName). Column(0) is hidden and column(1) is shown to the user. Now, suppose I select 2 people from the list box and their IDs are 23 and 27, my SQL statement would end up looking like this: Select * from tblPersonnel Where tblPersonnel.lngID=23 or tblPersonnel.lngID=27.

But, again, if you 1st build a sample query via the query builder, you will see how the final SQL statement is suppose to work.

4. The values from you listbox get to the query via the following statement: Set qdf = dbs.CreateQueryDef("Find Candidate", strSQL) Notice that strSQL contains your SQL statement and, so, it is written to the query, assuming the SQL statement is correct.

5. To determine what's wrong with your SQL statement, type STOP right after this line: strSQL = strYourSQLSelectStatement & strSQLWhere. The program will then go into Debug Mode. From the menubar, select VIEW|IMMEDIATE WINDOW. This will display the debug window. In the debug window type ?strSQL and hit return. Highlight the string it displays, press control-C (copy). Then goto the Query Builder and select SQL and paste the string. Now try to run the query you just created. This might help you figure out what's wrong.

 

[AndyCole]

Dear Crystalguru

Just reading this thread and noticed the following in your code;

For Each varItem In Lst.ItemsSelected
strSLQWhere = strSQLWhere & "sft_software='" & Lst.Column(0, varItem) & "'OR"
Next varItem

You need a space after the single quote (before OR) and another after OR (before the doublequote) as follows;

For Each varItem In Lst.ItemsSelected
strSLQWhere = strSQLWhere & "sft_software='" & Lst.Column(0, varItem) & "' OR "
Next varItem

Then it should work

Cheers

Andy

 

[Crystalguru]

FancyPrairie, thank you so much! Your response time and descriptive help are awesome!

I did create the query in the query designer, that was much easier.

My Microsoft DAO 3.6 Object Library wasn't checked, it is now and the DAO statements work fine.

I needed to change sft_software to the sft_uid (the actually ID field)

Two problems remain:
1. When I put the stop statement in it gives me -4. I see I have -4 in the code, if I change the 4 to a 1 and I get a 1 returned...???

2.The statement If(lst.itemsSelected.count=0)then exit sub
I see in you initialpost it was Exit Function, but I recieve an error when it has Function there. Any ideas?

Seeing the light!
Thanks AndyCole, I made spaces and that is not erroring now!

 

[Crystalguru]

FancyPrairie, thank you so much! Your response time and descriptive help are awesome!

I did create the query in the query designer, that was much easier.

My Microsoft DAO 3.6 Object Library wasn't checked, it is now and the DAO statements work fine.

I needed to change sft_software to the sft_uid (the actually ID field)

Two problems remain:
1. When I put the stop statement in it gives me -4. I see I have -4 in the code, if I change the 4 to a 1 and I get a 1 returned...???

2.The statement If(lst.itemsSelected.count=0)then exit sub
I see in your initial post it was Exit Function, but I recieve an error when it has Function there. Any ideas?

Seeing the light at the end of the tunnel!
Thanks AndyCole, I made spaces and that is not erroring now!

 

[FancyPrairie]

1. I don't understand what you mean by receiving a -4 when displaying strSQL in the debug mode. You should see the SQL statement. Try this instead of "stop"; Msgbox strSQL or with the debug window open, instead of "stop" type: Debug.Print strSQL. The reason I'm having you do this is so that you can see if the SQL statement you create via code looks like the one you build in the query builder. When you're testing your code, select the same items from your list box that you used in the query builder. That way you can compare apples with apples.

2. This statement If(lst.itemsSelected.count=0)then exit sub is in the wrong place for what you want to do. Your code should look something like this:

'***********************************************************
'*  Build Select...From Clause and Initialize Where clause *
'***********************************************************

    strYourSQLSelectFromStatement = "Select * From YourTable"

'Building Select Statement

   strSQLWhere = vbNullString

'***********************************************************
'*  Build Where Clause If user selected at least one item  *
'***********************************************************

    If (lst.ItemsSelected.Count > 0) then

'If True, at least 1 item selected

       For Each varItem In YourListBox.ItemsSelected
           strSQLWhere = strSQLWhere & "sft_uid ='" & YourListBox.column(0,varItem) & "' OR "
       Next varItem

       strSQLWhere = len(strSQLWhere)-4  'Get rid of last OR
    End If

'********************************************************
'*  Build Entire SQL string (Select...From...Where...)  *
'********************************************************

    strSQL = strYourSQLSelectFromStatement & strSQLWhere

'******************************
'*  Save SQL string to query  *
'******************************

    Set dbs = CurrentDb
    dbs.QueryDefs.Delete "YourQueryName"
    Set qdf = dbs.CreateQueryDef("YourQueryName",strSQL)
    strSQL  = vbNullString
    DoEvents

'********************
'*  Exit Procedure  *
'********************

ExitProcedure:
    ...

'*******************
'*  Error Handler  *
'*******************

ErrHandler:
    ...

NOTE: If you're procedure is a sub procedure, then you need Exit Sub (which is your case). If it's a function, then you need Exit Function. The only difference, basically, between a sub procedure and a function is that a function returns a value to you. Don't worry about it at this point. What ever works. Since I changed it to an IF...Then you don't have to worry about it any way.

 

[Crystalguru]

I hope I am not becoming a Pain in the ***...

Here's what I have so farI've squished it together for less space.)
Private Sub Command5_Click()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
On Error GoTo ErrHandler
Dim strSQL As String
Dim strYourSQLSelectFromStatement As String
Dim strSLQWhere As String
Dim varItem As Variant
strYourSQLSelectFromStatement = "SELECT Asc_Profile.Asc_FirstName, Asc_Profile.Asc_LastName, Sft_Software.Sft_descr, Sft_Software.Sft_uid FROM Asc_Profile INNER JOIN (Sft_Software INNER JOIN [J_Sft_Asc Table] ON Sft_Software.Sft_uid = [J_Sft_Asc Table].J_Sft_uid) ON Asc_Profile.Asc_UID = [J_Sft_Asc Table].J_asc_uid"
strSQLWhere = vbNullString
If (Lst.ItemsSelected.Count = 0) Then Exit Sub
For Each varItem In Lst.ItemsSelected
strSLQWhere = strSQLWhere & "sft_uid='" & Lst.Column(0, varItem) & "' OR "
Next varItem
strSQLWhere = Len(strSQLWhere) 'get rid of last or
strSQL = strYourSQLSelectStatement & strSQLWhere
***this is where I have been trying to put STOP or MsgBox strSQL or Debug.Print strSQL******************
Set dbs = CurrentDb
dbs.QueryDefs.Delete "Find Candidate"
Set qdf = dbs.CreateQueryDef("Find Candidate", strSQL)
strSQL = vbNullString
DoEvents
ExitProcedure:
Exit Sub
ErrHandler:
If (Err.Number = 3265) Then 'IFT, tryed to delete a query that did not exist
Resume Next
Else
MsgBox Err.Number & Err.Description
Resume ExitProcedure
End If
End Sub

ARGH! I am so ready to pull my hair out. Is it always this hard??? I cannot get my SQL statement to appear in that window at all. I keep getting numbers there, like a 0 or I get in a vicious error cycle and I have to kill access to get out.

What have I done wrong now?

Thanks again!

 

[jebry]

Hi Crystalguru!

I noticed a couple of errors:

strSQLWhere = vbNullString should be

strSQLWhere = ' Where '

and

strSQLWhere = Len(strSQLWhere) should be

strSQLWhere = Left(strSQLWhere, Len(strSQLWhere) - 4)

The complete code will look like this:

Private Sub Command5_Click()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
On Error GoTo ErrHandler
Dim strSQL As String
Dim strYourSQLSelectFromStatement As String
Dim strSLQWhere As String
Dim varItem As Variant
strYourSQLSelectFromStatement = "SELECT Asc_Profile.Asc_FirstName, Asc_Profile.Asc_LastName, Sft_Software.Sft_descr, Sft_Software.Sft_uid FROM Asc_Profile INNER JOIN (Sft_Software INNER JOIN [J_Sft_Asc Table] ON Sft_Software.Sft_uid = [J_Sft_Asc Table].J_Sft_uid) ON Asc_Profile.Asc_UID = [J_Sft_Asc Table].J_asc_uid"
strSQLWhere = " Where "
If (Lst.ItemsSelected.Count = 0) Then Exit Sub
For Each varItem In Lst.ItemsSelected
strSLQWhere = strSQLWhere & "sft_uid='" & Lst.Column(0, varItem) & "' OR "
Next varItem
strSQLWhere = Left(strSQLWhere, Len(strSQLWhere) - 4)
strSQL = strYourSQLSelectStatement & strSQLWhere

Set dbs = CurrentDb
dbs.QueryDefs.Delete "Find Candidate"
Set qdf = dbs.CreateQueryDef("Find Candidate", strSQL)
strSQL = vbNullString
DoEvents
ExitProcedure:
Exit Sub
ErrHandler:
If (Err.Number = 3265) Then 'IFT, tryed to delete a query that did not exist
Resume Next
Else
MsgBox Err.Number & Err.Description
Resume ExitProcedure
End If
End Sub

hth
Jeff Bridgham
bridgham@purdue.edu

 

[FancyPrairie]

Jebry is exactly right. I have put this code together a bunch of times for different posts (each person has different requirement but same concept) and between cutting and pasting left out a few things.

1. This statement: strSQLWhere = Len(strSQLWhere) just returns the length of the string
That's why you're getting a number rather than your Where Clause.

2. I also missed putting the Where statement in

Sorry about my mistakes.

3. One other thing you should consider. If the user does not select anything from the list box, do you still want to create the query? If you do, then the query will display all of your records (since no Where clause exists). Therefore, if you do want to create the query, regardless of whether or not the user selects items from the list box, then you should use the If statement If (lst.ItemsSelected.Count > 0) then via my post of Aug 4 rather than If (Lst.ItemsSelected.Count = 0) Then Exit Sub. Here's how it should look (note I put strSQLWhere = vbNullString back in (Jebry took it out (which was right for the original IF statement), but in this case we need it):

strSQLWhere = vbNullString
If (lst.ItemsSelected.Count > 0) then
strSQLWhere = " WHERE "
For Each varItem In lst.ItemsSelected
strSQLWhere = strSQLWhere & "sft_uid ='" & lst.column(0,varItem) & "' OR "
Next varItem

strSQLWhere = Left(strSQLWhere, Len(strSQLWhere) - 4) 'Get rid of last OR
End If


strSQL = strYourSQLSelectStatement & strSQLWhere

4. Finally, if sft_uid represents a number (i.e. integer, long, autonumber (which is long), then you need to get rid of the single quotes in "sft_uid'" & lst.column(0,varItem) & "' OR "

 

[Crystalguru]

I think I killed a tree printing this post out...uhoh.

Ok. Here's what I've got:
strYourSQLSelectFromStatement = "SELECT Asc_Profile.Asc_FirstName, Asc_Profile.Asc_LastName, Sft_Software.Sft_descr, Sft_Software.Sft_uid, [J_Sft_Asc Table].J_Sft_uid FROM Asc_Profile INNER JOIN (Sft_Software INNER JOIN [J_Sft_Asc Table] ON Sft_Software.Sft_uid = [J_Sft_Asc Table].J_Sft_uid) ON Asc_Profile.Asc_UID = [J_Sft_Asc Table].J_asc_uid"

strSQLWhere = vbNullString
If (Lst.ItemsSelected.Count > 0) Then
strSQLWhere = " Where "
For Each varItem In Lst.ItemsSelected
strSLQWhere = strSQLWhere & "j_sft_uid=" & Lst.Column(0, varItem) & " OR "
Next varItem

strSQLWhere = Left(strSQLWhere, Len(strSQLWhere) - 4)
End If
strSQL = strYourSQLSelectStatement & strSQLWhere

I am getting a Error #3129 Invalid Sql statement... I did get it to run (I forgot what code I had) but it did nothing. This will produce a query window, right?

Yes, sft_uid is number, so I changed that part of the code.
Thanks again to all those helping me.
FancyPrairie-no need for apologies!

 

[FancyPrairie]

Glad you're not upset for my obvious coding errors (thought faster than I typed).

1. Hope you left the following code in your procedure. (This is the code that creates the query. You will have to run the newly created query via the Query Builder)

Set dbs = CurrentDb
dbs.QueryDefs.Delete "Find Candidate"
Set qdf = dbs.CreateQueryDef("Find Candidate", strSQL)
strSQL = vbNullString
DoEvents
ExitProcedure:
    Exit Sub
ErrHandler:
If (Err.Number = 3265) Then 'IFT, tryed to delete a query that did not exist
Resume Next
Else
MsgBox Err.Number & Err.Description
Resume ExitProcedure
End If

2. Where did this variable j_sft_uid come from? It thought it was sft_uid

3. Otherwise it looks ok. Did you get the Msgbox or Debug.print working? If not, insert msgbox or debug.print after this line: strSQL = strYourSQLSelectStatement & strSQLWhere

Example:

strSQL = strYourSQLSelectStatement & strSQLWhere
msgbox strSQL
debug.print strSQL

This will show you what the SQL statement looks like. From there you can figure out what the code is doing or not doing. You should receive no errors up to this point because you haven't actually attempted to create a query (you've just created a string at this point).

Remember, when testing your code, select items that worked when you built the SQL statement via the Query Builder. Try running your code without selecting anything from the listbox. The query created should display all of the records. If not, your Select Clause is wrong. If, by not selecting anything, your query works, then verify that the Where clause is correct.

 

[FancyPrairie]

I copied the code you put in your previous post and ran it. Discovered that you misspelled a variable (looks like it was misspelled starting with your post of Aug 2). You should have received a compile error (unless your code is correct, and you just typed it wrong when you posted it).
In the following line, you typed strSLQWhere rather than strSQLWhere:
strSLQWhere = strSQLWhere & "j_sft_uid=" & Lst.Column(0, varItem) & " OR "

Once I corrected the spelling error, I ran the code shown in your post (of course I changed the SELECT statement in my test). The Where clause performed as should and so did the code that created the query. I opened the query in the Query Builder and ran it and it did what is was supposed to. So yours should work.

 

[Crystalguru]

1.) I did leave the Creat Query code in, just left it out of the post due to space.... When you say "I will have to run the query via the query builder" where will the query appear?

2.)j_sft_uid is the join table between Candidates and Software skills, if I used sf_uid, it only brought back one candidate, the first one...my mistake.

3.)Still cannot get msgbox strSQL debug.print strSQL to work. I inserted it underneath the code line - strSQL = strYourSQLSelectStatement & strSQLWhere. When I click on the command button I still get the error # 3129 invalid SQL statement. I copy the sql statement and run that in the query builder and it runs fine. I choose a software skill and click on the button and it gives me a blank error message then the #3129.

4.) I corrected my misspellings, I had a few! oops.

Is there something wrong with my Access? I am getting this feeling....

 

[Crystalguru]

1.) I did leave the Creat Query code in, just left it out of the post due to space.... When you say "I will have to run the query via the query builder" where will the query appear?

2.)j_sft_uid is the join table between Candidates and Software skills, if I used sf_uid, it only brought back one candidate, the first one...my mistake.

3.)Still cannot get msgbox strSQL debug.print strSQL to work. I inserted it underneath the code line - strSQL = strYourSQLSelectStatement & strSQLWhere. When I click on the command button I still get the error # 3129 invalid SQL statement. I copy the sql statement and run that in the query builder and it runs fine. I choose a software skill and click on the button and it gives me a blank error message then the #3129.

4.) I corrected my misspellings, I had a few! oops.

Is there something wrong with my Access? I am getting this feeling....

 

[FancyPrairie]

OK, I think we're getting someplace. You said when you click the command button you're getting a blank message and then error 3129. If you inserted Msgbox strSQL and debug.print strSQL, like I suggested, then the blank message you're seeing is via Msgbox strSQL, which indicates that strSQL doesn't contain anything.

So lets step thru you code using debug. Right after the statement strSQLWhere = vbNullString type Stop, your code now looks like this

strSQLWhere = vbNullString
stop

Now click the command button that kicks off this code. Your program will stop execution when it hits the stop statement. At this point you should see your code. From the menubar select VIEW|IMMEDIATE WINDOW.

In the Immediate Window type:
?strYourSQLSelectFromStatement
You should see your select statement displayed
Now press F11 to step thru your code 1 line at a time. If you want, after the statement strSLQWhere = strSQLWhere & "j_sft_uid=" & Lst.Column(0, varItem) & " OR " is executed type in the Immediate Window:
?strSQLWhere
This should tell you what your Where clause looks like up to this point.
After the statement strSQLWhere = Left(strSQLWhere, Len(strSQLWhere) - 4) is executed, type in the Immediate Window:
?strSQLWhere
?strYourSQLSelectFromStatement
After the statement strSQL = strYourSQLSelectStatement & strSQLWhere is executed, type in the Immediate Window:
?strSQL
This should show you the complete query. If everything looks ok, highlight what was displayed here (Select...From...Where) and press ctrl+C (copies to paste buffer). Now open a new query and select SQL View. Don't select any tables, just highlight what is currently shown in the SQL view and press ctrl+V. Now run the query.

Stepping thru your code like this, you should be able to see where you're (we're) messing up.

Press F5 continue executing your code.