Issue with subform and multiple listboxes

[tmcrouse]

I have a form and in my details I have several listboxes, 11 to be exact. I cannot use the same query for them and make columns 11, bound 1, bound 2, bound 3, etc and alter the widths because I need distinct values for each item and the database that is the main database has duplicates and can contain duplicates. The inner joins to the tables do not. For instance, the main table can have multiple state codes appear because we have multiple things we do in each state, but if you look at the state table, they are distinct. So, my example query for the state looks like this:

SELECT state.st_cd FROM state INNER JOIN qual_main ON state.st_id = qual_main.st_id GROUP BY state.st_cd;

Remember, I have 11 various listboxes that pull different items, year, month, etc. So, my form, the end-user selects what they want to see and that is to display in a subform which I have built. I have an afterupdate event for each 11 listboxes. Here is the code that I have in the main form and when I run this code I get the following error: run-time error 3075 syntax error missing operator in query expression lob.lob where lob.lob="". I have tried placing ; but that does not work.

Option Compare Database

Sub SetFilter()
Dim LSQL As String
Dim LSQL1 As String
Dim LSQL2 As String
Dim LSQL3 As String
Dim LSQL4 As String
Dim LSQL5 As String
Dim LSQL6 As String
Dim LSQL7 As String
Dim LSQL8 As String
Dim LSQL9 As String
Dim LSQL10 As String

LSQL = "select lob.lob from lob inner join qual_main on lob.lob_id = qual_main.lob_id group by lob.lob"
LSQL = LSQL & " where lob.lob = '" & List1 & "'"
Form_frmQual_sub.RecordSource = LSQL

LSQL1 = "select year_table.yr from year_table inner join qual_main on year_table.yr_id = qual_main.yr_id group by year_table.yr"
LSQL1 = LSQL1 & " where year_table.yr = '" & List2 & "'"
Form_frmQual_sub.RecordSource = LSQL1
LSQL2 = "select month_table.mth from month_table inner join qual_main on month_table.mth_id= qual_main.mth_id group by month_table.mth"
LSQL2 = LSQL2 & " where month_table.mth = '" & List3 & "'"
Form_frmQual_sub.RecordSource = LSQL2

LSQL3 = "select state.st_cd from state inner join qual_main on state.st_id = qual_main.st_id group by state.st_cd"
LSQL3 = LSQL3 & " where state_st_cd = '" & List4 & "'"
Form_frmQual_sub.RecordSource = LSQL3

LSQL4 = "select bus_unit.bus_unit from bus_unit inner join qual_main on bus_unit.bus_id = qual_main.bus_id group by bus_unit.bus_unit"
LSQL4 = LSQL4 & " where bus_unit.bus_unit = '" & List5 & "'"
Form_frmQual_sub.RecordSource = LSQL4

LSQL5 = "select product.prod_nm from product inner join qual_main on product.prod_id = qual_main.prod_id group by product.prod_nm"
LSQL5 = LSQL5 & " where product.prod_nm = '" & List6 & "'"
Form_frmQual_sub.RecordSource = LSQL5
LSQL6 = "select measures.condition_category from measured inner join qual_main on measures.measure_id = qual_main.measure_id group by measures.condition_category"
LSQL6 = LSQL6 & " where measures.condition_category = '" & list7 & "'"
Form_frmQual_sub.RecordSource = LSQL6

LSQL7 = "select measures.measure from measures inner join qual_main on measures.measure_id = qual_main.measure_id group by measures.measure"
LSQL7 = LSQL7 & " where measures.meausre = '" & list8 & "'"
Form_frmQual_sub.RecordSource = LSQL7

LSQL8 = "select measures.sub_measure from measures inner join qual_main on measures.measure_id = qual_main.measure_id group by measures.sub_measure"
LSQL8 = LSQL8 & " where measures.sub_measure = '" & list9 & "'"
Form_frmQual_sub.RecordSource = LSQL8

LSQL9 = "select communication.comm_lvl from communication inner join qual_main on communication.comm_id = qual_main.comm_id group by communication.comm_lvl"
LSQL9 = LSQL9 & " where communication_comm_lvl = '" & List10 & "'"
Form_frmQual_sub.RecordSource = LSQL9

LSQL10 = "select communication.comm_type from communication inner join qual_main on communication.comm_id = qual_main.comm_id group by communication.comm_type"
LSQL10 = LSQL10 & " where communication.comm_type = '" & List11 & "'"
Form_frmQual_sub.RecordSource = LSQL10
End Sub
Private Sub List1_AfterUpdate()
'call subroutine
SetFilter
End Sub
Private Sub List2_AfterUpdate()
'call subroutine
SetFilter
End Sub
Private Sub List3_AfterUpdate()
'call subroutine
SetFilter
End Sub
Private Sub List4_AfterUpdate()
'call subroutine
SetFilter
End Sub
Private Sub List5_AfterUpdate()
'call subroutine
SetFilter
End Sub
Private Sub List6_AfterUpdate()
'call subroutine
SetFilter
End Sub
Private Sub List7_AfterUpdate()
'call subroutine
SetFilter
End Sub
Private Sub List8_AfterUpdate()
'call subroutine
SetFilter
End Sub
Private Sub List9_AfterUpdate()
'call subroutine
SetFilter
End Sub
Private Sub List10_AfterUpdate()
'call subroutine
SetFilter
End Sub
Private Sub List11_AfterUpdate()
'call subroutine
SetFilter
End Sub
Private Sub Form_Open(Cancel As Integer)
Forms![Switchboard].Visible = False
'call subroutine
SetFilter
End Sub

[June7]

The WHERE clause must precede the GROUP BY clause. Build query in Access query designer to get correct syntax for VBA.

Could try SELECT DISTINCT instead of grouping.

Code doesn't make sense to me. Why are you setting the same form RecordSource after each LSQLx variable is set? Only the last one will ever be used.

lob.lob where lob.lob="" shows that List1 has no value, the double quotes is an empty string. If you group by lob.lob why join to qual_main.

[tmcrouse]

The qual_main is where the data is housed that is reported on. But, the other tables house the descriptions of items. For example, qual_main has st_id but that is a number. So, the state table has st_id and st_cd. Therefore I need a join that goes from qual_main to state so I can get the st_cd in my listbox for the end-user to select on. they cannot select on the st_id because that is my primary key and they would not know what that is. So, I have to have joins for these other tables and I cannot do just 1 query split it all because then I get duplicates regardless of distinct in the listboxes because a state can have multiple times the lob, which is line of business. So the state of CA can have COM appear 100 times because there are numerous programs. This is why I did the join so I could get a distinct value. I did make the changes you suggested, however I am getting an error in the 2nd statement. It does not like the code and I am so not well versed in this. I am a SAS person, not VB. I have read and read and done so much research it is nuts. I have all this listboxes the end-user can select from that do joins from the qual_main to the other tables in the DB and what I need is once they select what they want, the sub-form when the command button is entered at the end to query the qual_main table.

[June7]

Can analyse edits only if you post them. What error message?

[tmcrouse]

I get run-time error 3075 still. Maybe a screenshot would help, oops will not let me post that. Ok. So I will save as JPG.



So each of the listboxes come from a descriptive table inner join to qual_main to get the lists. then I want to make sure their selections are stored and when I click the button it runs the main query and displays on the subform.

[June7]

Doesn't help me.

If you edited code to correctly construct each SQL, then no idea why still getting error.

I still don't understand code that changes the form RecordSource 11 times.

[tmcrouse]

I am totally lost on this on how to get what I need it to do. I don't understand RecordSource 11 times? What I am trying to accomplish is I have a form that contains several listboxes. Each list box has a query that points to the descriptive table. Such as LOB which contains lob_id(primary) lob, lob_desc. Then a qual_main where all the incoming records are put but it has qid(primary key auto for distinct), lob_id, st_id, etc,etc. So, I have to join from the lob to the qual_main because if I just have qual_main as the main source, then I cannot show lob since lob_id is only in the qual_main table. So, the join allows for the lob which is what I need. I tried doing a query that I called Qual_Q1 that did all this for me. pulled in what I needed with 1 query, however putting in distinct does nothing. This is because the Qual_Q1 has a 1 to many relationship. This is why I went the 2nd route with join to qual_main. Because of that, I need to code each listbox with it's own SQL string and did it in an afterupdate statement and then tried to put all those in the sub filter() routine or maybe that is a function, but it did not work. Maybe I need a case statement? I cannot believe after all the Googling I have done since Saturday and reading I have not found or come across anyone who has mulitple listboxes or multiple anything. I could have 2 textboxes, 3 combos and 7 listboxes and they have had issue with running based on user-selection a query. I am sure there is a way to do this. There has to be. I know SAS can do it but this new job I don't get to use SAS. I have to use Access and I am pulling my hair out with all the research I have done and nothing. Maybe I am just not putting in right keywords. I put in multiple listboxes the first time and all I got was how to select multiple items in a listbox. Well I know how to do that. There has to be code to do this. I just wished I could find someone that could get me started down the right path. Not asking for someone to code it for me, just give me an idea do I need separate functions and call them all in button, do I need case statements. I just have no idea.

[June7]

I am referring to all the lines in the code that set the RecordSource of form:

...
Form_frmQual_sub.RecordSource = LSQL
...
Form_frmQual_sub.RecordSource = LSQL10
...


Doesn't matter where that procedure is called from, RecordSource will always be set to LSQL10. Yes, probably need a Select Case.

I avoid setting RecordSource of form and instead set the Filter and FilterOn properties of form. Might find this of interest http://www.allenbrowne.com/ser-62code.html


I am still not clear why you need join query to build listbox RowSource. If you have a table of states then just query the table of states. Unless you want to restrict the list to only states not already used in records?

[tmcrouse]

Ok. So what you are saying is each list box, instead of a join to my main table, I don't require that. I can just do something like:

select distinct st_cd from state; select distinct lob from lob; select distinct measure from measures; and so on and place that in my rowsource. Nothing at all goes into the recordsource anywhere. Not even on the main form. Instead use filter and filter on. I read through the link and trying to determine what to do with filter and filter on. I did alter my code based on his example for the command9 button titled click for results and had to change I think to 1 instead of 5 because st_cd is only 2. I think I am still doing something wrong because when clicking on click for results it comes back no criteria. I have my filter on load changed to yes and trying to determine what goes into the filter section. Maybe that is why nothing is found. Or maybe I have to have something different in the [] items. Maybe I have to put a query in there?

 Option Compare Database
