Variable Query based off form control - RowSource

[eking002]

Hello everyone.



I've been searching the forums and have been unable to find a good document, was hoping someone would be able to assist me.


Have an edit field and buttons on a form, that currently populate a list contro, all on the same forml. Example, you type in account number 999 into the edit feild, hit button_4 and list_0 gets populated with any accounts that match 999 string.

I am looking to add a drop down menu and some radio buttons, to futher limit the result populated in list_0.

So far I am using the following code with no isue, to populate list_0:

Public Sub SrchByAddr1_Click()
    stdocname = "COMBO"
    Me!
[List0] = "0"
    Me.List0.RowSource = "SELECT [Main].[ProjectID], [Main].[Status], [Main].[CCOMID], [Main].[FIBERstaff], [Main].[SlsMgr], [Main].[ProjNme], [Main].[PTD], [Main].[Addr1], [Main].[Addr2], [Main].[City], [Main].ID FROM [Main] where [Main].[Addr1] like " & "'*" & Me!ProjectID & "*'" & "ORDER BY [ProjectID] DESC;"
End Sub

Public Sub List0_DblClick(Cancel As Integer)
On Error GoTo Err_List0_DblClick
    Dim stLinkCriteria As String
    Dim stdocname As String
    stdocname = "Combo"
    If CurrentProject.AllForms(stdocname).IsLoaded Then
      MsgBox "The form you are attempting to open is already open." & _
      vbNewLine & "Please save what you are working on then" & _
      vbNewLine & "close the form.  Then try this operation again.", vbOKOnly, "Form Already Open"
    Else
    stLinkCriteria = "[ID]=" & Me!
[List0]
        If IsNull(Me!
[List0]) = False Then
            IfMe!
[List0] <> 0 Then
                DoCmd.OpenForm stdocname, , , stLinkCriteria
            Else
                MsgBox "Please select an account", vbOKOnly, "WARNING!"
            End If
        End If
    End If
    
Exit_List0_DblClick:
    Exit Sub
Err_List0_DblClick:
    
    Response = MsgBox("Please select an account", vbOKOnly, "WARNING!", "DEMO.HLP", 1000)
    Resume Exit_List0_DblClick
End Sub

I assume it has something to do with the 'select' section of the query, but I am unsure how or when to assign a variables value into the sql string, which is currently using rowsource to pop data.

I am still fairly new to all of this, sorry if my wording/terms are not correct.

[Dal Jeanis]

Basically, you will conditionally add conditions to the "WHERE" clause. Here's a routine which builds some SQL using that technique based on the values of two different frames with radio buttons, loads that SQL to one control and then requeries two controls. Since it is possible to not use any of the possible conditions, this code adds the " WHERE ( " keyword to the first condition that is actually selected, and uses " AND " for each selected condition after that one.

Code like this has been running for several months, but I've modified it slightly to simplify the example, so I can't guarantee it is without typos.

Private Sub BuildQuery()
Dim WhereDone As Boolean
Dim strListSQL As String
  ' the select fields are static
  WhereDone = False
  strListSQL = "SELECT [tbl_Staff].[StaffID], " & _
                "[tbl_Staff].[LastName] & "", """ & _
                "& [tbl_Staff].[FirstName] FROM [tbl_Staff] "
  
  ' the where values vary
  Select Case F1frame.Value
    Case 0
       ' do nothing
    
    Case 1
       If WhereDone Then
          strListSQL = strListSQL & " AND "
       Else
          strListSQL = strListSQL & " WHERE ("
          WhereDone = True
       End If
    
       strListSQL = strListSQL & _
       "[tbl_Staff].[FTE] = True "
    
    Case 2
       If WhereDone Then
          strListSQL = strListSQL & " AND "
       Else
          strListSQL = strListSQL & " WHERE ("
          WhereDone = True
       End If
    
       strListSQL = strListSQL & _
       "[tbl_Staff].[FTE] = False "
  
  End Select
       
  Select Case F2frame.Value
    Case 0
       ' do nothing
    
    Case 8
       If WhereDone Then
          strListSQL = strListSQL & " AND "
       Else
          strListSQL = strListSQL & " WHERE ("
          WhereDone = True
       End If
    
       strListSQL = strListSQL & _
          "[tbl_Staff].[Active] = True "
       
    Case 16
       If WhereDone Then
          strListSQL = strListSQL & " AND "
       Else
          strListSQL = strListSQL & " WHERE ("
          WhereDone = True
       End If
    
       strListSQL = strListSQL & _
          "[tbl_Staff].[Active] = False "
       
  End Select
      
  ' close the where if there is one
  If WhereDone Then
     strListSQL = strListSQL & ") "
  End If
      
  ' eliminate anything in table that populates
  ' the second list box
  If WhereDone Then
     strListSQL = strListSQL & _
        "AND NOT EXISTS (SELECT * FROM tmp_SelStaff " & _
        "WHERE tmp_SelStaff.StaffID = tbl_Staff.StaffID  )" 
  Else
     strListSQL = strListSQL & _
        "WHERE NOT EXISTS (SELECT * FROM tmp_SelStaff " & _
        "WHERE tmp_SelStaff.StaffID = tbl_Staff.StaffID  )"
  End If
  
      
      
  ' Add the order by clause
  strListSQL = strListSQL & _
     "ORDER BY [tbl_Staff].[LastName]& "", """ & _
     "& [tbl_Staff].[FirstName];"
     
 ' for testing, show sql
 ' MsgBox strListSQL
 
 ' load sql
 F3lstLeft.RowSource = ""
 F3lstLeft.RowSourceType = "Table/Query"
 F3lstLeft.RowSource = strListSQL

 ' requery both list boxes
 Me.F3lstLeft.Requery
 Me.F5lstRight.Requery
 
