Access and SQL Part 5: More Instant Queries In the last part of this tutorial I showed how you could build an all-purpose query by supplying the user with a dialog box from which they could choose their query criteria. The dialog box had combo boxes displaying criteria choices and when the user clicked a command button their choices were used to construct an SQL statement. The SQL statement was then applied to a stored query and the user saw the result of their choices without having to get involved with query design. This tutorial takes the idea a stage further. Offering More Choices Instead of combo boxes I am going to use list boxes. List boxes can offer the user the ability to choose more than one item, a property known as Multi Select. I am also going to allow the user to specify either "And" or "Or" conditions. Here's how my dialog box looks... The user makes their choices from the list boxes and option buttons and clicks the OK button to see the result. In the example below the user has asked for a list of all the Female staff working in the Design departments of the Nice and Paris offices [click the thumbnail to see a full-sized image]: Building the Dialog Box The first job is to build the dialog box. I have decided to allow the user to query three fields: Office, Department and Gender. You will see that the code that powers the dialog box is easily modified to suit any number of fields. I am not going to describe the form building process in detail so, if you are not experienced in building dialog forms like this one, take a look at my tutorialCustomizing Access Parameter Queries which contains detailed step-by-step instructions. This illustration shows the names I have given to the various objects on the dialog box... There are three list boxes, one for each field, and two pairs of option buttons. The option buttons allow the user to choose whether the AND operator or the ORoperator is used when combining the criteria. Setting Up the List Boxes Building the Lists The Row Source property of a list box (likewise a combo box) defines what makes up the list of items displayed. You can specify the name of a table containing the list of items, or a query which creates a suitable list, or you can simply type the list directly into the properties sheet of the list box (this is called a Value List). For the first two fields I decided to use a query. You can create a stored query for this and enter its name into the Row Source property textbox but, unless you plan to use the query for something else, it is a better idea to enter an SQL statement directly into the Row Source. (Why? There's always a chance that the stored query will get changed or deleted by someone, then your dialog won't work. It's neater too, we access-heads like neat-and-tidy!). Since this tutorial is about SQL we might as well take a look at the SQL statement I used. SELECT DISTINCT tblStaff.Office FROM tblStaff ORDER BY  tblStaff.Office; The SQL statement queries the same table that contains the data I am querying with my dialog box. The "SELECT DISTINCT" clause instructs Access to return only one of each of the items it finds. Without this a simple "SELECT" clause would return an Office name for each record in the table. The "ORDER BY" clause tells Access to return the values sorted into alphabetical order. This method gives me the advantage that should any new Offices or Departments be created as data is added to the table, the SQL statement (which is run each time the dialog opens) will ensure that the list box always displays an up-to-date list. There is a possible disadvantage to using this method. If the table on which the SQL statement is based contains a very large number of rows, or if you are using a large number of list boxes, this could seriously slow down the opening of the dialog box as it waits for the queries to be processed. In this case I would opt for using a purpose made table for the Row Source of each list box. TIP: You don't need to write the SQL statement into the Row Source yourself. Click on the Row Source textbox and the properties window will display a build button  to the right of the textbox. Click the build button to open the Query Builder tool. This works just like the familiar Access query tool. Use it to construct and test your Row Source query. When you close the Query Builder it returns a ready-made SQL statement to theRow Source textbox. Since the Gender list box will contain only two items ("Male" and "Female") there is little point in using an SQL statement so instead I have used a Value List as theRow Source. To do this type the values directly into the textbox, separating the items with a semicolon (;). The actual values in the Gender field are "M" and "F" so, to help the user, I have created a list containing two columns. When creating a multi-list column using this method enter the values for each row of the list together, e.g. M;Male;F;Female. Here's how it looks on the properties sheet... You must also specify which column of a multi-column list is the Bound Column, meaning which is to be taken as the "value" of the list box. Here it is the first column, containing the letters "M" and "F". The words "Male" and "Female" are just there to help the user decode the letters (and don't think I'm joking here! More than once I've had to explain to a user what they meant... "I can type M for Man but it won't accept W for Woman". No, not dumb - just not thinking the same way as me. Okay, I'm being kind, I mean dumb!) It is also necessary, with a multi-column list, to specify how many columns are to be displayed and how wide the columns should be. If you don't do this you will only see one column. I have specified a Column Count of 2 and Column Widths of 0.5cm and 1.5cm. Note that when using metric measurements Access sometimes rounds the values to the nearest imperial equivalent, hence 0.503cm and 1.501cm. TIP: You can create a multi-column list using the Query Builder too. Just design a query that returns more than one column, then specify Bound Column, Column Count and Column Widths as described above. Enabling Multi Select Unlike combo boxes, list boxes can offer the facility to select more that one item from the list. But you must enable the Multi Select feature for this to be possible. The Multi Select property is located on the Other tab of the list box property sheet. I have chosen to enable "Simple" multi select. In this mode the user clicks an item to select it, whilst clicking a selected item deselects it. They can select as many items as they want by clicking on each in turn. You can also choose "Extended" multi select, which allows you to drag down the list to select a group of items, or use click and shift-click to select a continuous group, or click and control-click to select a non-continuous group. Preparing the Option Buttons I have used option buttons to offer the user the choice of "AND" or "OR" when composing the query. I want each pair of option buttons to work as an option group, so that only one member of the group is selected at a time. When one button is selected the other gets deselected. Normally this is achieved on an Access form by placing the option buttons inside a frame. Doing this automatically forces them to work as an option group. But I didn't want to draw frames on my dialog so I'm relying on code to take care of the "group" activity. I did consider using a frame formatted in such a way as not to be visible but sometimes I just like doing things the hard way! If you don't specify default values for option buttons they start with a "Null" value (neither True nor False). This can confuse the user, and will confound my code if they don't change the situation by making a choice. For this reason I have set the Default Value property of each "And" button to True and that of each "Or" button to False. The Stored Query Create and save a query. Anything will do because the dialog box code is going to change it anyway. I have called my query qryStaffListQuery. Writing the VBA and SQL Code The following section explains step-by-step what code is needed and how it works. You can write the code yourself, or copy it from the code listing below and paste it directly into the Access code window. Coding the Option Buttons An option button can have the value True or False. Each option button needs some VBA attached to its OnClick event that says: "If my value is True make my partner's value False, but if my value is False make my partner's value True." A simple VBA If Statement will do it. A procedure like this is needed for each option button: Private Sub optAndDepartment_Click()     If Me.optAndDepartment.Value = True Then         Me.optOrDepartment.Value = False     Else         Me.optOrDepartment.Value = True     End If End Sub TIP: When you have to repeat blocks of code like this, it makes sense to copy and paste then change the appropriate bits, but sometimes this can be quite fiddly. Instead, make use of the Visual Basic Editor's Replace tool (Edit > Replace or Keys: Ctrl+H). Paste a copy of the original procedure then select it and open the Replace tool. Make sure that Selected Text is chosen and enter the original text item into the Find Whattextbox and the new item in the Replace With textbox. Click the Replace All button and your new procedure is written for you... Coding the OK Button Everything else runs on the OnClick event of the OK button. When the user clicks the OK button the code must assemble an SQL statement based on the user's choices from the list boxes and option buttons. It must then apply the SQL statement to the stored query and open the query so that the user can see the result. Start by declaring the string variables that are going to hold the information collected from the dialog box. In my example they are as follows: This variable will be used to collect the selections from the list boxes: Dim varItem As Variant These variables will hold the collected choices from each list box: Dim strOffice As String Dim strDepartment As String Dim strGender As String These variables will hold the And/Or choices from the option groups: Dim strDepartmentCondition As String Dim strGenderCondition As String This variable will hold the completed SQL statement: Dim strSQL As String Getting the Choices from the List Boxes The code must look at each list box in turn and find out which items have been selected by the user. It's possible that the user might not select anything at all. If this happens I will assume that they want to see everything (i.e. selecting nothing will have the same effect as selecting everything). NOTE: I'm assuming that the data being queried is text. If the data is non-text such as numbers or dates, some small but important modifications have to be made. Read the note about data types near the bottom of the page. The following code uses a For...Next loop to gather all the selections from the lstOffice list box and join them together, separated by commas, into a text string. The text string is stored in a variable called strOffice... For Each varItem In Me.lstOffice.ItemsSelected     strOffice = strOffice & ",'" & Me.lstOffice.ItemData(varItem) _     & "'" Next varItem The next step is to check the length of the resulting string. If its length is zero, it means that the user didn't select anything. So the next section of code takes the form of an If Statement which builds a suitable criteria string representing the users choice... If Len(strOffice) = 0 Then     strOffice = "Like '*'" Else     strOffice = Right(strOffice, Len(strOffice) - 1)     strOffice = "IN(" & strOffice & ")" End If If the user selects nothing the code inserts a wildcard into the criteria clause, resulting in something like this: WHERE tblStaff.[Office] Like '*’ But if the user selects one or more items the code creates an IN clause (after removing the leading comma from the string) like this: WHERE tblStaff.[Office] IN('Amsterdam’,'London’,'Paris’) This complete process is repeated for each list box and the resulting criteria are stored in separate variables. I called my variables strOffice, strDepartment andstrGender. Getting the Choices from the Option Buttons The code needs to find out whether the user chose And or Or from each pair of option buttons. It is only necessary to look at the value of one button from each pair since I know that if one's value is True then the other must be False, and vice versa. Again an If Statement does the job... If Me.optAndDepartment.Value = True Then     strDepartmentCondition = " AND " Else     strDepartmentCondition = " OR " End If Each set of option buttons requires an if statement like this. My dialog has two sets. The If Statements create suitable strings representing the specified conditions and stores them in separate variables. Mine are called strDepartmentCondition and strGenderCondition. NOTE: Notice that there are spaces each side of the text in the condition strings " AND " and " OR ". The following section of code also includes spaces as part of the string. It is very important when combining hard-coded text and variables that you remember to include spaces where they will be needed in your finished SQL string. Building the Query's SQL Statement Having gathered all the information from the dialog box, it's now time to build the SQL statement. I have defined a string variable called strSQL to hold it. Here's how the code builds the SQL statement incorporating hard-coded SQL with the variables created in the previous section... strSQL = "SELECT tblStaff.* FROM tblStaff " & _          "WHERE tblStaff.[Office] " & strOffice & _          strDepartmentCondition & "tblStaff.[Department] " &           strDepartment & _          strGenderCondition & "tblStaff.[Gender] " &           strGender & ";" Test the Code Now is a good time to test the code you have written so far. An easy way to do this is to display the SQL statement in a message box. Do this by adding the line: MsgBox strSQL Alternatively you can "print" the SQL statement to the Immediate Window of the Visual Basic Editor by adding the line: Debug.Print strSQL Before you run your code check that you have everything you need by comparing it with Code Listing 1 then compile it with Debug > Compile... If the dialog box is in Design View switch it into Form View and click Save, then make some choices from the list boxes and option buttons and click the OK button. If you asked for a message box it will be displayed now, and you will be able to read the SQL statement. Or you can return to the  Visual Basic Editor to view the resulting SQL statement in the Immediate Window (choose View > Immediate Window or Keys: Ctrl+G). This gives you the opportunity to make sure that your code is working correctly before proceeding to the next step. Apply the SQL Statement to the Stored Query All that remains is to apply the SQL statement to the stored query and open the query for the user to view the results. There are two ways to write the code for this, depending on whether you want to use DAO or ADO. If you use Access 97 you must use the DAO method. If you use Access 2000 or later you can use either. Using DAO (Access 97): If you are using Access 97 you have to write the code this way, but if you use Access 2000 or later you can use DAO instead of the default ADO if you prefer (many Access developers prefer DAO because of its simpler coding style). If you are not using Access 97 you need to set a reference to DAO. To do this, in the Visual Basic Editor go to Tools > References and scroll down the list until you find Microsoft DAO 3.6 Object Library (if you can't find 3.6 use the highest number available). Place a tick in the check box and click OK. Two additional variables must be declared (Note: Access 97 users can omit the DAO. prefixes, but the code will work fine if they are left in place.): Dim db As DAO.Database Dim qdf As DAO.QueryDef Here is the code which will apply the SQL statement to the query: Set db = CurrentDb Set qdf = db.QueryDefs("qryStaffListQuery") qdf.SQL = strSQL Set qdf = Nothing Set db = Nothing The first two lines tell Access that we are talking about the current database, and name the query we are concerned with. The third line applies the new SQL statement to the query. The last two lines empty the db and qdf variables to clear the memory. Using ADO (Access 2000 onwards): Because this code deals with database structure, you need to set a reference to ADOX. To do this, in the Visual Basic Editor go to Tools > References and scroll down the list until you find Microsoft ADO Ext. 2.x for DDL and Security (where x is the highest number you have). Place a tick in the check box and click OK. Two additional variables must be declared: Dim cat As New ADOX.Catalog Dim cmd As ADODB.Command In ADO the information about the structure of a database is stored in its Catalog. The Command object in ADO is similar to the QueryDef object in DAO. Here is the code which will apply the SQL statement to the query: cat.ActiveConnection = CurrentProject.Connection Set cmd = cat.Views("qryStaffListQuery").Command cmd.CommandText = strSQL Set cat.Views("qryStaffListQuery").Command = cmd Set cat = Nothing The first line tells Access that we are talking about the current database, and the second line identifies our stored query (in ADO a query is a View or a Proceduredepending on its type). The third line applies the new SQL statement and the fourth line saves the changes back to the catalog. Finally, the catalog variable is set to Nothing to clear the item from the memory. Open the Query and Close the Dialog Box The code to open the query and close the dialog box is the same for DAO ad ADO: DoCmd.OpenQuery "qryStaffListQuery" DoCmd.Close acForm, Me.Name You can consider the project finished at this point, but it can be made a little more user-friendly with the addition of a few refinements... Refinements: Leave the Form and Query Open Now that you have made it really easy for the user to query their data, they are sure to want to run lots of queries! You can save them the trouble of reopening the dialog box each time by omitting the line DoCmd.Close acForm, Me.Name and adding some extra code to allow them to leave the query window open too. They can arrange the query window and dialog box so that they can see both on the screen, then as they make their choices in the dialog they will see the results in the query window as soon as they click the OK button. In fact the query must be closed before the new SQL statement can be implemented (the SQL statement will be applied to the query even if it is open but the user will not see the changes until the query is run again). The following code should be placed at the beginning of the cmdOK_Click procedure, immediately following the variable declarations: If SysCmd(acSysCmdGetObjectState, acQuery, "qryStaffListQuery")  = acObjStateOpen Then     DoCmd.Close acQuery, "qryStaffListQuery" End If It simply checks to see if the query is already open and, if it is, closes it. The code will reopen it after the new SQL statement has been applied. If the query takes more than a moment to run, the user will see query close then open. To make this process invisible turn off screen updating by placing this line before the code above: DoCmd.Echo False ...and add the following line to the very end of the procedure before the End Sub line to restore screen updating: DoCmd.Echo True TIP: When testing your code it's a good idea to temporarily remove or disable the line that turns off screen updating (DoCmd.Echo False) in case anything goes wrong and you're left with a frozen window. The line which restores screen updating (DoCmd.Echo True) usually comes near the end of the procedure. If there is an error somewhere which prevents the procedure reaching this line then screen updating will not be restored and you will be left with a blank or frozen screen. Even after several years as a VBA developer I still forget to do this, and sometimes I find myself staring at a blank screen wondering what the heck has happened! If it happens to you, don't panic - instead switch to the Visual Basic Editor and open the Immediate Window (Ctrl+G). Type DoCmd.Echo True into the Immediate Window and press Enter and when you return to the Access window you will find that Screen Updating has been restored. If you have a procedure that switches off screen updating, remember always to include a line in your error handler to restore screen updating in the event of an error. Refinements: Restore a Missing Query Users (that includes you and me!) have the annoying habit of deleting things if they don't know what they are. If the code can't find the query because someone has deleted or renamed it there will be an error. In the last tutorial in this series I explained how to create a function to test for the existence of a query. This time, I'll explain how to include the code in the cmdOK_Click procedure. The method is slightly different for DAO and ADO so I'll outline both. Using DAO (Access 97) If you haven't already added the db and qdf variables (as described above) you should add them now, together with a third variable blnQueryExists. Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim blnQueryExists As Boolean The code is in two parts, and should be placed right at the beginning of the cmdOK_Click procedure, after the variable declarations. The first part uses aFor...Next loop to search through the database's queries looking for one with the name specified. If it finds the named query it sets the value of the blnQueryExistsvariable to True and exits the loop (it must exit the loop now because any other query it finds will set the variable's value back to False)... Set db = CurrentDb blnQueryExists = False For Each qdf In db.QueryDefs     If qdf.Name = "qryStaffListQuery" Then         blnQueryExists = True         Exit For     End If Next qdf The second part looks at the value of the blnQueryExists variable and if it is False it creates a new copy of the query... If blnQueryExists = False Then     Set qdf = db.CreateQueryDef("qryStaffListQuery") End If Application.RefreshDatabaseWindow The last line is not usually necessary because the new query will be opened later in the procedure. But, in case something unforeseen happens, this makes sure that the new query appears without the user having to refresh the window themselves. Using ADO (Access 2000 onwards) If you haven't already added the cat and cmd variables (as described above) you should add them now, together with the variables qry and blnQueryExists. Dim cat As New ADOX.Catalog Dim cmd As New ADODB.Command Dim qry As ADOX.View Dim blnQueryExists As Boolean The code is in two parts, and should be placed right at the beginning of the cmdOK_Click procedure, after the variable declarations. The first part uses aFor...Next loop to search through the database's queries (a query in ADO is called a View) looking for one with the name specified. If it finds the named query it sets the value of the blnQueryExists variable to True and exits the loop (it must exit the loop now because any other query it finds will set the variable's value back to False)... Set cat.ActiveConnection = CurrentProject.Connection blnQueryExists = False For Each qry In cat.Views     If qry.Name = "qryStaffListQuery" Then         blnQueryExists = True         Exit For     End If Next qry The second part looks at the value of the blnQueryExists variable and if it is False it creates a new copy of the query... If blnQueryExists = False Then     cmd.CommandText = "SELECT * FROM tblStaff"     cat.Views.Append "qryStaffListQuery", cmd End IfApplication.RefreshDatabaseWindow Note that when using VBA to create a new query, ADO insists that I provide the query with an SQL statement. Anything will do, but it won't accept an empty string (""). With DAO I can create an "empty" query definition (the procedure supplies the SQL later anyway). The Completed Code Procedures Here is the completed code listing for the dialog box. It is in a format that you can copy and paste directly into the Access code window. Code Listing 2 (DAO) for Access 97 Code Listing 2 (ADO) for Access 2000 (and later) Note About Data Types When composing SQL statements it is important to get everything right. SQL is a simple language but it is very precise and does not tolerate errors. In this tutorial I have made use of the IN() clause which is very useful for presenting a list of items when specifying your criteria: WHERE tblStaff.[Office] IN('Amsterdam','London',Paris') ...is the same as: WHERE tblStaff.[Office] = 'Amsterdam' OR tblStaff.[Office] =  'London' OR tblStaff.[Office] = 'Paris' The IN() clause can be used for any data type, not just text as I have illustrated here, but you must remember to use the appropriate qualifier when composing the SQL. The qualifier is a symbol enclosing the data which tells Access what the data type is and, of course, the data type must match the data type of the field that the criteria applies to (my Office field is a text field). Text criteria must be enclosed in quote marks. SQL will accept single quotes (') or double quotes (") but since here I am creating the SQL statement as a VBA text string the whole thing is itself enclosed in double quotes, so I use single quotes for the individual criteria items. Date criteria must be enclosed in hash marks (#) (you might call them number signs). Remember also that when hard-coding dates into SQL they must always be written in the US format of month/day/year. [Note: If your Windows regional settings dictate that you use the European day/month/year date format it is you should use that format when entering criteria into the grid of the Access query design window. Access converts it to the US format when it constructs the SQL statement. Take a look at the query in SQL View and you will see it.] Number criteria should be entered without any qualifier. So, to modify the code I used to gather data from the list boxes: If the list box contains a list of dates: For Each varItem In Me.lstBirthDate.ItemsSelected     strBirthDate = strBirthdate & ",#" &      Me.lstBirthDate.ItemData(varItem) & "#" Next varItem Note that the single quotes have been replaces by hash marks (marked in red). The resulting IN() clause looking something like this: WHERE tblStaff.[BirthDate] IN(#9/27/1950#,#2/7/1968#,#6/19/1977#) If the list box contains a list of numbers: For Each varItem In Me.lstID.ItemsSelected     strID = strID & "," & Me.lstID.ItemData(varItem) Next varItem Note that the single quotes have been removed leaving just the comma and, since no qualifier is required for numerical data, the closing qualifier is removed completely. The resulting IN() clause looking something like this: WHERE tblStaff.[ID] IN(25,50,75,100) Some Additional Features If you want to add extra functionality to your dialog box take a look at part two of this tutorial. It shows you how to build list box lists with code, how to let the user choose how the data is sorted by add an ORDER BY clause to the query, and how to add a Clear All button to reset the list boxes... Go to the next part of the tutorial >>> Download the File CORRECTION: The sample file accompanying this tutorial originally contained a typo in the error-handling code. The last line of the cmdOK_Click procedure of each of the sample forms read Resume cmdOK_Click_Err when it should have read Resume cmdOK_Click_Exit. This error was corrected on 12 April 2004. AccessAndSQL5_97.zip [82KB] Access 97 format. AccessAndSQL5_2000.zip [122KB] Access 2000 format.