Option Explicit
Private Sub Command8_Click()
    
    Dim strWhere As String
    Dim lngLen As Long
    
    'List Boxes.
    If Not IsNull(Me.List1) Then
        strWhere = strWhere & "([lob] = """ & Me.List1 & """) AND "
    End If
    
    If Not IsNull(Me.List2) Then
        strWhere = strWhere & "([yr] = """ & Me.List1 & """) AND "
    End If
    If Not IsNull(Me.List3) Then
        strWhere = strWhere & "([mth] = """ & Me.List3 & """) AND "
    End If
    
    If Not IsNull(Me.List4) Then
        strWhere = strWhere & "([st_cd] = """ & Me.List4 & """) AND "
    End If
    
    If Not IsNull(Me.List5) Then
        strWhere = strWhere & "([bus_unit] = """ & Me.List5 & """) AND "
    End If
    
    If Not IsNull(Me.List6) Then
        strWhere = strWhere & "([prod_nm] = """ & Me.List6 & """) AND "
    End If
    
    If Not IsNull(Me.list7) Then
        strWhere = strWhere & "([condition_category] = """ & Me.list7 & """) AND "
    End If
    
    If Not IsNull(Me.list8) Then
        strWhere = strWhere & "([measure] = """ & Me.list8 & """) AND "
    End If
    
    If Not IsNull(Me.list9) Then
        strWhere = strWhere & "([sub_measure] = """ & Me.list9 & """) AND "
    End If
    
    If Not IsNull(Me.List10) Then
        strWhere = strWhere & "([comm_lvl] = """ & Me.List10 & """) AND "
    End If
       
    If Not IsNull(Me.List11) Then
        strWhere = strWhere & "([comm_type] = """ & Me.List11 & """) AND "
    End If
    
    
    lngLen = Len(strWhere) - 1
    If lngLen <= 0 Then
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else
        strWhere = Left$(strWhere, lngLen)
        
        
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub
Private Sub Command9_Click()
    
    Dim ctl As Control
    
    For Each ctl In Me.Section(acDetail).Controls
        Select Case ctl.ControlType
        Case acListBox, acComboBox
            ctl.Value = Null
        Case acCheckBox
            ctl.Value = False
        End Select
    Next
    
    Me.FilterOn = False
End Sub
Private Sub Form_BeforeInsert(Cancel As Integer)
    
    Cancel = True
    MsgBox "You cannot add new data to this search form.", vbInformation, "Permission denied."
End Sub
Private Sub Form_Open(Cancel As Integer)
    
End Sub

[June7]

If the values are unique in the table the DISTINCT qualifier is not needed.

The form must have a RecordSource otherwise there are no records to filter. But it's a static RecordSource, not changed by code.

What you now have looks good. If not working then debug. Refer to link at bottom of my post for debugging guidelines.

[tmcrouse]

I am so ready to have a nervous breakdown. Why is this so hard? I have 30 tables in the database. So, I cannot use 1 particular table in this search form. The Qual_Main table only houses the ID's of the other tables that are descriptive. For example Qual_Main: QID, YR_ID ST_ID, etc. Then I have a Year_table, Month_table and so on. The QID is my autonumber for this main table where data is put into but based on the ID from the descriptive tables. Here is an example of what I mean:



So, the qual_main will have duplicates because you can have multiple items. NY might have program under COM (LOB) and CA under COM(LOB) So COM for the LOB will show up multiple times unless I use the actual LOB table that has distinct values. So, my list boxes are setup as the select statement for the end-user can select what they want to query on. Then the form has as QUALQ1 query that is the pictured relationship and in the query I have selected all the items ID's, descriptions and all. The QUALQ1 is in the form recordsource. Then say I go to listbox 1 which is the LOB, my control source from the QUALQ1 is lob. The row source is now select lob from lob. I cannot do the row source from the QUALQ1 because it will show duplicates and the listboxes have to be distinct. Then in the onclick event procedure for the command button, I have the code from my prior post. I still get the response no criteria. It is not picking up that I have listboxes and I selected the data or it is confusing the QUALQ1 recordsource with the queries I have on each list, but again, I cannot have duplicates in this data and there is no way to take QUALQ1 and say give me distinct because there are no distincts other than by the QID in the data, but then again states can shared lob, share quality plans. So, there will always be dupes of all the items but the QID.

[June7]

Provide db and I will look at it this weekend. Follow instructions at bottom of my post.

[tmcrouse]

OK. I figured 1 thing out, I have to do a QUALQ1 query as my record source and then I think I should beable to do a select distinct lob from qualq1 and see I get distinct information. So the only recordsource I have is QUALQ1. So, now I have to figure out the whole click for results and how to show the information that is in qualq1. Thanks

[tmcrouse]

Ok, so maybe I should try it like this for all the 11 listboxes storing in a unique temp variable. Would that work?

Private Sub list1_AfterUpdate()
    Dim oItem As Variant
    Dim sTemp As String
    Dim iCount As Integer
    
    iCount = 0
            
    If Me!List1.ItemsSelected.Count <> 0 Then
        For Each oItem In Me!List1.ItemsSelected
            If iCount = 0 Then
                sTemp = sTemp & Me!List1.ItemData(oItem)
                iCount = iCount + 1
            Else
                sTemp = sTemp & "," & Me!List1.ItemData(oItem)
                iCount = iCount + 1
            End If
        Next oItem
    Else
        MsgBox "No line of business was selected from the list", vbInformation
        Exit Sub  'Nothing was selected
    End If
    
    Me!mySelections.Value = sTemp
End Sub
Private Sub list2_AfterUpdate()
    Dim oItem As Variant
    Dim sTemp2 As String
    Dim iCount As Integer
    
    iCount = 0
            
    If Me!List2.ItemsSelected.Count <> 0 Then
        For Each oItem In Me!List2.ItemsSelected
            If iCount = 0 Then
                sTemp2 = sTemp2 & Me!List2.ItemData(oItem)
                iCount = iCount + 1
            Else
                sTemp2 = sTemp2 & "," & Me!List2.ItemData(oItem)
                iCount = iCount + 1
            End If
        Next oItem
    Else
        MsgBox "No year was selected from the list", vbInformation
        Exit Sub  'Nothing was selected
    End If
    
    Me!mySelections.Value = sTemp2
End Sub

So I can do that, but then how do I pass sTemp, sTemp2 and the other distinct variable names to be the filters for the query?

[June7]

Here is example code using listbox to build filter criteria with multiple selections of listbox. http://allenbrowne.com/ser-50.html