Thanks you June7 and ssfanu - I seem to have cracked it (for now) using a combination of your suggestions.....
Thanks you June7 and ssfanu - I seem to have cracked it (for now) using a combination of your suggestions.....
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
happy you got it working for you.
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.
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.
Yep... it is a PBKAC. Brain was out thinking while the fingers were hitting the keys...... Brain got sidetracked.....
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
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.
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. It's a while since I've created a function so I may be back for a nudge in the right direction!
Thankyou and thanks for your help so far!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...............
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
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.
Yes!!!!! Thankyou June7 - it's obvious now that you point it out.