End Sub

[eking002]

Thank you for your reply. I am having a hard time fully understanding your example thouhg.

Obviously, I cannot just copy/paste it, so I am tying to figure out how to adapt it to my needs the best I can.
So I would need to change all of my individual buttons/code (Me.List0.RowSource as an example), to be Case statements?
Or, keep the rowsource and keep assigning values to the sql string holder, depending on criterea?

I am unclear exactly which type of control element I will be using, be it a option, checkbox, DDL or whatever. My english is not the best at time, sorry.

[Dal Jeanis]

Here's the only idea that you need to understand - You can build SQL commands one phrase at a time. In your first example code, which is pretty good, that means just adding more stuff in between "WHERE" and "ORDER BY".

You can use literally ANY control you want - checkboxes, text boxes, radio buttons in a frame, or whatever. in your case, you would probably build the SQL in the CLICK event of button-4.

My code example was for two frames called F1frame and F2frame.

F1frame contained three radio buttons, The first is labeled "All", and has value 0 when selected, The second says "FTE only", and has value 1 when selected, and the third says "Contractor Only", and has value 2 when selected. The sample code built a where condition to select the appropriate data.

F2frame contained three radio buttons, The first is labeled "All", and has value 0 when selected, The second says "Active only", and has value 8 when selected, and the third says "Inactive Only", and has value 16 when selected. The sample code built a where condition to select the appropriate data.

Here's a rough-in of the kind of code you need.

Dim WhereDone As Boolean
Dim strSQL As String

  ' the select fields are static
  WhereDone = False
  strSQl = "SELECT ProjectID, Status, CCOMID, FIBERstaff, SlsMgr, " & _
           "ProjNme, PTD, Addr1, Addr2, City, ID FROM [Main] " 

  ' if anything in text box for address, add condition for like
  if (txtAddr1 > "" )
      if  whereDone  Then  
         strSQl = strSQl & " AND Addr1 like '*" & Me!txtAddr1 & "*' " 
      Else 
         strSQl = strSQl & " WHERE ( Addr1 like '*" & Me!txtAddr1 & "*' " 
         Wheredone = True
      End If

  ' if anything in text box for city, add condition for exact match
  if (txtCity > "" )
      if  whereDone  Then  
         strSQl = strSQl & " AND City = '" & Me!txtCity & "' " 
      Else 
         strSQl = strSQl & " WHERE ( City = '" & Me!txtCity & "' " 
         Wheredone = True
      End If

  ' if check box for active is set, require status = 'active'
  If (chkActiveOnly is True)
      if  whereDone  Then  
         strSQl = strSQl & " AND Status = 'Active' " 
      Else 
         strSQl = strSQl & " WHERE ( Status = 'Active' " 
         Wheredone = True
      End If

 ' close the where if there is one
  If WhereDone Then
     strSQL = strSQL & ") "
  End If

 ' order by project
  strSQl =   strSQl & "ORDER BY [ProjectID] DESC;"

 ' Uncomment to Display SQL for testing
 ' MsgBox strSQL

 ' set the rowsource for the listbox and then requery
    Me!
[List0] = "0"
    Me.List0.RowSourceType = "Table/Query" 
    Me.List0.RowSource = strSQL
    Me.List0.Requery

[eking002]

After seeing your second example and explination things are much clearer for me. I was able to get it to filter as I originally asked, using a checkbox true/false.

Thank you so much for your assistance with this.

[Dal Jeanis]

Glad to help. Please mark thread solved. (Top of page, last option under Thread Tools.)