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.

 

[Crystalguru]

I was able to get the SQL statement and the Where info from the immediate window. Pasted it into the SQL Query Designer and ran it. No problems. But still cannot click on the button and not get error 3129...

After the STOP statement, I had to use F8 instead of F11. It stepped through the code but didn't "error" at any of the lines. Just continued through...?

At this point in time I am feeling cursed....

 

[FancyPrairie]

The fact that you were able to examine the contents of strSQL (via the debugger's immediate window) and paste its results in the Query Builder and it ran ok, puzzles the heck out of me. Why don't you post your entire procedure (the procedure that is launched when the command button is clicked). So far you've just been posting bits and pieces. Let's look at the whole thing. I have an idea where the problem may be.

By the way, I didn't expect any errors to show up when stepping thru your code via debug. Just wanted you to examine variables to make sure that they contained what you expected them to.

 

[Crystalguru]

Here it is:
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 strSQLWhere As String
Dim varItem As Variant
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
strSQLWhere = strSQLWhere & "j_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
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

If you can figure this one out- you are the VB GURU!!

 

[FancyPrairie]

FOUND YOUR PROBLEM. You misspelled strYourSQLSelectFromStatement. It seems some posts (i.e. 1st post) had it right and others didn't. This statement:
strSQL = strYourSQLSelectStatement & strSQLWhere
should be
strSQL = strYourSQLSelectFromStatement & strSQLWhere

You should ALWAYS use Opition Explicit in your modules. Then Access will catch spelling errors because it forces you to delcare all variables

 

[FancyPrairie]

To ensure that all modules you create default to Option Explicit, open a module and then select TOOLS|OPTIONS. Then, under the Editor tab, check "Require Variable Declarations"

 

[Crystalguru]

You've got to be kidding, this whole time it's been a spelling error! I turned on the Require Variable Declarations and won't EVER turn it off! Is there a limit to how many post a thread can have? I think I have max'ed the limit and may not be welcome here anymore due to stupidity.

Ok. I don't get any errors when I click on my command button. My next task: How do I get a query out of this??

Do you really want to tackle another one with me?

Thanks for hanging in there with me! Can I send a note to your boss and tell them how wonderful you are?

 

[FancyPrairie]

I'm so glad we got it working.

Yes you do get a query out of this (in fact, it's already been created). The name of the query is "Find Candidate". The statement "Set qdf = dbs.CreateQueryDef("Find Candidate", strSQL)" is the statement that created the query. To run the query, in the Database Window, select Queries and then select "Find Candidate".

But, since you're asking the question "How do I get a query out of this?? ", I've got a bad feeling that that's not what you're really trying to do.

 

[Crystalguru]

Your going to kill me! I found the query. And it is EXACTLY what I was looking for. This whole thing has been a learning process for me. Thank you for being very kind and very detailed in your explanations. I hope you won't be scared to answer another of my post...

take care
crystalguru (I'll leave the VB to you!)

 

[hycoo]

Just want you people to know your timing is perfect, I have been trying to do this exact thing, and have gotten bits and pieces of code but never knew what to do with it. I am what they call an Access power user and just getting my feet we with SQL and VBA. My boss has put me in for sainthood (to bad the paycheck doesn't reflect it) . Anyway thanks for your help!

 

[barbola]

Can someone help me also? I don't know where to put the code. When I click on the build button it takes me to the macro builder, not vba. I can get to vba another way, but have no idea where to put the code. there is nothing there.

 

[FancyPrairie]

For testing, start out by putting a command button on your form that says something like "build query" or something. Then double click inside the OnClick property of the command button. (The words [Event Procedure] will appear.) Or, via the drop down list, select [Event Procedure]. Then select the build button. Now you copy and paste the code from these posts.

 

[barbola]

I think what I am trying to do is different. I have a category table, and a detail table. I want my report to print the details for items matching the categories selected in the list box.

IE. If I select category A, all the records in detail should appear on my report that have category = A.

I only want my list box to have the categories. I think the examples I am finding show all the detail records in the list box, and you select what you want to print. That is not for me. Can anyone help?

 

[Onyxpurr]

Just some changes I had to make, but thanks for the code!!! To test, rather than creating the whole sql statement, I used a msgbox to show the results, therefore the last sentence.

Dim strSQLWhere As String
Dim i As Integer

For i = 1 To Me.lstBatch.ItemsSelected.Count
strSQLWhere = strSQLWhere & "[SCID]='" & Me.lstBatch.Column(0, Me.lstBatch.ItemsSelected.Item(i - 1)) & "' OR "
Next i

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

MsgBox strSQLWhere

 

[CTAdmin]

I appreciate this forum VERY, VERY much!!! I was tasked with building a query based on 28 user input variables or lack thereof (i.e. They may or may not use all 28!). My supervisor then asked to be able to choose any combination of 24 fields to be included in the report when the results were returned! UUUUUGGGGGGGGHHHHHHHHHHH!!!

I was so happy to find this forum and I gave out several stars for the WONDERFUL posts.

If someone is looking for a deviation of this code (i.e. Custom Built SELECT & WHERE Statements), let me know. Only one of us should have to beat our heads against the wall!

[Hammer]

 

[jdegeorge]

Hi, FancyPrairie and CrystalGuru

This code is very close to what I need. I've got a form with a few multi-select list boxes where the user can select values in one or more boxes, but DOES NOT have to select values from ALL boxes. How can this code be adapted to fit this situation? I'm thinking that the final search SQL will be very long, but how do you ignore list boxes with ItemsSelected.Count = 0?

Jim DeGeorge

 

[RamziSaab]

how about an if statement

if itemselected.count = 0 then
else
'your code
end if

 

[FancyPrairie]

That get's a little more complicated. Because, sometimes the listboxes don't apply to the report the user selects. Also, you may, depending on the report the user selects, want to default the selections in some of the list boxes. Therefore, you will need to check to see if the list box is enabled or not. If not enabled, then assume that the report does not need it. If the listbox defaults to some predefined selections, then lock it so the user can't change it. Finally, your SQL statement may need to add join clauses.

But if it's simple then loop thru the code controls on your form and when you encounter a list box, then process it. Something like this:


Dim i as Integer

For i = 0 To frmLB.Count - 1 'Loop thru all controls on form

If (frmLB.Controls(i).ControlType = acListBox) Then 'IFT, control represents a List Box


If ((frmLB.Controls(i).Enabled) And (Not frmLB.Controls(i).Locked)) Then 'IFT, List box is enabled, therefore, process it

Now proceed with the code above as far as creating your Where Clause.

I use the tag property to store information I need concerning building the Where clause. The Tag Property looks something like this:

TagProperty...Where=TableName,FieldName,Type;

Where TableName equals the name of the table in which the Field (FieldName) resides. Type represents either String, Number, etc. When you build the Where clause you now know what to put it in it.

I created a function (and put it in my library database) that will return the Where clause. I pass it the name of the form that contains the Listboxes. This becomes alot complicated to build, but once built, takes a lot less time to build reports.

 

[FancyPrairie]

By the way, if the ItemSelected.Count = 0, then just don't process that list box.

 

[jdegeorge]

Hi:

With the help of DHOOKOM, NATE1749 and other Tek-Tip'ers, I got this to work. Here's the code:

Private Sub btnPrintReport_Click()

On Error GoTo Err_btnPrintReport_Click

Dim ctl As Control
Dim varItem As Variant
Dim strFilter As String

Dim strlstAreaFunction As String
Dim strlstCategory As String

strFilter = ""
strlstAreaFunction = ""
strlstCategory = ""


'Gather all List Box Variables
Set ctl = Me!lstAreaFunction

If ctl.ItemsSelected.Count > 1 Then
For Each varItem In ctl.ItemsSelected
If strlstAreaFunction = "" Then
strlstAreaFunction = "[Area/Function] in (" & ctl.ItemData(varItem) & ", "
Else
strlstAreaFunction = strlstAreaFunction & ctl.ItemData(varItem)
End If
Next varItem
ElseIf ctl.ItemsSelected.Count = 1 Then
For Each varItem In ctl.ItemsSelected
If strlstAreaFunction = "" Then
strlstAreaFunction = "[Area/Function] in (" & ctl.ItemData(varItem)
End If
Next varItem
End If

If strlstAreaFunction <> &quot;&quot; Then
strlstAreaFunction = strlstAreaFunction & &quot&quot;
End If



Set ctl = Me!lstCategory

If ctl.ItemsSelected.Count > 1 Then
For Each varItem In ctl.ItemsSelected
If strlstCategory = &quot;&quot; Then
strlstCategory = &quot;[Category] in (&quot; & ctl.ItemData(varItem) & &quot;, &quot;
Else
strlstCategory = strlstCategory & ctl.ItemData(varItem)
End If
Next varItem
ElseIf ctl.ItemsSelected.Count = 1 Then
For Each varItem In ctl.ItemsSelected
If strlstCategory = &quot;&quot; Then
strlstCategory = &quot;[Category] in (&quot; & ctl.ItemData(varItem)
End If
Next varItem
End If

If strlstCategory <> &quot;&quot; Then
strlstCategory = strlstCategory & &quot&quot;
End If




'Concatenate all List Box Variables
If strlstAreaFunction <> &quot;&quot; Then
If strFilter = &quot;&quot; Then
strFilter = strlstAreaFunction
Else
strFilter = strFilter & &quot; And &quot; & strlstAreaFunction
End If
End If

If strlstCategory <> &quot;&quot; Then
If strFilter = &quot;&quot; Then
strFilter = strlstCategory
Else
strFilter = strFilter & &quot; And &quot; & strlstCategory
End If
End If


DoCmd.OpenReport &quot;Area/Function&quot;, acPreview, , strFilter

Exit_btnPrintReport_Click:
Exit Sub

Err_btnPrintReport_Click:
MsgBox Err.Description
Resume Exit_btnPrintReport_Click

End Sub

Yeah! Thanks to everyone for their help!

Jim DeGeorge

 

[FancyPrairie]

The code you created above will only work for just one form (the current form). However, if you modify it a little bit and take advantage of the tag property, you can use the same code for every form you create that will be used as a Report Criteria form.

The following code assumes that the listboxes that you want included contain the following structure for the Tag Property.

Tag Property...Where=TableName.FieldName,DataType,

In your example, the tag property would look something like this:

Where=NameOfYourTable.[Area/Function],Number,

The following function will return the Where clause (In clause). To open your report, you would call the function like this (assuming no errors are produced).

DoCmd.OpenReport &quot;Area/Function&quot;, acPreview, , BuildWhereClause(Me)

Function BuildWhereClause(frm As Form) As String

'********************************
'*  Declaration Specifications  *
'********************************

    Dim ctl As Control
    
    Dim varItem As Variant
    
    Dim strAnd As String
    Dim strField As String
    Dim strFilter As String
    Dim strType As String
    
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    
    On Error GoTo ErrHandler

'****************
'*  Initialize  *
'****************

    strFilter = vbNullString
    strAnd = vbNullString
    
'*********************************************************
'*  Loop thru all controls on form to find list box(es)  *
'*********************************************************

    For Each ctl In frm.Controls
                
        If (ctl.ControlType = acListBox) Then
                
        '*************************************************************************************************
        '*  Should this list box be processed?                                                           *
        '*  If so, then tag property contains the name of the table and field and the type of the field  *
        '*      (Structure of tag property:  Where=TableName.FieldName,DataType,    )                    *
        '*      NOTE that the code assumes the tag property is structured properly                       *
        '*************************************************************************************************
            
            If ((ctl.Enabled) And (Not ctl.Locked) And (ctl.ItemsSelected.Count > 1) And (InStr(ctl.Tag, "Where=") > 0)) Then
                
                j = InStr(ctl.Tag, "Where=")
                k = InStr(j, ctl.Tag, ",")
                strField = Mid(ctl.Tag, j + 6, k - (j + 6))
                
                j = InStr(k + 1, ctl.Tag, ",")
                strType = Mid(ctl.Tag, k + 1, j - k - 1)
                
                strFilter = strFilter & strAnd & "[" & strField & "]" & " In ("
                
        '******************************************
        '*  Loop thru items selected in list box  *
        '******************************************
        
                For Each varItem In ctl.ItemsSelected
                    
                    If (strType = "String") Then
                        strFilter = strFilter & "'" & ctl.ItemData(varItem) & "', "
                    ElseIf (strType = "Number") Then
                        strFilter = strFilter & ctl.ItemData(varItem) & ", "
                    End If
                    
                Next varItem
    
                strFilter = Mid(strFilter, 1, Len(strFilter) - 2) & ") "
                strAnd = " AND "
                
            End If
        End If
    
    Next
    
'***********************************
'*  Return Where clause to caller  *
'***********************************

    BuildWhereClause = strFilter
    
'********************
'*  Exit Procedure  *
'********************
        
ExitProcedure:

    Exit Function

'****************************
'*  Error Recovery Section  *
'****************************
        
ErrHandler:
        
    MsgBox Err.Number & vbCrLf & Err.Description, vbExclamation
    
    BuildWhereClause = "!!!ERROR!!!"
    
    Resume ExitProcedure

End Function