Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2013
    Posts
    5

    Post Unanswered: Use a single control button to query more fields or combo boxes on a form

    Hi,
    I have a database with tables and fields. I have created a form that with three to four combo boxes and inserted a control button.
    I would like a write a code to query my data based on the selection(s) made from the combo boxes when I click the command button.

    I have this code and it is working perfectly well, only that I have created multiple command button to perform the queries I need.

    Here is what I have:
    Code:
    Private Sub cmdGetRecord_Click()
    Dim Filter As String
    Dim strProgram As String, strCohort As String, strCounty As String
     
     If Me![cboSProgram] = "Select Program" Then Exit Sub
     If IsNull(Me![cboSProgram]) Then Exit Sub
     If Me![cboSCohort] = "Select Cohort" Then Exit Sub
     If IsNull(Me![cboSCohort]) Then Exit Sub
     If Me![cboSCounty] = "Select County" Then Exit Sub
     If IsNull(Me![cboSCounty]) Then Exit Sub
     
         strProgram = Nz(Me.cboSProgram, "*")
         strCohort = Nz(Me.cboSCohort, "*")
         strCounty = Nz(Me.cboSCounty, "*")
         Filter = "strProgram = """ & cboSProgram & """ And strCohort = """ & cboSCohort & """ And strCounty = """ & cboSCounty & """"
         DoCmd.OpenForm "tblSortSchools", acFormDS, , Filter
    
         cboSProgram = "Select Program"
         cboSCohort = "Select Cohort"
         cboSCounty = "Select County"
         cboSCohort.Requery
         cboSCounty.Requery
    End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what you need to do is write your where clause on the fly then apply that as either a filter or requery
    there's several answers for this already posted... IIRC theres one for usewr:-BrastedHouse within the last two weeks
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2013
    Posts
    5

    Post

    Thanks,
    I don't just seem to get where your reference seem to fit in with my code. can you indicate within my code the lines you are suggesting?
    Thanks

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    im not
    what I am suggesting is that you build the where clause on the fly
    if there is a value in a specific combo box then add it to the where clause, if there isn't don't. or if you are suing a filter then a filter is the same as a where claasue, just that it don't use a where clasue
    eg a where clause might be
    where userid = 'Stoic'
    the same thing expressed as a filter
    userid = 'Stoic'
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2013
    Posts
    5

    Post

    Ok, thanks.
    I will try it out to see how it works.

  6. #6
    Join Date
    Nov 2013
    Posts
    5

    Question

    Hi,
    I seem to be having some problem with my code and I am receiving an error. this is what I have now:
    Code:
        Dim strSQLHead      As String
        Dim strSQLWhere     As String
        Dim strSQLOrderBy   As String
        Dim strSQL          As String
        Dim strJoin         As String
        
        strJoin = " AND "
        strSQLHead = "SELECT * FROM tblNationalSchool "
        
        If Len(Me.cboSProgram & vbNullString) Then
            If (Me.chkLike) Then
                strSQLWhere = "WHERE [strProgram] Like " & Chr$(39) & "*" & Me.cboSProgram & "*" & Chr$(39)
            Else
                strSQLWhere = "WHERE [strProgram] = " & Chr$(39) & Me.cboSProgram & Chr$(39)
            End If
        
            strSQLWhere = strSQLWhere & strJoin
        
        End If
        
        If Len(Me.cboSCohort & vbNullString) Then
            If Len(strSQLWhere) = 0 Then
                strSQLWhere = "WHERE "
            End If
            
            strSQLWhere = strSQLWhere & "[strCohort] = " & Me.cboSCohort
            
            strSQLWhere = strSQLWhere & strJoin
        End If
        
        If Len(Me.cboSCounty & vbNullString) Then
            If Len(strSQLWhere) = 0 Then
                strSQLWhere = "WHERE "
            End If
            
            strSQLWhere = strSQLWhere & "[strCounty] = " & Me.cboSCounty
            
            strSQLWhere = strSQLWhere & strJoin
        End If
        
        If Len(Me.cboTreatmentType & vbNullString) Then
            If Len(strSQLWhere) = 0 Then
                strSQLWhere = "WHERE "
            End If
            
            strSQLWhere = strSQLWhere & "[strTreatmentType] = " & Me.cboTreatmentType
            
            strSQLWhere = strSQLWhere & strJoin
        End If
       
        If Len(strSQLWhere) Then
            strSQLWhere = Left$(strSQLWhere, Len(strSQLWhere) - (Len(strJoin) - 1))
        End If
        
        strSQLOrderBy = "ORDER BY "
        Select Case Me.fraOrderBy
        Case 1
            strSQLOrderBy = strSQLOrderBy & "[strProgram]"
        Case 2
            strSQLOrderBy = strSQLOrderBy & "[strCohort]"
        Case 3
            strSQLOrderBy = strSQLOrderBy & "[strCounty]"
        Case 4
            strSQLOrderBy = strSQLOrderBy & "[strTreatmentType]"
        End Select
        
        strSQL = strSQLHead & strSQLWhere & strSQLOrderBy
        
        Me.tblSortSchools.Form.RecordSource = strSQL
    
    End Sub
    And when I execute the command this is the error message I get:

    Code:
    Syntax error (missing operator) in your query expression '[strProgram] Like '*In-Service*' AND [strCohort] = Cohort-02 AND '[strCounty] = Sinoe County AND [strTreatmentType] = Cathment'.
    I am not sure what I am missing at this point.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •