Page 2 of 2 FirstFirst 12
Results 16 to 28 of 28
  1. #16
    AndyRob1973 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    81
    Thanks you June7 and ssfanu - I seem to have cracked it (for now) using a combination of your suggestions.....

  2. #17
    AndyRob1973 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    81
    The code below is what I have ended up using. On the click of a ‘Run query’ button this code opens a query.
    I would like second button to open a report using the same query. I could do this by just copying the code from the ‘run query’ button and add a docmd to open a report. But I’m assuming that I should only write the code once and refer to it in both buttons.

    Could anyone point me in the right direction?

    Thanks

    Code:
    Private Sub OpenQuery_Click()
    'On Error GoTo Err_cmdOpenQuery_Click
        Dim MyDB As DAO.Database
        Dim qdef As DAO.QueryDef
        Dim i As Integer
        Dim strSQL As String
        Dim strWhere As String
        Dim strSpec As String
        Dim strStat As String
        Dim strLCRF As String
        Dim strEpid As String
        Dim varItem As Variant
    
        Set MyDB = CurrentDb()
    
        strSQL = "SELECT * FROM tblOTR"
        
    'SPECIALTY CRITERIA
    
         'Build the specialty criteria string by looping through the listbox
        For i = 0 To lstSpec.ListCount - 1
            If lstSpec.Selected(i) Then
            strSpec = strSpec & "'" & lstSpec.Column(0, i) & "',"
            End If
        Next i
        
        ' strip off the last comma
        If Len(strSpec) > 0 Then
        strSpec = Left(strSpec, Len(strSpec) - 1)
        End If
        
        ' if nothing has been selected then don't add to the strwhere
            If Len(strSpec) > 0 Then
            strWhere = strWhere & "[Specialty] in (" & strSpec & ") AND "
            End If
            
    'STATUS CRITERIA
        
        'build the status criteria - if null don't add to the criteria
            If Not IsNull(Me.cmbStat) Then
            strWhere = strWhere & "[Status] = '" & Me.cmbStat & "' AND "
            End If
            
    'LCRF CRITERIA
    
        'build the LCRF criteria - if null don't add to the criteria
            If Not IsNull(Me.cmbLCRF) Then
            strWhere = strWhere & "[LCRF] = '" & Me.cmbLCRF & "' AND "
            End If
            
    'EPIDEMIOLOGY CRITERIA
            
       'Build the Epidemiology criteria string by looping through the listbox
        For i = 0 To lstEpid.ListCount - 1
            If lstEpid.Selected(i) Then
            strEpid = strEpid & "'" & lstEpid.Column(0, i) & "',"
            End If
        Next i
        
        ' strip off the last comma
        If Len(strEpid) > 0 Then
        strEpid = Left(strEpid, Len(strEpid) - 1)
        End If
            
        ' if nothing has been selected then don't add to the strwhere
         If Len(strEpid) > 0 Then
         strWhere = strWhere & "[Epidemiology] in (" & strEpid & ") AND "
         End If
            
    'WHERE CLAUSE
            
      ' there will be a trailing ' AND ' at the end so strip this off
      If Len(strWhere) > 0 Then
      strWhere = Left(strWhere, Len(strWhere) - 5)
      End If
        
       'MsgBox strEpid
       'MsgBox strWhere
       
       'if there is no criteria selected then the strWhere will be empty
       'in which case don't add the where clause
       
       If Len(strWhere) > 0 Then
       strSQL = strSQL & " WHERE " & strWhere
       End If
       
        
       'MsgBox strSQL
       
     
    'RUN THE QUERY
     
        MyDB.QueryDefs.Delete "qryOTR"
        
        Set qdef = MyDB.CreateQueryDef("qryOTR", strSQL)
        
        'Open the query, built using the IN clause to set the criteria
        DoCmd.OpenQuery "qryOTR", acViewNormal
    
    
    
    'CLEAR THE CRITERIA SELECTION
    
        'Clear specialty listbox selection after running query
        For Each varItem In Me.lstSpec.ItemsSelected
            Me.lstSpec.Selected(varItem) = False
        Next varItem
    
        ' clear status combo selection after running query
        
        Me.cmbStat.Value = Null
        
        ' clear LCRF combo selection after running query
      
        Me.cmbLCRF.Value = Null
        
        
        'Clear epidemiology listbox selection after running query
        For Each varItem In Me.lstEpid.ItemsSelected
            Me.lstEpid.Selected(varItem) = False
        Next varItem

  3. #18
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    happy you got it working for you.

    Quote Originally Posted by AndyRob1973 View Post
    The code below is what I have ended up using. On the click of a ‘Run query’ button this code opens a query.
    I would like second button to open a report using the same query. I could do this by just copying the code from the ‘run query’ button and add a docmd to open a report. But I’m assuming that I should only write the code once and refer to it in both buttons.

    Could anyone point me in the right direction?

    However, I see you are still deleting the query, then recreating it.
    Consider:
    1) I don't know how often you will be executing this query, but constantly deleting, then recreating the query causes dB bloat. If something causes an error and the query is not recreated, the code will not run until the query is manually created.
    2a) Once you have saved the query with the criteria you want, the query can be used as the record source for the report. You don't have to "copy the code" to create the query again!! Just have a button to open the report.
    2b) I just had a thought.... maybe you want to just click the report button and skip the query button.... then you would have to copy the code.




    I still recommend changing the SQL of the saved query, rather than deleting, then recreating the query.

  4. #19
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Think ssanfu meant:

    ".... then you would NOT have to copy the code"

    Or have code in a Sub that is called by both buttons.

    But why even open query object?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #20
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Yep... it is a PBKAC. Brain was out thinking while the fingers were hitting the keys...... Brain got sidetracked.....

  6. #21
    AndyRob1973 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    81
    I think my brain is going too, I posted some old code by mistake. The actual code is below – sorry! I am fairly new to this side of access.

    I’ll try to explain. I have a main form with a variety of combo and list boxes on. I have a subform within this main form whose record source is determined by the code below and the ‘run query’ changes the results of the subform. This seems to be working ok using the code below.

    In addition I have a report which I would like to be based on the same query results as above and id like a button that runs it. However, at the moment I only know how to construct the code from scratch within this button – this means I will be repeating the main part of the code from the run query button. Is there a way of referring to code from one button to another – or do I need a module (I don’t know how to do this!)

    Also ssfanu, is my code now better as it doesn’t delete the query?

    Thankyou!

    Code:
    Private Sub RunQueryButton_Click()
    
    
       ' Dim myspecstat As String
        Dim strWhere As String
        Dim strSQL As String
        Dim strSpec As String
        Dim strStat As String
        Dim strSpons As String
        Dim strLCRF As String
        
       strSQL = "SELECT * FROM tblOTR"
        
    'STATUS CRITERIA
    
         'Build the status criteria string by looping through the listbox
        For i = 0 To lst_status.ListCount - 1
            If lst_status.Selected(i) Then
            strStat = strStat & "'" & lst_status.Column(0, i) & "',"
            End If
        Next i
        
    ' strip off the last comma
        If Len(strStat) > 0 Then
        strStat = Left(strStat, Len(strStat) - 1)
        End If
        
    ' if nothing has been selected then don't add to the strwhere
        If Len(strStat) > 0 Then
        strWhere = strWhere & "[Status] in (" & strStat & ") AND "
        End If
    
           
    'FUNDING CRITERIA
        
        'build the funding criteria - if null don't add to the criteria
            If Not IsNull(Me.cmbComA) Then
            strWhere = strWhere & "[Commercial/Academic] = '" & Me.cmbComA & "' AND "
            End If
    
    
           
    'SPECIALTY CRITERIA
    
         'Build the specialty criteria string by looping through the listbox
        For i = 0 To lst_spec.ListCount - 1
            If lst_spec.Selected(i) Then
            strSpec = strSpec & "'" & lst_spec.Column(0, i) & "',"
            End If
        Next i
        
        ' strip off the last comma
        If Len(strSpec) > 0 Then
        strSpec = Left(strSpec, Len(strSpec) - 1)
        End If
        
        ' if nothing has been selected then don't add to the strwhere
        If Len(strSpec) > 0 Then
        strWhere = strWhere & "[Specialty] in (" & strSpec & ") AND "
        End If
        
    
    'SPONSOR CRITERIA
    
         'Build the sponsor criteria string by looping through the listbox
        For i = 0 To lst_sponsor.ListCount - 1
            If lst_sponsor.Selected(i) Then
            strSpons = strSpons & "'" & lst_sponsor.Column(0, i) & "',"
            End If
        Next i
        
        ' strip off the last comma
        If Len(strSpons) > 0 Then
        strSpons = Left(strSpons, Len(strSpons) - 1)
        End If
        
        ' if nothing has been selected then don't add to the strwhere
        If Len(strSpons) > 0 Then
        strWhere = strWhere & "[Sponsor] in (" & strSpons & ") AND "
        End If
    
    
    'SUBSPECIALTY CRITERIA
        
        'build the LCRF criteria - if null don't add to the criteria
            If Not IsNull(Me.cmbLCRF) Then
            strWhere = strWhere & "[LCRF] = '" & Me.cmbLCRF & "' AND "
            End If
            
        'build the Epidemiology criteria - if null don't add to the criteria
            If Not IsNull(Me.cmbEpid) Then
            strWhere = strWhere & "[Epidemiology] = '" & Me.cmbEpid & "' AND "
            End If
            
        'build the Biomarker criteria - if null don't add to the criteria
            If Not IsNull(Me.cmbBioM) Then
            strWhere = strWhere & "[Bio Marker] = '" & Me.cmbBioM & "' AND "
            End If
            
        'build the Diagnostic criteria - if null don't add to the criteria
            If Not IsNull(Me.cmbDiag) Then
            strWhere = strWhere & "[Diagnostic] = '" & Me.cmbDiag & "' AND "
            End If
            
        'build the Qualitative criteria - if null don't add to the criteria
            If Not IsNull(Me.cmbQual) Then
            strWhere = strWhere & "[Qualitative] = '" & Me.cmbQual & "' AND "
            End If
            
        'build the Palliative & Supportive Care criteria - if null don't add to the criteria
            If Not IsNull(Me.cmbPaSC) Then
            strWhere = strWhere & "[Palliative & Supportive Care] = '" & Me.cmbPaSC & "' AND "
            End If
            
        'build the Observational criteria - if null don't add to the criteria
            If Not IsNull(Me.cmbObse) Then
            strWhere = strWhere & "[Observational] = '" & Me.cmbObse & "' AND "
            End If
            
        'build the Neo adjuvant criteria - if null don't add to the criteria
            If Not IsNull(Me.cmbNeoA) Then
            strWhere = strWhere & "[Neo adjuvant] = '" & Me.cmbNeoA & "' AND "
            End If
            
        'build the Adjuvant criteria - if null don't add to the criteria
            If Not IsNull(Me.cmbAdju) Then
            strWhere = strWhere & "[Adjuvant] = '" & Me.cmbAdju & "' AND "
            End If
            
        'build the Radical criteria - if null don't add to the criteria
            If Not IsNull(Me.cmbRadi) Then
            strWhere = strWhere & "[Radical] = '" & Me.cmbRadi & "' AND "
            End If
            
        'build the Radiotherapy criteria - if null don't add to the criteria
            If Not IsNull(Me.cmbXRT) Then
            strWhere = strWhere & "[Radiotherapy] = '" & Me.cmbXRT & "' AND "
            End If
            
        'build the Surgical criteria - if null don't add to the criteria
            If Not IsNull(Me.cmbSurg) Then
            strWhere = strWhere & "[Surgical] = '" & Me.cmbSurg & "' AND "
            End If
            
            
        'build the 1st line metastatic criteria - if null don't add to the criteria
            If Not IsNull(Me.cmb1stM) Then
            strWhere = strWhere & "[1st line metastatic] = '" & Me.cmb1stM & "' AND "
            End If
            
        'build the 2nd line metastatic criteria - if null don't add to the criteria
            If Not IsNull(Me.cmb2ndM) Then
            strWhere = strWhere & "[2nd line metastatic] = '" & Me.cmb2ndM & "' AND "
            End If
            
        'build the 3rd line metastatic criteria - if null don't add to the criteria
            If Not IsNull(Me.cmb3rdM) Then
            strWhere = strWhere & "[3rd line metastatic] = '" & Me.cmb3rdM & "' AND "
            End If
            
        'build the Hormone receptor criteria - if null don't add to the criteria
            If Not IsNull(Me.cmbHorR) Then
            strWhere = strWhere & "[Hormone receptor] = '" & Me.cmbHorR & "' AND "
            End If
    
    
    'WHERE CLAUSE
            
      ' there will be a trailing ' AND ' at the end so strip this off
      If Len(strWhere) > 0 Then
      strWhere = Left(strWhere, Len(strWhere) - 5)
      End If
         
       'if there is no criteria selected then the strWhere will be empty
       'in which case don't add the where clause
         
        If Len(strWhere) > 0 Then
       strSQL = strSQL & " WHERE " & strWhere
       MsgBox " You have selected: " & strWhere
       Else
       MsgBox " You have selected: " & " All Records"
       End If
       
       
       
       'MsgBox " You have selected: " & strWhere
        
        'myspecstat = "select * from tblOTR where [Status] LIKE '*" & Me.cmb_Status & "*' AND [Specialty] LIKE '*" & Me.cmb_Specialty & "*'"
        Me.[tblOTR subform].Form.RecordSource = strSQL

  7. #22
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Put code that builds filter into a procedure behind form, call it something like: Function BuildFilter() then both click events can call procedure - function would return string to calling procedure
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #23
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by AndyRob1973 View Post
    Also ssfanu, is my code now better as it doesn’t delete the query?
    OK, now you are changing the FORM's record source, bypassing the query. This is OK as long as it works for you - you get to decide the method you use to set the SQL.
    But I want to point out your question about using the same data set for a report as well as a form. Because you are no longer using the query, you will have to recreate the SQL string (that you are using for the form) for the report.

    On one hand you can use the SQL string or a query for the record source........... on the other hand there are four fingers and a thumb.
    Sometimes I use queries, sometimes I use SQL strings. It depends on how I feel that day. The main thing is that you have it returning the results you want.

    The "Best" way is subjective and an always moving target.




    Good luck on your project...............

  9. #24
    AndyRob1973 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    81
    Quote Originally Posted by June7 View Post
    Put code that builds filter into a procedure behind form, call it something like: Function BuildFilter() then both click events can call procedure - function would return string to calling procedure
    Thankyou. It's a while since I've created a function so I may be back for a nudge in the right direction!

  10. #25
    AndyRob1973 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    81
    Quote Originally Posted by ssanfu View Post
    OK, now you are changing the FORM's record source, bypassing the query. This is OK as long as it works for you - you get to decide the method you use to set the SQL.
    But I want to point out your question about using the same data set for a report as well as a form. Because you are no longer using the query, you will have to recreate the SQL string (that you are using for the form) for the report.

    On one hand you can use the SQL string or a query for the record source........... on the other hand there are four fingers and a thumb.
    Sometimes I use queries, sometimes I use SQL strings. It depends on how I feel that day. The main thing is that you have it returning the results you want.

    The "Best" way is subjective and an always moving target.




    Good luck on your project...............
    Thankyou and thanks for your help so far!

  11. #26
    AndyRob1973 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    81
    Hi again,

    I've tried to write a function which both buttons can call.

    It seems to work when I choose my selection and hit one of the buttons, but when I either click the other button or the same button again the sql adds on the criteria again.

    I can’t work out where or how to clear the sql for the second click.

    Could anyone help?

    Code:
    Option Compare Database
    
    
       ' Dim myspecstat As String
        Dim strWhere As String
        Dim strSQL As String
        Dim strSpec As String
        Dim strStat As String
        Dim strSpons As String
        Dim strLCRF As String
        
           
    
    
    
    Private Sub cmdFormQuery_Click()
    
    strSQL = "SELECT * FROM tblOTR"
    
    sql
    
    MsgBox strSQL
    MsgBox strWhere
    
    Me.tblOTR_subform.Form.RecordSource = strSQL
    
    
    
    End Sub
    
    
    Private Sub cmdForm_Click()
    
    sql
    
    MsgBox strSQL
    MsgBox strWhere
    
    DoCmd.OpenReport "Report1", acViewReport, , strWhere
    
    
    
    End Sub
    
    Private Sub sql()
    
    strSQL = "SELECT * FROM tblOTR"
    
     
      
    'STATUS CRITERIA
    
         'Build the status criteria string by looping through the listbox
        For i = 0 To lst_status.ListCount - 1
            If lst_status.Selected(i) Then
            strStat = strStat & "'" & lst_status.Column(0, i) & "',"
            End If
        Next i
        
    ' strip off the last comma
        If Len(strStat) > 0 Then
        strStat = Left(strStat, Len(strStat) - 1)
        End If
        
    ' if nothing has been selected then don't add to the strwhere
        If Len(strStat) > 0 Then
        strWhere = strWhere & "[Status] in (" & strStat & ") AND "
        End If
    
    'SPECIALTY CRITERIA
    
         'Build the specialty criteria string by looping through the listbox
        For i = 0 To lst_spec.ListCount - 1
            If lst_spec.Selected(i) Then
            strSpec = strSpec & "'" & lst_spec.Column(0, i) & "',"
            End If
        Next i
        
        ' strip off the last comma
        If Len(strSpec) > 0 Then
        strSpec = Left(strSpec, Len(strSpec) - 1)
        End If
        
        ' if nothing has been selected then don't add to the strwhere
        If Len(strSpec) > 0 Then
        strWhere = strWhere & "[Specialty] in (" & strSpec & ") AND "
        End If
    
    'WHERE CLAUSE
            
      ' there will be a trailing ' AND ' at the end so strip this off
      If Len(strWhere) > 0 Then
      strWhere = Left(strWhere, Len(strWhere) - 5)
      End If
      
         'if there is no criteria selected then the strWhere will be empty
       'in which case don't add the where clause
         
        If Len(strWhere) > 0 Then
       strSQL = strSQL & " WHERE " & strWhere
       'MsgBox " You have selected: " & strWhere
       Else
       'MsgBox " You have selected: " & " All Records"
       End If
       
       
        MsgBox strSQL
        MsgBox strWhere
       
       
       
       'MsgBox " You have selected: " & strWhere
        
        'myspecstat = "select * from tblOTR where [Status] LIKE '*" & Me.cmb_Status & "*' AND [Specialty] LIKE '*" & Me.cmb_Specialty & "*'"
        'Me.[tblOTR subform].Form.RecordSource = strSQL
    
    
    End Sub

  12. #27
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    At beginning of function, set each public string variable = "".
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #28
    AndyRob1973 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    81
    Yes!!!!! Thankyou June7 - it's obvious now that you point it out.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 06-12-2019, 05:57 PM
  2. Replies: 8
    Last Post: 06-14-2018, 10:14 AM
  3. Multiple values for a single database field
    By NJH0512 in forum Access
    Replies: 3
    Last Post: 01-30-2018, 07:31 PM
  4. Retrieving Multiple Values into a Single Field
    By Simbiose in forum Queries
    Replies: 6
    Last Post: 08-23-2016, 07:54 AM
  5. Passing multiple values to a second form
    By WithoutPause in forum Forms
    Replies: 39
    Last Post: 02-12-2014, 04:03 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums